SQL Dumbass

For about a week now a member of our team has been helping a customer with some query tuning. We have examined a lot of pieces to the puzzle, but it all boils down to this:

1. Same query


2. Two different version of the database


3. Same server


4. One query runs in seconds, the other takes minutes

Now, given those four facts, what would you believe to be the cause? Or, more exactly, what would you look to examine next? We emphasized that the data and schemas were different in the two databases, thus the reason for different execution times. The customer followed a different path:

“It is the same query, against the same server, but different results. Clearly there is something wrong with the database and/or the server. How quickly can we get Microsoft involved for a resolution?”

Uh…not quickly. Not quickly at all. I would rather not call them and waste their time until after you verify the data and schema (which they had not). So, we asked for someone to give us a list of objects involved in the queries (we knew them, but we wanted a complete list anyway). We identified one table that had a different rowcount in the two databases. In one database it had 500 rows. In the other, it had 400,000 rows. Guess which query was running faster?

Yeah, I really want to call Microsoft and get them involved as to what the problem is with this query. I wonder what their first question will be. Hmmm. Could it be about data, or schemas?

I just had a person come to me and explain that we have a month end this Friday. Last time we had a month end on a friday, the database dumps were “lost”…

Me: “What do you mean by ‘lost’?”

Them: “I don’t know, but when we needed to do a restore, they weren’t there.”

Me: “Which one of the dumps? (they have four to choose from)”

Them: ‘I don’t know. It could have been the 7pm. Or the 4am.”

Me: “Well, the 4am is overwritten nightly, so that makes sense, and has always been the case. But the 7pm would be unusual.”

Them: “Exactly. So we need you to make sure that nothing weird happens this Friday.”

Me: “Excuse me? How do you expect me to guarantee that nothing weird will happen this Friday? On Halloween?”

Them: “We don’t know.”

Me: “So, you don’t know what happened previously, and you want me to guarantee that whatever may or may not have happened before, will not happen again this Friday. Is that correct?”

Them: “Yes. Let us know when you have it all worked out.”

Me: “You’ll be the first to know.”