SQL Dumbass

Fighting dumbasses, one query at a time…

Regression Testing Should Not Be Optional

So, we spend this past weekend like most weekends, deploying changes to our production environment. In this case, we are migrating several databases from SQL 2000 to SQL 2005. These databases have been migrated from their corresponding dev and test boxes already, meaning we took the SQL 2000 dev version and brought it over to the SQL 2005 dev server, and did the same for test. So, this weekend, we go from “old prod” to “new prod”.

We have migrated many systems to date, and we have a process in place for migrations. At least, on our end there is a process. After our work is complete we pass the database back over the wall and (expect? assume?) that the developers will let us know if they find any issues.

So, we finish up on Saturday afternoon, about 3PM. On Sunday evening at 11:30PM we are sent an email saying that the application is not working. The queries are taking 12 minutes to run, and that they used to only take 2-3 seconds. The developer expects that with the new system and new hardware, everything should be running faster and wants to know what the problem is, and how long will it take to fix.

Okay then. Let’s take that query and run it in test. Wow. Eight minutes. How long did it take on the old test server? Just a few seconds? Okay then, what about dev? Well, it’s slow there too. Did you not see this when you were testing?

<Crickets chirping>

Apparently, due to differences in the environments, this particular team feels that testing is a waste of time. So, they would rather just go right to prod and fix things as they happen. Wait, that is not true. They would rather go to prod and DEMAND that someone fix their problems for them. Hard to believe that in this day and age, the idea of migrating from one version to another would not, in and of itself, warrant the running of the most basic of tests. How can anyone even begin to justify not testing when upgrading to a new version?

So, the developers want everything to work that same with no code changes. We do everything on our end and go back to say that code changes are going to be necessary. They want to know why and feel that “an in-house DBA should be able to solve this issue quickly”. Okay then, apparently you think you are not only the best at what you do, but now you are the person who can best decide what skills a DBA should and should not have.

The end result? Turns out the SQL 2005 optimizer is different than the SQL 2000 optimizer. Who knew? As a result, poorly written queries in SQL 2000 perform even worse on SQL 2005, and need to be rewritten. So, we take one, rewrite it a bit, and get it to under one second. Meanwhile, the developer is asking us to generate a showplan in XML from the SQL 2000 box so that he can force the optimizer to choose the correct plan.

Simply priceless.