Metroid.

Wither was up there too, but Stalker series is in another league

Going to have to go back a decade or two and say the games in the Myst series. All of 'em.

I have to agree with Max Payne being a game with a great atmosphere. Other games I liked have already been mentioned such as Fallout 3, Metro 2033, Metro: Last Light, and The Last of Us. It almost feels like it's actually you in these games.

Xenoblade, Metroid Prime and Fusion, Endless Ocean 2.

The Ocarina of Time, definitely, as well ad Chrono Cross. Bastion and Superbrothers: Sword and Sworcery are also very immersive.

I'd say either dead space or resistance

The original Resident Evil games, they did such a great job at sucking you into their world. Not just with the zombies, but all the letters and notes left behind by the deceased.

I see a few mentions of Alan Wake. *thumbs up*

I would also say Silent Hill and Resident Evil 1 as well.

For me, it's easily Oddword: Abe's Oddyssey.
The entire game captured the runaway slave atmosphere so well.