When a query plan goes wrong

I'm working on a whitepaper on when query plans go wrong, and I'm amazed at the number of scenarios that they can go wrong.

A recent one I just came across was related to SSIS. If you use the table or view option for OLEDB sources you can get really bad plans. Piers did some digging and found that to get the meta data for the query it issues a set rowcount 1. He found this resulted in a plan being cached that when used without the set rowcount was awful for performance. http://piers7.blogspot.com/2009/06/nasty-ssis-2008-issue-with-table-or.html

Below is a repro and highlight a typical bad plan situation. A plan is built on the assumption of processing a very small number of rows and so chooses a nested loop join. Nested loop joins dont' perform if you are processing large numbers of rows, do to the lookup nature.

In this repro you will see that we exceute the same logical query three times. The first and second queries are identical and so will reuse the same query plan, that is cached by the first one being executed, but due to the comments the third one will generate a new plan. Because the third one is not limited to 1 row it generates the best plan for readding all the data (should be a MERGE join in this case).

So the lesson here is be very careful when using rowcount to look at a query if you are going to be executing the same query to get all the data.

set rowcount 1

go

select H.SalesOrderID, H.SalesOrderNumber , O.ProductID , O.UnitPrice

from AdventureWorks2008.Sales.SalesOrderHeader H

join AdventureWorks2008.Sales.SalesOrderDetail O on H.SalesOrderID = O.SalesOrderID

go

set rowcount 0

go

select H.SalesOrderID, H.SalesOrderNumber , O.ProductID , O.UnitPrice

from AdventureWorks2008.Sales.SalesOrderHeader H

join AdventureWorks2008.Sales.SalesOrderDetail O on H.SalesOrderID = O.SalesOrderID

go

--Use some text to change the query to get a new plan

select H.SalesOrderID, H.SalesOrderNumber , O.ProductID , O.UnitPrice

from AdventureWorks2008.Sales.SalesOrderHeader H

join AdventureWorks2008.Sales.SalesOrderDetail O on H.SalesOrderID = O.SalesOrderID

go

 


-
Published Wednesday, June 17, 2009 3:22 PM by simonsabin

Comments

Wednesday, June 17, 2009 8:39 PM by simonsabin

# re: When a query plan goes wrong

Allan Mitchell of SQLIS.com fame and fellow SQL MVP pointed me to a post from the sqlperf blog that highlighted exactly this issue.