For me, the witch's theme was always more intimidating than the tanks. Not for what was coming, but just because it was so unsettling.
Another painful song is the Black Hole CO Super Power from Advance Wars 2. It's a very big military level "SCREW YOU" to the player.
For individual sound effects (That haven't been mentioned, most of mine are already up here) I'd propably go for the crying sound from baby Mario in Yoshi's island (SNES version, haven't played the other) for its oh crap and annoyance factors, and the guitar's sounds as you fail a song in Guitar Hero.