There's one particular song in Half-Life 2 Episode 2, right near the beginning. It's the fight with the Antlions defending Alyx. The song is called Vortal Combat, and it's probably one of the best songs to be played mid-game that I've ever heard. There's other good ones in Half-Life 2 as well, but that one is the best in my opinion.
I also liked Blow Me Away in the Mausoleum fight of Halo 2. It's there without lyrics, and the song fits the pace of that battler perfectly; calm opening and observation for a few seconds, then bullets and rock start flying. Song winds down as fight finishes.