Eternal Darkness: Sanity's Requiem

There are essentially two types of "horror" systems in video games.  You have games that set dark, foreboding atmospheres, play off the natural fears we, as players, all have, and let the tension build until we wind up getting ourselves completely worked up waiting for the killer to emerge or not emerge from behind every door.

You also have "twitch" horror games that mostly involve monsters jumping out of dark places, poorly lit rooms that make it hard to see everything, and typically allow the player to carry enough ammo to make Commando jealous.  The only real "scares" are the same "jump" scares that many modern horror movies use when they're out of ideas or are afraid the audience is getting bored.  Now, while a "jump" scare can be extremely effective when done correctly, a game that's nothing but jump scares tends to become tiring extremely quickly.

Needless to say, I'm much more a fan of the former than the latter.

Now, while I will say that no game sets up a better atmosphere than the Silent Hill franchise (a shame they stopped after four games, no matter what anybody else says), I think the game that made me the most tense and freaked me out the most was Eternal Darkness: Sanity's Requiem.