July 29, 2013

Last year, I wrote the article Lessons From Geordi Laforge and I had a ton of fun doing it. So I’m writing a bit of a follow-up article for another fictional hero of mine, Sherlock Holmes. It’s a fun post for the summer.

When I looked at Laforge’s character, he was a model for a competent engineer. Now when I look at Sherlock Holmes, it isn’t about engineering. It’s about troubleshooting. So the lessons below aren’t strictly about SQL Server, but about troubleshooting in general.

Sherlock Holmes, the famous detective can tell how good a Chinese restaurant is by its door handle. I’m certainly not that good. But I notice that there are a lot of parallels between what he does and what I do when I’m troubleshooting. I may never be a consulting detective, but a consulting troubleshooter? Maybe.

Read on! I quote some favorite lines from Sir Arthur Conan Doyle. Then I talk about how each line applies to me, or at least me when I’m wearing my troubleshooting hat.

Here we go! Enjoy the quotes:

“Eliminate all other factors, and the one which remains must be the truth”

Eliminating causes! Great! I think of this as narrowing down the possible causes.

It’s super helpful to be able to say, “The problem we’re dealing with lies somewhere in this sproc”, or “The problem lies with contention in this area.”

Most people put this into practice subconsciously. Try to think about what you do. When you’re alerted to a database performance issue, do you check sp_whoisactive first? Or do you go straight to sys.dm_os_wait_stats? Both will give quick information that lets you eliminate a whole host of causes. But which do you check first? There’s no right answer. Experience with your environment may give you a good idea about what’s more likely to be useful.

“An outside eye, a second opinion. It’s very useful to me. Really!”

Okay, this one isn’t from Sir Arthur, it’s a line straight from BBC’s Sherlock so it counts.

I agree with the message here. It is useful to get a second set of eyes on a problem, especially if you’ve gotten stuck. Experience has taught me that you should ask for help earlier than later. And sometimes the simple act of explaining a problem brings new understanding!

And that’s your bonus Sherlock Holmes quote “Nothing clears up a case so much as stating it to another person.”

“It is the unofficial force, the Baker Street Irregulars”

Sherlock Holmes has a team of street kids who have really good access to information in London. He pays them for clues and they can often canvass London a lot better than the police can.

“It was easier to know it than to explain why I know it.”

I know what it’s like to understand something completely but not be able to explain it easily. So I kind of know what Sherlock Holmes is getting at here. For me, I think it’s true for two reasons actually.

The first reason is that I’m not that eloquent, but I’m getting better. I think of my own thought processes as visual rather than verbal. So I sometimes think of my writing as a translation of my thoughts and ideas into English.

The second reason is that with practice, the brain takes shortcuts to understanding. Good chess players can look at a game in progress and just see good moves. And so some other SQL Server experts can look at query plans and assess the important information quickly. I’d like to think I’m getting there.

“It is a capital mistake to theorize before you have all the evidence. It biases the judgment.”

I couldn’t have said it better. This is one of the few Sherlock Holmes quotes that gives advice I agree with and explains exactly why that advice is good. We actually see other inspectors in Sherlock mysteries that develop their own (often incorrect) pet theories. These inspectors are often reluctant to give their theories up even when they are shown to be wrong.

At a place I used to work I’ve been burned by making this mistake. Someone asked for a root cause analysis of a problem that wasn’t understood yet. And I said “Well, I really don’t know yet. The reason might be higher use of [some new feature], but I have to check”. The next day I’m misquoted in someone’s email as “Michael says it’s [that new feature]”.

Woo hoo! I guess I’ve got talent, because to be honest, I know tons of people higher than myself. The people I hold in high esteem matches Tom Larock’s rankings pretty closely.

Some anti-quotes

“Data! Data! Data! I can’t make bricks without clay.”

Who talks like that? Even a hundred years ago, and even in England, it’s hard to imagine. I like the sentiment, but it doesn’t sound right to me. The prose is off for some reason I can’t explain.

“You know a conjurer gets no credit when once he has explained his trick.”

The only reason Sherlock Holmes delays explaining himself is because otherwise the story would be over too soon. The plot’s tension would be resolved too early. Me, I’ll explain my “tricks” to everyone and anyone who will listen. I guess someone might avoid transparency for appearances sake. I don’t play that game. Or I don’t think I do, at least not consciously.

July 3, 2013

Takeaway: I tackled a problem recently and I present a version of the problem here as a challenge. The challenge is to figure out why a query and a stored procedure return different results even though the procedure is defined using the same query.

If no one cracks this one, I’ll leave the answer in the comments in a couple days.

The Setup

Okay, I use AdventureWorks2012 here. I define a table data type IDList and a stored procedure s_GetCustomerProductPurchases:

So Why Are These Results Different?

That’s what I want you to figure out. Try to crack this one before looking at the comments.

SQL Fiddle

Have you used SQL Fiddle yet? It’s a site that allows SQL developers to set up different scenarios for use in online discussions. So if you don’t have Adventureworks2012 handy, I’ve setup up two SQLFiddles here and here. Your job then is to follow these two links and explain why the results are different.