StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server 2008 - Bit Param Evaluation alters Execution Plan
text
Body
copied!<p>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:</p> <pre><code> And ( @bOnlyUnmatched = 0 -- offending line Or Not Exists( </code></pre> <p>The SQL statements and execution plans are linked below. </p> <p>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.</p> <p>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.</p> <p><a href="http://installationexcellence.com/downloads/MSSQL%2B2008%2BQuery%2BIssue.zip" rel="nofollow noreferrer">Zip file with SQL statements and XML execution plans</a></p> <p>Thanks in advance!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload