This entire thread is full of set-pieces...
My most awesome moments always hop by in gameplay.
Like whacking the entire enemy team in CS with the TMP (A gun I usually can't handle well at all).
Virtua Fighter 5, me and my opponent picking the exactly correct moves so that we're literally swinging limbs just past each others' faces for a couple of seconds.
Correctly evading moves and escaping throws, going back and forth, attacking and defending for some five seconds before getting that counterhit launcher in is one of the greatest gaming experiences you can have.
In general, having two players of near equal skill going back and forth and then winning in the end.