Planescape: Torment, Max Payne 1&2, and Silent Hill. I may be forgetting a couple, but these few are the only ones I've played that made me think of them more than just a game.
For most console RPGs it's never really been the game but the soundtracks that stuck in my head.
