Here's some of my memorable faves:
Unreal: When you meet your first Skaarj on level 3.... you know the bit, where you suddenly find your path blocked, then the lights shut off one by one, run back the other way to find it also blocked, you hear a roar, desperately throw down a flare before it emerges from the darkness. Pure class.
CoD: Quite a few sections in all the games, but the Stalingrad level is just MENTAL, really drives home the fury of war
Freespace 2: One of the early missions where you are really up against it, and then a massive cruiser comes in to save you, beam cannon blazing from miles away. Have some of that!
Max Payne 2: The 3DR/DNF parodies on the police station level, pure class, especially the guy on the toilet "I'll come out when I'm done!!"
MOH
A: The attack on Pearl Harbour, great atmosphere
Halflife 2: The entrance to Nova Prospekt when you lead your antlion army in to attack the enemy position, shooting down choppers etc. One of the most manic combat sections of the whole game.
Unreal 2: Defending a space station from attack by positioning static defences, forcefields, teammates etc. Great little set piece.
Doom 2: Map 17 (tenements) in co-op mode, opening the door to the main courtyard and finding a Cyberdemon there! We were like WTF?!? There's no cyberdemon on this level?