Still Life and The Black Mirror are both very good games, both a bit disturbing, but nothing compared to really scary movies. You see dead people (
) in both, there's blood and murder in both, and I'd say both are creepy, but nothing to make nightmares about, really, especially in the case of Still Life. Black Mirror is a bit more twisted.
Also, Gabriel Knight is a must play for an adventure game lover if you haven't already played it, especially the second game. And this is coming from a Lucas Arts fanboy.
To sum it up, the best games I've played lately (in the last 5 years or so) are Syberia (1), The Black Mirror, Return to Mystery Island, Still Life, and Myst 4.
And Grim Fandango, of course...