I've just finished the entire body of Sherlock Holmes stories (there are, what, 56 or so?) and I have a few general thoughts:
For the most part, I am disappointed in how Doyle decided to address Holmes as a character. In fact, I find Watson to be a much more interesting case study. Perhaps it was the audience that he was writing for, though I suspect it has more to do with Doyle's focus on nonfiction and the factual. Very rarely is Holmes treated like a character at all - more often he is a Deus Ex Machina who develops new abilities depending on what the problems he is facing.
Ash on the ground? No problem, Holmes wrote a paper on that and is an expert. Attacked? No problem, Holmes is an expert boxer - oh, and he learned jujitsu in Japan as well. Cryptographic message? He wrote a paper on that, too.
The fact that the stories are not written in chronological order doesn't help - it makes it very difficult to think of Holmes as a character who progresses. Indeed, Holmes never progresses and is pretty much always right.
The mysteries themselves are not really what the stories are about - at least, not to me. Of the Holmes novels, I have to say that The Sign of the Four is my favorite - indeed, probably my favorite of all of his writings. It is one of the few instances where Holmes is addressed as a character, and we can see the motive for his drug addiction and loneliness in a world that only stands as a puzzle to be solved to him. The end (after Doyle's insufferable habit to sneak long historical pieces in the end of his books disguised as explanations of crimes) in which Holmes is left with nothing but his drugs, while Watson finds love and the client is satisfied, was uncommonly poetic when compared to the rest of the work.
As for the mysteries themselves, they are interesting enough. It's hard for someone my age to compare, as I've grown up watching crime dramas that were inspired by Holmes by have indeed improved upon the formula.
Watson comes across as a very interesting character, but as Doyle treats both Holmes and Watson primarily as tools to tell the mystery, he isn't developed very well. He devotes most of his life to doing whatever Holmes wants, seeking affection from Holmes, attempting to market the beauty he sees in Holmes to the general public, but never really gets anything in return. It actually seems like Holmes regrets the attention he receives from Watson.
On a side note, if you want to see a modernized and much better-developed characterization of the Holmes character, watch the television sitcom "House M.D." Dr. House is modeled precisely after Sherlock Holmes, but as a more imperfect being. The focus on drug abuse and loneliness due to genius is much more developed in the television show.
And don't scoff at me for comparing television to Holmes - the vast majority of Holmes stories were published in magazine periodicals, which were the equivalent of the time.