I think that Vampire: the Masquerade is worth a special mention here. Music is not only good for a Vampire game, I consider it absolutely essential. Rob Zombie, Lords of Acid, Genitorturers, Professional Murder Music, and Zombie Girl play when I run games. High-energy music and the whole music scene are as much a fixture to Vampire's milieu as knights and castles are to D&D--I just can't imagine playing Vampire without music to drive it along. Sex, blood, and rock 'n roll.
Third... Don't play music just for the sake of playing music. I know DMs or hosts that just play music for the heck of it and it bugs me--not even an attempt at ambiance, they just play music to have noise in the background. Listening to 80's easy listening or 60's rock-and-roll during a game reminds me too much of muzak, it's difficult for me to get into the game when the play space sounds like a restaurant. That might not bother some people, but it's a distraction for me.
When I DM, I sometimes use music to enhance the setting the characters are in. I haven't done it much though so I'm not sure if its a useful tool or just a distraction yet. Has anyone had substantial experience with this? Does it make the game more fun?
Also, if it does help, what are some good sound tracks to use?