The most dramatic use of music in one of our games was with the "Duel of the Fates". It was when we faced our final BBEG after a long and grueling campaign, and it really fit the conflict perfectly.
Music is a very complimentary addition to any D&D game - use liberally, but tastefully.
I've played guitar for many, many years. And I sing.
So I'll typically record custom stuff for use in games. And I'll do fun stuff like record bardic songs about the PCs, or write special songs (using custom effects) for settings like a magic forest, a royal court, etc.
I've never had music going while playing and never really liked the idea. I just think it'd be too distracting...and to diffacult on my ears of course I've played with some pretty loud people. The DM of one of the games I was in(it was hosted at my and my roomates place) was always wanting us to have music but I dunno...I also dont really think theres much out there that'd fit, for me, with most games.
Nothing beats the Chrono Trigger and Final Fantasy soundtracks. Particular favourites are The Temple of the Ancients tune from FF7 for certain dungeons, Peaceful Days from CT for villages, and Waltz for the Moon from FF8 for High-Class get-togethers by the local lord.
I've tried it once or twice. Too distracting. If you find something particularly evocative or obscure, an instrumental of some sort, it can be okay, but it's usually some recognizably un-medieval song that takes you out of the game. It's very often just an excuse for the DM to make you listen to some atrocious band he/she likes. I remember once my DM put on a Perfect Circle CD because he swore how great it would be in-game. And all I kept thinking about is "I hate this music. I hate this song. I hate this singer. Did he just say a trap went off? I hate this music."