SQL Server 2008 - Bit Param Evaluation alters Execution Plan

Question

I have been working on migrating some of our data from Microsoft SQL Server 2000 to 2008. Among the usual hiccups and whatnot, I’ve run across something strange. Linked below is a SQL query that returns very quickly under 2000, but takes 20 minutes under 2008. I have read quite a bit on upgrading SQL server and went down the usual paths of checking indexes, statistics, etc. before coming to the conclusion that the following statement, found in the WHERE clause, causes the execution plan for the steps that follow this statement to change dramatically:

  And (
    @bOnlyUnmatched = 0 -- offending line
    Or Not Exists(

The SQL statements and execution plans are linked below.

A coworker was able to rewrite a portion of the WHERE clause using a CASE statement, which seems to “trick” the optimizer into using a better execution plan. The version with the CASE statement is also contained in the linked archive.

I’d like to see if someone has an explanation as to why this is happening and if there may be a more elegant solution than using a CASE statement. While we can work around this specific issue, I’d like to have a broader understanding of what is happening to ensure the rest of the migration is as painless as possible.

Zip file with SQL statements and XML execution plans

Thanks in advance!

Answer 1

Not sure exactly. In your "Doesn't Work - Plan" the estimated and actual rows are massively different though. Do you have AUTO STATS switched on?

Answer 2

We experienced similar problems a few years back in our migration from 2000 to 2005. The error we were seeing was actually an invalid cast error. I think I found the thread here

The query optimiser has much more freedom in SQL Server >=2005. The CASE solution is probably the best route.