Hemlock is quite right that the blind spaces in the rules make for problems in the sort of games you run, and that the more space your rules cover well, the more ways your mechanics impact the game. I don't think he's quite right that you can't accomplish this by something like, "Has advantage on every skill used in investigation.", but that without an investigation subsystem that isn't going to be a really exciting part of the game or something DM's will think to do.
Here I have to point out that I'm a computer programmer, and I choose my words carefully. I believe I wrote that you can't create a "mechanically interesting" class without mechanics; this is not to be confused with saying that you can't create a class that has advantage on every skill used in investigation, but rather a value judgment that such a class doesn't meet my threshold for "interesting."
I don't mind admitting when I'm wrong, but in this specific case I believe I already accounted for the argument you're making here as part of my initial statement.
(See, all of these topics - class design, skill challenges, breadth of complexity, depth of complexity, why combat is fun - really are tied together.)
As for "Sherlock Holmes", there is room. You might think of "journalist", "mathematician", and "lawyer" is being very small amounts of what the class that allows Sherlock Holmes actually would cover. "Pursue Rumors", "Spread Gossip", "Make Use of Leverage", "Enumerate Anything", "Dazzle them with Jargon", and "Make a Legal Bargain with the Universe" could really be things, and they'd fit in a generic fantasy world.
I wouldn't mind making room for Sherlock Holmes, but I think the way I would choose to do it is by inventing new affordances first (i.e. rules for solving mysteries), and then write the character class in terms of those affordances. I think there isn't any reason you couldn't introduce the notions of Scenes, Find a Clue, and Noticing clues into D&D--the example I gave was intended to still be in the D&D genre.
A DM who says "You've just entered a Scene" is making a statement about the mode of play just as emphatically as a DM who says, "Okay, combat is starting, please declare actions." (Or "roll initiative", for those who favor that sort of thing.) D&D being what it is, you could probably apply the Scene rules to traps as well as to longer-term mysteries.
Remember, there's a distinction between "what the PCs do" and "how the players do it." A large part of the art of running a good game consists in making sure that the right things are happening at the metagame level, at the level of granularity that will make it fun for the players sitting at the table. As the Alexandrian points out, you
could conduct dungeon crawling by having players roll an abstract "dungeoneering" check and then narrating the monsters they killed and the treasure they find, and you
could let players navigating cities by describing the city block they're on and the buildings to the north/south/east/west and then asking them which direction they walk in--but in practice, none of us actually does either of those things. We choose the level detail we present to the players, and the level of detail of action declarations which we implicitly are asking from them, based on the context of what is actually happening. When I suggest creating rules for things like Find a Clue/Solve a Scene, it is in the context of offering more affordances to the players at the metagame level, so they know what kinds of action declarations make sense. It doesn't mean that action resolution has to get new rules.
Simple example:
PCs enter a dungeon. DM calls for a DC 15 Perception check. Any PCs who pass it Notice a Clue: "There are tracks in the stone floor, occasional scratches or small holes in the stone itself. If these are footprints, whatever made them has razor-sharp talons and feet over sixteen inches long."
Later on they find a room with two holes in the wall. A DC 15 Investigation check yields another Clue: "A chain used to run through these holes--probably with manacles attached to both ends, keeping a large creature chained to this wall."
Later on they stumble across an old tapestry. If any of the PCs examines the tapestry, they find writing in a language which turns out to be Drow. If they can read it, it tells of the political struggles between two noble houses, and how one house lost the struggle and was exiled here but was determined to regain favor. This also counts as Noticing a Clue.
Finally, the PCs came here expecting to find drow warriors, but no matter where they go in the dungeon, the place is empty. There is furniture and some treasure, but no drow, no bodies, and no weapons or equipment. If a player comments on this to the DM, the DM will inform him that he has Noticed a Clue.
Once all four Clues are acquired, the DM informs the PCs of a vital piece of information: this dungeon is home to a half-drow, half-demon creature called a Draegloth, which was bred by the exiled house in a risky bid to regain power, but it has now broken free of the drow, killed all of their warriors who did not flee, and eaten all of their bodies. A DC 20 Arcana or History check will provide additional details on the likely capabilities of a Draegloth.
A Sherlock would be able to make an intuitive leap to receive this information as soon as he acquired three Clues, instead of four.
Obviously, the value of such a class is directly related to the value of the hidden information the DM includes in his campaign. Information such as "the Duke is really a Rakshasa" or "this entrance to the tomb is a sham--there must be another way in" or "the Deryni and the Murgos aren't allies--they're at war with each other as well as us!" could be quite valuable.
