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.
Any time Eddie performs the Bring It On Home solo.
Eddie: Start making an "O" shape with your mouth so you can talk about the humanity!
The climax of "Exploited in the Bowels of Hell" - just when Mittens has been defeated, a bunch of Bouncers come in to deal with Eddie and the Headbangers. What does Eddie do? Recruit every last Headbanger in the mine, using Mittens's headset to blare the Battle Cry all over the mines.