My motto is "Less mods, the better"...the problem is, if one has too many mods the game just CTDs too much. That is the same as it was in Oblivion/Fallout 3 and New Vegas. However, if one chooses larger compilation mods, while they are bigger, the game CTDs A LOT less, if at all. I don't think the game can handle a hundred mods, that some people put on their game. Then again, I'm sure someone with 200 mods runs the game just fine. So it all depends.
Anyway, I personally like less mods, but bigger ones. Doing it that way has stopped CTDs (for me) in Oblivion, Fallout 3 and now Skyrim.
Having said that...here are some mods that change up the game (be it graphics or gameplay)...I have like 50 mods or so, and the game works fine. I'll just list some of my favorite ones.
Texture Pack Combiner...I'll get this one out of the way. if you want to use the whole package, it will take 2+ hours to download everything and install the mod. What it does is take a lot of graphics mods, and combines them into one pack, so they are all compatible with each other. Need a higher end PC for this one
http://skyrim.nexusmods.com/mods/20801Vanilla vampires suck (but Dawnguard does improve them, and this mod is compatible with Dawnguard)
http://skyrim.nexusmods.com/mods/9717Makes character creation that of Oblivion...choosing major/minor skills, birth sign and what not
http://skyrim.nexusmods.com/mods/21587This one adds TONS of dialogue (both voice and text) to NPCs in Skyrim
http://skyrim.nexusmods.com/mods/8429If you want a completely different Skyrim experience (compatible with character creation mod)...adds tons of new skills and lots of new stuff.
http://skyrim.nexusmods.com/mods/9286DLC quality mod. If you get any mod on this list, you need this one
http://skyrim.nexusmods.com/mods/9782And two mods for being a better mage (since Skyrim vanilla mages are really bland compared to other games in the series)
http://skyrim.nexusmods.com/mods/8451http://skyrim.nexusmods.com/mods/16225I was using Midas mod for spells (that I saw recommended on first page), but it has some rather...game breaking bug(s). If you get all the tomes that are from Midas and use them, it causes your save to become broken and get an infinite load screen. I've seen this reported a few times now. Granted, it could be an incompatibility. But in any case...Midas mod is abandoned, hasn't been updated in ages. It was updated before all the creation kit changes Bethesda made a little while back, so who knows what problems it could have.
Skyrim Improved Magic System is a must though for a mage, it adds some new spells and balances it out. Plus, you can actually choose what spells to start with out of a list of choices. Apocalypse Spell Package goes well with it, and adds a ton of spells...some are a bit overpowered though, so keep that in mind. But the creator updates constantly to fix/balance/add things.