Make the adventure related to the Pc:s somehow.
I had a fairly simple plot where they ran into a cult who tortuered children.
One of the Pc:s thought his parents had been killed by this cult, because his father possesed some book they wanted (true).
When they finally found the badguy, it proved to be the father of one of the other Pc:s. He totally freaked out, and in the fight with the cultist, he switched sides and tried to help his father
Another tip, Don't overdo it. If you go into gory details the players will at most pretend that their characters are discusted, you wont scare them though.
No one knows better what scares a player than the player himself. Just give them a few ideas (like a cult tourturing children) and let them scare themselves. Nothing is more horrible then the things that you don't know what it is...
