I'm too big a sissy to play lots of horror games, but here's what I remember being spooked by:
-The giant bat, Medusa heads, and the mummies in the first Castlevania. I was five, okay?
-I was scared to death of Metroids (and I started with the NES) until I started playing Metroid Prime. I was nervous when I knew the first one was coming up, but I got over it when I started killing them.
-Any level in Mario 64 and Banjo-Kazooie where you were swimming and a big fish could come and eat you.
-Tawfret in Jet Force Gemini. Still one of my top five games ever, but I was scared to death of the zombie bugs when I was eight or nine. Also, the Cockroaches freaked me out pretty badly.
-The first Rainbow Six for the N64. Every time I walked through a door or around a corner, my guy would get shot to death and the sad music would play. Made me jump every damn time.
-FEAR was the first game I ever played that was actually designed to be scary, and it did a pretty good job. The combat was good too. Then again, it's the only piece of hair-in-front-of-eyes-little-girl horror I've ever seen, so it hit me harder than my friends who had seen The Ring.
-By the time I got Bioshock, I was quite a bit maturer than I was when I played the N64 games, so I never really jumped or got nervous while I was playing it. However, my hat is off a thousand times to the atmosphere designer, and I felt that rare but genuine sympathy and liking for the characters. (People complain about that hallway with the corpses where the lights go off. If you couldn't see what was going to happen when the lights came back on, then you may need to have your gamer's license revoked.)
-Condemned. Hold me. Hold me tight and tell me it's going to be over soon. (Never played Bloodshot)
-Most recently, I've played Silent Hill 2 and 4 (never tried 1 or 3). I didn't find 4 scary or even unnerving (except when I met Double-Headed Oh-My-God-Hold-Me Ghost Baby Thing). I don't remember 2 ever making me jump, but it did succeed in making me paranoid and nervous, and I give it a lot of respect for that. It says a lot about a game when the deepest character never says anything more complex than a grunt.