As I’ve said before, what I generally like in horror stories is atmospherics; I find the gore and violence which saturates modern horror fiction to be not in any way frightening, merely grotesque and unpleasant. Unfortunately, many alleged horror video games take their cue from the rest of the genre on this, and ratchet up the violence and ratchet down the suspense and the atmosphere. Even the best horror games, such as F.E.A.R. and Doom 3, only just manage to establish a pretty good spooky atmosphere, only to undercut it with fast-paced run-and-gun levels. (Ravenholm, from Half-Life 2, inverted this by putting a survival-horror level in an action game, which made for a pleasant surprise.)
The most successful scary moments in games are usually to be found in games that aren’t actually in the “horror” genre. The graveyard area from BioWare‘s unfairly underrated Jade Empire is one of the most unsettling things I’ve ever seen in a game. Practically every locale in Black Isle’s fantasy epic Planescape: Torment has about it the feel of weird, cosmic fear. Neither of these games are really considered “horror”, but both are far superior to the mindlessness of many supposedly “scary” games.
The overall story of the first Mass Effect was very Lovecraftian, but it shied away from emphasizing the cosmic fear engendered by the Reapers as much as it could have, which in my opinion was a mistake. The element of mystery and the Unknown were sadly removed from the Reapers in Mass Effect 2, and the cosmic horror elements played down even more. Which was a pity, because it contained a rather well-done horror level in the part set aboard a “dead” Reaper. (Note the Lovecraft reference: “Even a dead God can dream” in one of the crew logs.)
I suppose what it all comes down to is my taste for actual stories in games. In so many alleged horror games, the story is nothing but a bunch of foolishness cribbed from zombie movies. Most of the games I’ve listed above as liking are either BioWare or Black Isle (later to become Obsidian Entertainment); game companies renowned for their storytelling. Which, incidentally, reminds me what a pity it is that Sega canceled Obsidian’s Aliens RPG, which might have been a truly great horror game.