Count me in among the System Shock 2 adherents. The game really makes an excellent use of audio cues, level design and plot development to create an atmosphere of palpable dread - making you constantly fear what's around the next corner, and playing on your expecations so that you'll still jump out of your chair when you hear that arachnid annelid chittering directly behind you. Doom 3 may be known for its "shock and awe" tactic, but System Shock 2 did it first and did it better.
The old adventure/puzzle game The 7th Guest does a great job of creating atmosphere too, and if you say you didn't jump at the first time you hit a dead end in the labyrinth, you're either lying or inhuman.
It's one of the downright creepiest areas I've seen in a game to date.
While Doom 3 didn't really frighten me much, I've played so much of the original Doom/Doom II and player-made WADs for it that I react strongly to a lot of the sounds (the stomping of a Cyberdemon, for instance) instinctively and very viscerally. And a number of WADs have great atmosphere as well: the giant pyramid level in "Alien Vendetta" (map20, I think?) and the WAD "Asylum of the Wretched" are both top-notch in terms of atmosphere, including music use and map design.
However, there's one that got to me more than almost anything else I've experienced, and it's a
mod. Freespace 2 is pretty much universally acknowledged as the pinnacle of the "space sim" genre, and rightly so - its game design is amazing in every aspect. It still has a lively modding community to this day, and from that community came a campaign called
Transcend. Imagine an atmospheric horror game fused with a space sim; a Philip K. Dick plot combined with horror movie dread; the simultaneous claustrophobia of being trapped inside a tiny spaceship, and the sheer immensity and silent power of space; your sense of reality being pulled down around your ears while being murderously, relentlessly hunted across space. It's a truly mindblowing and harrowing experience, and its design is incredibly tight while taking advantage of all the things that made original Freespace 2 amazing. Everything from the visuals to the audio to the setpieces are brilliantly used. It's an experience without peer, and a fusion of genres I have yet to see adequately replicated elsewhere. It has a quasi-prequel called Sync that does some of the same things, but it's not nearly as polished or effective as Transcend.
