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
-