The game is awesome pretty much all the time, but the moment that takes the cake would be at the end where Eddie kills a giant monster (that's made out of a church) by driving his car into its face; and once inside, proceeding to bloodily decapitate Big Bad Doviculus to the tune of Judas Priest's "Painkiller".
The entire "Its Raining Death" mission. All set to "Through The Fire And The Flames" by DragonForce. Hell. Yes.
The intro video and first 20 minutes of this game are full of awesome. First, an on-stage accident during a concert causes a giant monster to be summoned and Eddie to be dragged to the Age of Metal. Then he fights hooded baddies with a giant axe and a lightning-summoning electric guitar atop a mountain of skulls, then he meets a hot Action Girl and joins forces with her, then he summons a Cool Car using The Power of Rock, then he battles a gigantic tapeworm, and finally he and his female companion escape over a crumbling bridge. It's like Tim Schafer decided to cram as much cool shit as he could in the game's introduction chapter, and he succeeded.