Wednesday, May 30, 2012

Query Execution Plan

First of all: Ignore the DB Modeling, DB Design mistakes and so on. This post relates to a DB made by a company a decade ago and I will not question the design choices.

So, the topic: migrating from SQLServer 2000 to SQLServer 2008. This is NOT a simple and direct upgrade. There are several issues that will arise when doing this process.

   - Is de DB mirrored?
   - Replicated?
   - Indexes?
   - Users?
   - Filegroups?
   - DTS?
   - How big is de DB?
   - Can it be offline while migrating?

On and on and on...
But after all this has been accounted for and after the migration process was successful, there is still a lot of work to do! Focusing on the issue, consider the following table:

And lets populate the table:

(click to enlarge)

Now, the applications were doing something like this:

(click to enlarge)

In SQLServer 2000, this worked because it's granted that when the field is "dateField" then the Value IS a date (or datetime). However, after the migration to SQLServer 2008, these kind of queries stopped working. The exact same query with the exact same "data" in the tables didn't work. Why?

Finding the issue... Well, the SQL Server 2008 query optimizer actually changed the execution plan and it does the second WHERE clause first:

(click to enlarge)

Meaning, it's trying to convert all Values from the table and then filtering with the LIKE clause. So, It's trying to convert non-datetimes (that actually exist in the table) and "bum"! The "fast" solution was to replace the LIKE with an EQUAL clause:

(click to enlarge)

This way, the optimizer will not change the query execution plan:

(click to enlarge)

And now it works:

My advice, however, was for them to review the database. In future versions of the SQLServer, similar problemas can arise.