Bit Param Evaluation alters Execution Plan

Question

Bit Param Evaluation alters Execution Plan

nwoolls

Valued Member

Points: 58
More actions
March 23, 2010 at 10:42 am

#213094

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.
Link: Zip file with SQL statements and XML execution plans
Thanks in advance!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

It seems like a case of 'parameter sniffing'

http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx"> http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx

One option is to use the recompile hint (well it is on 2005 i am not too sure about 2008)

nwoolls Valued Member Points: 58 More actions · Answer 2

Thanks very much for the feedback. I like this explanation a lot, it seems to fit the bill. I also read the following:

http://www.sqlmag.com/article/sql-server/-using-the-recompile-query-hint-to-solve-parameter-sniffing-problems.aspx

This also seemed to fit very well to what I am seeing.

However, I tried adding each of the following to the query:

Option (Recompile)

and

Option (Optimize For (@bOnlyUnmatched = 1))

In both cases I got the same result - a 20 min execution - with the same execution plan as before.

Paul White SSC Guru Points: 150467 More actions · Answer 3

Strictly speaking, this is not a parameter-sniffing issue. SQL Server picks a bad plan on this occasion due to a lack of accurate statistics. This results in SQL Server underestimating the cardinality, and producing a plan with a poor join order.

Interestingly, this often comes about due to improvements in the Query Optimizer (QO) since SQL Server 2000. Where the 2000 QO would make a guess at cardinality and come up with a good plan, later versions have more options, look into things more deeply, make different assumptions in difficult situations, and may end up producing a worse plan as a result. The cause, therefore, is that the products concerned are different 😉

In this situation, it seems to me that this procedure has reached a turning point. While you can certainly produce a reasonable plan by employing the 1 = CASE trickery, it does little to improve readability or maintainability.

It seems to me that there are two separate procedures here, masquerading as one. I would recommend splitting it into two - one for the case when only unmatched data is required, and the other to return all data.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

nwoolls Valued Member Points: 58 More actions · Answer 4

Thanks for the response Paul. I had actually broken the statement into two, as you suggested, as a work around for now.

A little background on that syntax: we used to use multiple simple statements, as you suggested, evaluating the param(s) first. But changing one select's fields, or where clause, meant changing each instance in the SP, potentially resulting in missed changes and bugs. This "consolidation" of the queries was done to try to improve maintainability and readability. That said, if it comes with performance degradation in some cases under 2005/2008, it will obviously have to go in this case 🙂

I do agree that it seems like, under 2000, the optimizer assumes a value of 1 for the param, which results in a more optimal join whether the param is 0 or 1. Under 2008, it seems like the plan assumes a value of 0, which works great when the value is 0 but the chosen execution plan works horribly when the value is 1.

Paul White SSC Guru Points: 150467 More actions · Answer 5

nwoolls (3/24/2010)
I do agree that it seems like, under 2000, the optimizer assumes a value of 1 for the param, which results in a more optimal join whether the param is 0 or 1. Under 2008, it seems like the plan assumes a value of 0, which works great when the value is 0 but the chosen execution plan works horribly when the value is 1.

It's really not so much that. In the 'bad plan' you will see a Filter operator as one of the inputs into the concatenation operator. That is a special kind of Filter, new for SQL Server 2005, called a start-up filter. The filter is evaluated first, and the query tree to its right is only executed if the start-up filter returns true.

This is just one of the new options available to the QO, and in this case it decided it made sense...when it didn't 😉

Complex subject, this.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

matzer SSCrazy Eights Points: 9727 More actions · Answer 6

IIRC you need to do some thing like the following in order to let the startup filter work properly:

And (

@bOnlyUnmatched = 0 -- offending line

Or (@bOnlyUnmatched = 1 and Not Exists( ... ))

Peter

WayneS SSC Guru Points: 95460 More actions · Answer 7

nwoolls (3/24/2010)
Thanks for the response Paul. I had actually broken the statement into two, as you suggested, as a work around for now.
A little background on that syntax: we used to use multiple simple statements, as you suggested, evaluating the param(s) first. But changing one select's fields, or where clause, meant changing each instance in the SP, potentially resulting in missed changes and bugs. This "consolidation" of the queries was done to try to improve maintainability and readability. That said, if it comes with performance degradation in some cases under 2005/2008, it will obviously have to go in this case 🙂
I do agree that it seems like, under 2000, the optimizer assumes a value of 1 for the param, which results in a more optimal join whether the param is 0 or 1. Under 2008, it seems like the plan assumes a value of 0, which works great when the value is 0 but the chosen execution plan works horribly when the value is 1.

Putting multiple conditions in a "catch-all" query will frequently return poor execution plans. I think that Paul is right-on with splitting these out into different procedures, based on the parameter. An alternative might be to use dynanic sql, but that comes with it's own issues and security risks.

To help in preventing missing changes, document in each procedure (the root one that calls the various ones, and all of the various ones) what all procedures need to be looked at when changing any of them. Oh, wait... I forgot that no one documents their code...:w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

nwoolls Valued Member Points: 58 More actions · Answer 8

Paul White NZ (3/24/2010)It's really not so much that. In the 'bad plan' you will see a Filter operator as one of the inputs into the concatenation operator. That is a special kind of Filter, new for SQL Server 2005, called a start-up filter. The filter is evaluated first, and the query tree to its right is only executed if the start-up filter returns true.
This is just one of the new options available to the QO, and in this case it decided it made sense...when it didn't 😉
Complex subject, this.

WayneS (3/24/2010)
Putting multiple conditions in a "catch-all" query will frequently return poor execution plans. I think that Paul is right-on with splitting these out into different procedures, based on the parameter. An alternative might be to use dynanic sql, but that comes with it's own issues and security risks.
To help in preventing missing changes, document in each procedure (the root one that calls the various ones, and all of the various ones) what all procedures need to be looked at when changing any of them. Oh, wait... I forgot that no one documents their code...:w00t:

Thanks for the suggestions and feedback both of you. I'll take a closer look at some of the other similarly structured queries to see if those are suffering from poor execution plans based on your advice.

Paul White SSC Guru Points: 150467 More actions · Answer 9

Thanks.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi