It appears to me a lot of people's impression of Sherlock Holmes' drug use out of context is "he's running around solving murders while coked out of his mind" which is really funny BUT!
As someone who reads acd canon & has the autistic urge to correct people about my interests, I'd like to let it be known that he actually primarily uses drugs between cases, because he gets painfully bored with nothing to do, stating "my mind rebels at stagnation." This is because he has adhd but the diagnosis hadn't been invented yet. Anyway
This can still be funny because it means the rest of the time he's acting like that while completely sober. He's just quirky. He IS a cokehead but it actually calms him down. Because he needs adderall
You may hear "Sherlock Holmes does cocaine" and think "oh, that explains why he's so fucking weird" but you would have it backwards. He does cocaine because he's already just Like That. He does this specifically when he's understimulated. They didn't have adhd meds in victorian london he's taking whatever stimulants are available. And That's why he's coked out of his mind. But Not while currently working on a case, because that keeps him busy
*note: I have adhd I know how it is
This is exactly why I enjoyed the modern version of the Sherlock TV show "Elementary", which presented Sherlock as a recovering drug addict and Watson as his recovery support/coach/babysitter. Sherlock explains his addiction as, he needed the drugs to cope with the modern world, with the noise, the people, the demands and expectations. He said if he was born in a quieter time, maybe he wouldn't have been an addict.
I like the extrapolation; that bringing Sherlock into our modern world means pushing his coping mechanisms (including a sex addiction, which I know a lot of people thought was too OOC) much further. OG Sherlock was in control of his drug use. But our world is too much.