This is gameplay-wise, so I don't want "Sephiroth kills Aeriths (or whatever the hell they are called). I just want to hear about some really awesome parts of games that made you go HOLY SHIT.
One part in a game that struck out to me was fighting the splicers in Bioshock. It wouldn't have been as cool if it hadn't been playing the Waltz of the Flowers from the Nutcracker. Maybe I was just tired and losing mind, but that scene emanated awesomeness. I also got a strange Clockwork Orange vibe from the whole classical music+violence, which is no problem with me.
Runners up include any boss battle in Shadow of the Colossus and leaving City 17 in Half-Life 2.
What about your favorites?