The very first Story, "A Study in Scarlet", presents Mormons as having a sinister men in black force of keepers who will find you and kill you no matter where you hide. Ditto the KKK in "Five Orange Pips".
Said Mormons' Heavies, Enoch Drebber and Joseph Stangerson, eventually find themselves on the receiving end of such Paranoia Fuel: Jefferson Hope is a Crusading WidowerOne-Man Army who will find them and kill them no matter where they hide. Not to say they don't deserve it, but still...
The titular substance in "The Devil's Foot" is literally nightmare fuel. In a less literal sense, Holmes and Watson testing it on themselves.
"The Adventure of the Copper Beeches" is chock full of Nightmare Fuel. First, there's the poor governess, who is brought to a mysterious countryside manor, where she is subject to bizarre demands, discovers that her boss is evil, and finds the child she is to care for takes a perverse delight in torturing animals. Likewise for Rucastle's daughter, who has been imprisoned by her unstable father for some time to keep her from marrying and obtaining her inheritance. This would be creepy enough, but the setting of the story means that the two girls are entirely at the mercy of an unbalanced sociopath, and can draw on no one for aid. (Holmes even comments that the isolated country setting can elevate ordinary crimes to the level of Nightmare Fuel.) Also, depending on your feelings about dogs, the vicious, half-starved mastiff can count, too.
"The Speckled Band," especially if you don't like snakes. And, as in the above story, the Stoker sisters are likewise at the mercy of their cruel and vindictive step-father who's perfectly willing to dispose of them if they threaten his income, and poor Julia falls victim to his malice.
In the Granada television version there's an increidbly tense scene with very creepy music as Holmes and Watson sit in the dark, waiting in dread for whatever's going to come through the ventilation shaft. Eventually Holmes lights a candle and... sees what's lying on the bed off-screen; Jeremy Brett really sells the fear that even Holmes must be feeling as he carefully stands up and prepares to try and drive off an incredibly dangerous animal.
"The Creeping Man" is, well, super creepy.
The first sight of the murder victim in The Sign of the Four is described in genuinely nightmarish terms: I stooped to the hole, and recoiled in horror. Moonlight was streaming into the room, and it was bright with a vague and shifty radiance. Looking straight at me, and suspended, as it were, in the air, for all beneath was in shadow, there hung a face, ... [Its] features ... set ... in a horrible smile, a fixed and unnatural grin, which in that still and moonlit room was more jarring to the nerves than any scowl or contortion.
During the climax, the villain describes in quite detail how he and his accomplices murder a servant to steal his master's treasure.
Doyle may have unintentionally screwed the pooch (no pun intended) on ever getting a satisfyingly terrifying visual representation of the Hound, because nothing can possibly top Watson's description of it:
A hound it was, an enormous coal-black hound, but not such a hound as mortal eyes have ever seen. Fire burst from its open mouth, its eyes glowed with a smouldering glare, its muzzle and hackles and dewlap were outlined in flickering flame. Never in the delirious dream of a disordered brain could anything more savage, more appalling, more hellish be conceived than that dark form and savage face which broke upon us out of the wall of fog.
To be fair, although one or two of Sidney Paget's original illustrations are disappointing compared to the description in the text, there is one that's likely to meet the reader's expectations. It shows the climax of the original legend: Hugo's cronies and their horses are panicking in the background, a body is visible sprawled on the moor, and something is outlined in shadow in the foreground, prowling across the page...
The Grimpen Mire gives the hound a run for its money. One false step onto what you thought was solid ground, and you're up to your waist in a slimy bog that sucks you under like quicksand.
"The Three Garridebs" Watson getting wounded, and that threat to Evans.
The hydraulic press in "The Adventure of the Engineer's Thumb." It's the reason the building that contained it burned down.
In "The Disappearance of Lady Frances Carfax," the titular woman turns out to have been kidnapped by a couple of con artists, smothered in chloroform, and locked in a coffin. Holmes and Watson arrive just in time to prevent her from being buried alive, and even then it takes all of Watson's medical expertise to revive her.
"The Greek Interpreter" features a victim who is imprisoned (in a foreign country where he can't speak the language, no less), tortured via starvation, and finally locked in a room to suffocate on charcoal gas.
"The Cardboard Box" is often cited as one of the darkest Holmes stories, and for good reason. Never mind the severedearsin a box. There's no true villain, just a family of unhappy people who brutally and irreparably destroy each other's lives to the point that Holmes wonders aloud whether we live in a Crapsack World, and the murderer'sconfessionishaunting.
The Valley of Fear is aptly named. The Scowrers would be scary enough, but it's the looming background threat of Moriarty that makes the story really ominous. He's brilliant, he's merciless, he has the wealth and power of an enormous criminal empire at his command, and he orchestrates his crimes so carefully and blends in so seamlessly that even Holmes can't prove anything against him. And he wins in the end.
The entire atmosphere and description of "The Five Orange Pips". Even though modern readers can instantly recognize the KKK letters, the way in which Conan Doyle describes the client's past and problems, and his eventual demise, is bloodcurdling.
The adaptation of "The Adventure of the Copper Beeches" in the Granada TV series. The original story is pretty scary (see above) and they manage to do it just as well here. The actor who plays Rucastle manages to make him incredibly creepy and unsettling throughout.
Not to mention, Holmes' hallucination in The Devil's Foot (which is Nightmare Fuel enough by itself), plus his reaction afterward is one of the more... unsettling scenes of the series.
Also in the Granada series, Professor James Moriarty himself, played with chilling effectiveness by Eric Porter. The original series depicts him as having "reptilian" qualities— Porter gives him the appearance of a vulture or some equally ominous bird of prey, and he never speaks above a low murmur even when making threats. "Withdraw or stand clear" indeed.
In The Final Problem, there is a scene where Holmes is walking under the falls and comes upon Moriarty waiting on the bridge, dressed in black and shaking his head...yikes!
In The Copper Beeches, Violet is clearly creeped out by her prospective employer, who seems willing to offer her a suspiciously high sum of money to be a live-in governess based on her appearance alone, and wishes her to rearrange her habits to suit her whims. But despite this, the head of the hiring agency flat out tells her that if she doesn't take the job, they would have to assume that she wasn't serious about seeking employment because she wouldn't take the first offer available despite it coming from someone who obviously made her uncomfortable, and strike her from the list of employees the agency would recommend to clients. Lovely working environment there.
In the finale of The Illustrious Client, the woman that Holmes has been paid to warn away from Baron Gruner is looking through his journal of previous lovers, which is depicted to be a series of photographs of the women followed by descriptions of what he did to them once he tired of them. Then she reaches the end of the book, and her picture is on the last page.
The reveal of what happened to the missing wife of the eponymous ""The Retired Colourman""