Isn't it strange how most of the games we experience this sort of reaction to are RPGs?
Anyway, to me, music is really important in a game. It might not break it, but a great soundtrack sure can make it. There's some tracks that just send shivers down my spine. Others make me cry like a girl, which is awkward when one pops up on my playlist when I'm on the Tube.
I'd have to cite Mass Effect 2 and 3 for having spectacular music that really amplified the atmosphere. And Inon Zur's composition on the first Dragon Age was brilliant. Main theme and In Uthenera are the two standouts there.
Silent Hill was great in a different way. It wasn't so much music as discordant noise and static and... It totally succeeded in creeping me out. Then you got something beautiful in the middle when Lisa had her moment and that served to make it even better.
But to me, Square are the kings. Chrono Cross is stunning, and what can be said about the Final fantasy OSTs that haven't already been said? I've got them all, plus the piano collections and Distant Worlds. I'm even having them as the music for my wedding. They're so inextricably linked with moments, characters and my feelings in the games that they rank top of the list, for me at least.