Everyone is going to hate me for this. But two things are counted among my top sections or levels in gaming that seem to be hated by everyone else, and one of them is the Library in Halo, particularly the remastered version in Anniversary. Yes, it's reptitive, and yes, it gets boring seeing the same environment over and over again, but I love a challenge, and playing that level on Legendary is just crazy. No time to think, just act, and hope to Parangosky that you have enough ammo to survive the current wave.
The second one is less controversial, but people here still seem to hate it because it's "not stealthy" or "too much stuff in a bland game" - I really enjoy pretty much all of Assassin's Creed 3 (and even like Connor, seeing plenty of reason for his usual attitude as well as plenty of times he's actually a decent, not-moody, guy, which people don't think of because they don't give a rat's arse about the story in the first place yet still feel they have the right to complain about it

). But the main thing I want to mention, in terms of 'levels' or 'sections', are the naval battles. I love the different gameplay, mixing things up a little, and the tactics involved (and yes, there are tactics involved) - it also gives a bit of a basic education on old naval warfare strategy, while adding more depth to the game and the plot and something different for people to enjoy. Plus, there's no better feeling than seeing fleets of Templar ships wiped out by one single vessel...
I'm also going to agree with plenty of other people here and say Ravenholme. Whenever I play that level I get so creeped out and tense, checking every corner several times and watching my six just as often as my front. Only Ravenholme in HL2, the Dunwich Building in Fallout 3, and Bioshock and the Houdini Splicers have induced that same level of fear in me in games that aren't specifically horror titles (obviously horror games do this all the time, if they're any good...).
Finally, every part of Myst. Not the game, the franchise. Every single Age in every game in that series has an ethereal beauty and an eerily haunting atmosphere that stays with me long after I've put the game down...