I really wanted to like Stephen King's TV miniseries Storm of the Century. The concept was fascinating. But I was disappointed. I wanted to know more about the nature of the beast, but the film was more about the bickering of the townspeople. Maybe that's my problem -- I don't always find King's work to be about what one might call "mystery" or some inverted version of what Rudolf Otto called "the sacred/holy -- das Heilige." Instead, King's work tends to me to be more about creating some "thing" so that he can write about peoples' reactions to it. That's a different kind of movie in my book.
You're absolutely right about that, Swithin. King's focus has always been (from what I've read/seen) more on the dysfunctional families or surrogate families than on the actual supernatural or alien nemeses. There was a popular horror author in the 1970's and 1980's named Graham Masterton who excelled at depicting the "creatures", although his books tended to have excessive sex and grotesque violence, too. He had a string of novels, unconnected for the most part, that each featured a menace from a different culture and/or religion, and his detailing of the various mythos of these "evils" was very enjoyable. Clive Barker also wrote "good monsters", but his human protagonists often felt undercooked or unappealing.
Pet Sematary as a film has maintained a following primarily on the well-done bits with the resurrected boy in the last act. It's one of the few truly bloodthirsty kid menaces in films that seemed child-like. The nightmares involving the sickly relative were also unsettling.
I've stated my adoration for The Shining before. My second favorite King movie is The Dead Zone but I may have that listed with the SF movies. I treat psychic stories on a case-by-case basis as to whether they are more horror or SF. Carrie is very good. I liked parts of Christine. The original Salem's Lot is excellent for a TV movie of its time. Cujo I found dull, and would probably like less now, due to the animal element. Firestarter was enjoyable, if a bit campy, but it's definitely SF. Creepshow is a lot of fun. The more dramatic King films have been good: Stand By Me, The Shawshank Redemption, Misery, The Green Mile, Dolores Claiborne.
A minor film based on King that I liked was The Night Flier. It had some grisly originality. And I really liked The Mist.