Spoiler warning: if you haven't seen the Series Four finale of "Sherlock" yet, and are planning to do so, you should do that before reading any further.
As revered as the Sherlock Holmes stories are, it does seem that they're maybe not terribly well suited to adaptation - an awful lot of the stories consist of five minutes of the problem being laid out, five pages of Holmes checking "just a few things", and then five pages of Holmes laying out the explanation. There are some exceptions, of course, but it when I read through the stories in quick succession last year it was very noticable just how many fell into that pattern.
What this means is that an adaptation has a fairly small number of 'good' sources to adapt, and then there's a need to figure something else out - either write a new mystery from whole-cloth, or use the loose plot outline with some serious padding, or mash some stuff together, or something like that.
And what that probably means is that an adaptation of the Sherlock Holmes stories is probably best if it has only very few episodes - adapt the 'good' sources, and then stop. (Where 'good' in this case means "easy to adapt".)
Which brings us to "Sherlock", which was excellent for two series (six episodes), ended on a very strong "how did he get out of that one"... and just hasn't been the same since. (Which, to be fair, matches up neatly with the source.)
Series three was okay, but in hindsight showed very clear signs that things were going wrong (notably the failure to explain the answer to "how did he get out of that one?). Then there was the much-hyped 'period' piece, that could have been great had they actually done that rather than gone with "and it was all a dream".
And then series four fell to pieces.
Seriously, when your plot hinges on "Sherlock has a secret sister that he's forgotten all about... oh, and she has superpowers", you should probably stop and think again.
(Though my favourite bit was where Mycroft and Moriarty came face-to-face, and Mycroft lamented that Moriarty remained a "person of interest" but would remain at large until he committed some sort of crime... while they were literally standing on a secret prison used to 'disappear' inconvenient people!)
Ultimately, though, this last episode of "Sherlock", and increasingly the last two series, has shown much the same problems that have dogged much of Moffat's tenure as showrunner on "Doctor Who" - he's had some spectacular early success, and thereafter has been trying so hard to top his previous effort that he seems to have replaced 'clever' with 'convoluted'. And some of the output has, I'm afraid, degenerated into abject nonsense.
Which, conventiently, suggests to me that the best thing to happen with a Steven Moffat show is for it to run for a fairly short time (one or two series at most), and then stop. Because, "Day of the Doctor" aside, he never did manage to come close to his first season running "Doctor Who", and "Sherlock" was definitely at its best in those first two series.
(One other thought occurs. Some time ago, I did a post about "The Death of Superman", and how it damages the films - since the story is so big that they have to address it, without necessarily being very good. It would seem that "The Final Problem" maybe the same for Holmes - any adaptation will want to address it, and they'll want to use Moriarty as early as possible. But "The Final Problem" really should be the last story in any adaptation, and end with Holmes dying - thus neatly avoiding the question that nobody, including the author, has ever managed to answer convincingly: "how did he get out of that one"?)