The battle in the village in Resident Evil 4...possibly one of the greatest moments in gaming history, at least to me. The sheer sense of, "Oh crap, crap, CRAP!" that is constantly going through your mind as you try to find the next safe place to run to, or figure out if you have enough time to squeeze in that next shot before you lose your head...I haven't really found another game since that matches that.
Of course, it is immensely satisfying to go back there in New Game + with fully upgraded gear--or the Thompson--and just plow through enemies, but the first time you play that part. Great memories.