Silent Hill scared the crap out of me when I was a kid... why the hell my parents actually bought that for me at age 12, I will never know lol. Fatal Frame, already mentioned as well, is another game that had me awake all night with the light on. There are also a lot of indie PC games out there that are pretty scary. You can find various sites that rate their top 10 scariest indie games. Some that I have played and loved were Amnesia the Dark Descent, Eri, and Penumbra: Overture. One thing I did like was their are a lot of mods/stories out for Amnesia that people have done.
You have to try Siren: Blood Curse! I love that it's not just straight-up horror by throwing gore and violence at you, but it's also got such a haunting and mind-fucking story. Gotta check it out, it's such an underrated game and not too well known, but it won't disappoint, imo.
You should definitly take a look at Metro 2033 (PC and consoles). Just great amtosphere and survival elements. It's a linear FPS, so gameplay is VERY effective but not as overburdened as in rpg's or sandbox-games.
And if you don't dread playing games that are a tad older, I'd recommend "System Shock 2" (PC). That game left in the fetal position in my chair, whimpering and clamping my shivering hands around the wrench it gave me to defend myself against the dangers it would throw at me... and that was maybe 2 hours after the tutorials and into the game. It's a pretty long and complex fps-RPG, so not a everyones-game, but definitly deeper and more thoughtful than the whole garbage that gets filed under horror or rpg nowadays.
I too have heard Silent Hill 2 a lot, but I have also heard that the HD-rerelease is a pretty big f#ck-up. So the original Silent Hill 2, for the PS2, is probably where you want to go.
Amnesia is kind of a double-edged sword, but yes, it is pretty scary once it sucked you in. sometimes it just loses it's flow though... And it's an indie-game which shows through sometimes. But definitly a worth a try. (Unlike the whole hype around slender and it's various offsprings)
If you'd like some atmosphere to, but aren't totally focused on the "true horror"-aspect, I would recommend Limbo. It's a monochrome 2D-Platformer with physic-elements, that definitly gave me some chills. If you're into that sort of thing i could maybe give you some tips how to truly enjoy it, because it's like a gourmet menu, you can't just gulp it down like fast-food-casuel games.
Oh and wait, DON'T try any of the Dead Space games, or at least do a bigger research via Let's Plays and gameplay-videos. Why? Because if you're looking for actual Horror-Games they're a waste of money paired with a sleeping potion.
Silent Hill 123 Resident Evil 4 Dark Souls is pretty creepy but the scares really come from surprises and overwhelming odds.
Breakdown for original Xbox works on 360, it is very unique (ex:first person roundhouse kicks) You run into enemies that are inhuman and seem unstoppable (sometimes they are so you run). The whole game feels like a hallucination, but is one of the harder 3D games (that are good) that I have played.
I LOVE System Shock 2. Although I wasn't terrified, the beginning of the game before you fight an infected crew mate was somewhat unnerving. After that, I just stuck to my guns (literally) and kept moving. Some of the monsters like the spiders and the cyborg midwife are another thing...