Agatha Christie (in, I think, The Labours of Hercules) makes a point about the odd names of detectives, and imagines Sherlock (and Mycroft’s) mother talking with Hercule’s mother. Obviously, Agatha Christie, writing in the 1940s, didn’t think it was a common name. (For that matter, how many Neros do you know? ) I suspect that Doyle wanted a distinctive name (I heard the same thing Elendi;l’s Heir did – Shereringford would be a bit odd, too).
Incidentally, the idea of using surnames as first names directly isn’t unheard of (it’s how Cotton Mather got his distinctive first name), but it’s been pointed out that another way for surnames to migrate to first named is for someone to name a child with the full name of an admired individual as their first and middle names (for example, Martin Luther King), and when the first name gets dropped in favor of the middle name, that name gets a new status as a “first” name. I doubt if “Luther” got is first name status this way, bjut I could see it happening for other names.