Eternal Darkness stands out in my mind as one of the greatest horror titles, although I've always thought of horror as a theme rather than a genre.
A lot of games calling themselves horror games aren't particularly tense (see: every Resident Evil title after the third, except possibly the seventh) and the atmosphere in games like Bloodborne, the original Bioshock, and Alan Wake are far more invocative of horror.
The problem with modern horror games is that they seem to think excessive amounts of gore and cheap jumpscares are enough to do it. Tension, suspense, and a feeling of overwhelming vulnerability are what make a great horror game, at least to my mind.