There are two general threads that drugs and alcohol follow in The Adventures of Sherlock Holmes. First, there are lots and lots of examples of substance abuse ruining lives and destroying otherwise noble people. See, for example, opium addict Isa Whitney at the beginning of "The Man With the Twisted Lip," or drunkard Henry Baker in "The Blue Carbuncle."
At the same time, there is Sherlock Holmes's own cocaine use, mentioned in passing in "A Scandal in Bohemia." Cocaine would have been legal in Conan Doyle's day, so the social meaning of taking the drug would be quite different than it is today. At the time, Holmes's drug use might have underlined not only the extreme activity of his brain, but also his generally Bohemian lifestyle. Even so, Watson clearly disapproves of what he sees as Holmes's moral weakness in relation to his drug use and, in later Holmes episodes, Watson gradually persuades his friend to quit.
Holmes's cocaine use is a device for characterization to show how little he cares about social mores or restrictions.
Concerned about representing drug use too positively in the character of Holmes, Conan Doyle uses Isa Whitney to demonstrate the potentially dangerous effects of substance abuse on a person's life and character.