So, here we go, in order of the story development:

Trollocs - super scary at first. Attacked Rand and Tam's place on Winternight, their 'fetid breath', human-eating, mega size and animal heads were goddamn creepy in EotW. Since about halfway through that book, they became little more than pawns. Even by the time they blow up the Illuminator's Guild in TGH, they're not really scary anymore, just a bunch of numbers that Rand has to kill, like skinny tough guys in blue jeans on the first level of Double Dragon. Kick 'em twice and they disappear.

Myrddraal - super scary at first. Mysteriously created fear at the very beginning of book 1, were hard to kill, seemingly killed Thom, had creepy raspy voices, and were even scary up to the point where they were going to take the captive gals (before the Aiel saved them) in TDR. Stopped being scary sometime around when Fain was hammering them to tables or whatever.

Ishy - Mr. Scary Furnace Caverns for Eyes was terrifying at first, snapping rat's backs in dreams... and then in reality at the same time. Quickly became a broken record, and stopped being scary sometime between the first and second times Rand killed him. Was a joke by the third.

Mordeth/Fain - super scary when he creepily chatted up the boys, and especially when he grew real big and then disappeared through some hole. After Mashadar and the Ways and the introduction of the Forsaken, seemed a little less scary. Had a resurgence of scary when he merged with Fain, with waves of creepiness coming every so often. Sometime's Fain's a joke, but then he'll do something like kill Perrin's entire family that is just terrifying. Or new tricks, like stabbing rand in CoS, or doing the weird 'time warp' projection of the renegade Asha'man in WH, or whatever he does in the prologue of ToM. Hasn't really stopped being scary.

The skinny inkeeper - this guy creeped me out. Then he turned out to be just another putz. Same with Howal Gode. Scary guy, scary name, and then BOOM! Rand learns how to channel and that's that.

The Forsaken in general - terrifying when the old leather-faced Aginor and Balthamel attack Rand & Co at the Eye... creepy names, old man looks, the mystique of legend, they had all that going for them, and they weren't even less scary for having been killed. Then Lanfear was scary in TGH, mysterious, manipulative, driven but crazy ex-girlfriend obsessive. Things started to turn for the worse once Be'lal got offed without any character development. Then followed a sequence of disappointments, from petty planning conversations overheard in TAR, to Rahvin's reign of Inconvenience in Caemlyn, to Moggy's and Asmo's pathetic captivity, to Sammael's lame-ass 'death'. There were bright spots, like Lanfear's final flaying, girl-hurting tantrum (Cyndane is just boringly obsessed), or Semirhage, who was frankly terrifying. Graendal gets props for surviving, but putting mind-destroying Compulsion on pretty things is not that scary. Semirhage was really the last hope, but TGS made sure that her character got completely ruined in the stupidest way possible before being offed. Really, after the embarrassment of the 'fight' at the end of WH, Semi was the only one that got the benefit of the doubt from me, and brought back a little of my hope when she tortured and killed all the Seanchan nobility. But nope, then she got turned into a one-dimensional embarrassment before she was offed. The forsaken all suck.

Slayer - creepy at first, when he was a wolf-skinning blur in TAR, as we realized he was the merger of 2 men who used to be good guys and are related to central characters, but underuse has left him stale and boring. Kind of exciting when he stabbed those old people, but if that's the best you've got in the last 8 books, you don't really make me shake in my boots.

Shaidar Haran - totally scary for his out-of-the-blue appearance, and even stranger than regular Myrddraal powers. Kind of how the regular Myrddraal were scary in the first book. His smart-ass smile and abuse of the most powerful evil channellers in the world, and general unpredictability/mysteriousness make him ever-changing and still scary.

Gholam - Ripping an old portly scholar to pieces wasn't that terrifying, but the scene created in CoS where he basically takes out all the lady channellers and then Nalesean's throat was gorily terrifying. Made a little more boring by it's single-mindedness on Mat, but still kinda scary.

The Dark One - a huge joke. LIKE SOMEONE ON THE INTERNET WHO CAN'T STOP SHOUTING

So, by that analysis, it seems like the most consistently scary characters are Fain and Shaidar Haran, mostly because they are constantly evolving (as in we are seeing new creepy things from them all the time). Honourable mention to the Gholam.