The original Silent Hill on PS1 made me feel creeped out back in the day. It probably had something to do with all those demon babies. I played thru the Japanese import version and the music and atmosphere were on another level compared to everything else.
More recently there was Alan Wake. I hung on every single part of that story and loved every minute of it. The ending was both welcome and sad at the same time. The new DL off-shoot game that launched early last year made thigs alittle better until you realize it isn't part of the official cannon.
There was also Heavy Rain. Like Alan Wake this was another excellent game with a story rivaling some of the best stuff hollywood had to offer the last several years. Although this game is very good it can also be very damn depressing. And then there are the multiple endings which will leave you completely stunned.
Lastly theres The Walking Dead. Had Microsoft not made episiode 1 free on XBL I probably would have never even touched this game. I had seen a few episodes of the show on Netflix but never bothered digging into the series anymore than that. I obviously had no idea what I was missing. Episode one had some of the deepest story moments for such a short experience. It's one of those games where you have to question your every move and then live with the conseqences. I wish more games were like this.