Ask 2 readers what makes a great mystery and you'll get 2 answers, 6 for 6, 10 for 10, and so on. But they'll probably agree that the sleuth, protagonist, hero, or whatever has to appeal to the reader. Maybe he or she is sympathetic, like Odd Thomas, or heroic like Jack Reacher, or irritating-but-determined like Columbo. Somehow we have to want him to succeed, and we have to feel at the end that he has, even in noir, where success isn't always very successful.
How do sleuths go at their jobs? Writers have three basic explanations for a sleuth's tenacity. First, he may be a professional, a cop, a private eye, or a lawyer. It's his job to find the bad guy/prove the accused is innocent. Second, he has a personal stake: a friend, a relative, or he himself is in trouble, suspected of the crime, chased by the killers, affected in some way by a possible outcome. Finally, sometimes the sleuth just wants to know, can't let it drop.
Readers accept that mystery protagonists are people with more-than-usual curiosity. Most of us let the police handle crime; seldom do we "do a little digging" for ourselves. But in mystery, even police officer sleuths are more than normally dedicated -- driven, we might say -- to catch the killer or killers. That's part of their charm: they do what we ourselves would never do.
So what makes a great sleuth? The ones readers love best have unique qualities. In the past they were smarter-than-average types like Sherlock Holmes and Hercule Poirot, and that type is certainly still popular. Today's sleuth tends to struggle with something, though, anything from alcolhol abuse to a nagging mother to a dead man who speaks only to him. His personal demons make it harder for him to proceed and more interesting for us as readers.
Mystery has grown up, and authors and readers constantly look for new sleuths with unique voices. So we get serial-killer sleuths, child sleuths, time-traveling sleuths, and who knows what else...that's the mystery, at least part of it.