I have just watched this video by Pinal Dave.
He has a SELECT query that produces ~370k reads in tempdb and ~1200 reads of the table the query is SELECTing from.
He then creates an index (Let's call it, Index1) which removes the tempdb spool and therefore improves the performance of the query. All OK so far.
However, he then creates a further index (we'll call it Index2) and leaves the Index1 intact.
He then runs his query again and despite Index2 not being used, the query performance returns to how it was originally, with the ~370k tempdb spool still in place.
He doesn't actually seem to desribe what causes this (unless I missed it)
The code to reproduce is below (thanks to Martin Smith for providing the Pastebin) This assumes a vanilla version of AdventureWorks with the standard indexes on SalesOrderDetail
SET STATISTICS XML ON;
SET STATISTICS IO ON
GO
-- The query
DBCC FREEPROCCACHE;
SELECT SalesOrderID, ProductId,SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail sod
WHERE ProductID = (SELECT AVG(ProductID)
FROM Sales.SalesOrderDetail sod1
WHERE sod.SalesOrderID = sod1.SalesOrderID
GROUP BY sod1.SalesOrderID);
/*
(11110 rows affected)
Table 'Worktable'. Scan count 3, logical reads 368495, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 2, page server reads 0, read-ahead reads 1284, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
*/
here is the plan before we do anything (nested loops and table spools)
Then we create an index
CREATE NONCLUSTERED INDEX IX_Index1 ON Sales.SalesOrderDetail (SalesOrderID, ProductId) INCLUDE (SalesOrderDetailID, OrderQty);
We can then run the query again and see an improved plan and the tempdb spools gone:
DBCC FREEPROCCACHE;
SELECT SalesOrderID, ProductId,SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail sod
WHERE ProductID = (SELECT AVG(ProductID)
FROM Sales.SalesOrderDetail sod1
WHERE sod.SalesOrderID = sod1.SalesOrderID
GROUP BY sod1.SalesOrderID);
/*
(11110 rows affected)
Table 'SalesOrderDetail'. Scan count 2, logical reads 608, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
*/
Then we create another (not useful for our query) index
CREATE NONCLUSTERED INDEX IX_Index2 ON Sales.SalesOrderDetail (ProductId,SalesOrderID) INCLUDE (SalesOrderDetailID, OrderQty);
Then we run our query again:
-- Run the same query again
DBCC FREEPROCCACHE;
SELECT SalesOrderID, ProductId,SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail sod
WHERE ProductID = (SELECT AVG(ProductID)
FROM Sales.SalesOrderDetail sod1
WHERE sod.SalesOrderID = sod1.SalesOrderID
GROUP BY sod1.SalesOrderID);
/*
(11110 rows affected)
Table 'Worktable'. Scan count 3, logical reads 368495, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 304, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
*/
The reads and the plan are the same as they were before we added any indexes.
I can even try and force the use of IX_Index1:
-- Run the query an force the index
DBCC FREEPROCCACHE;
SELECT SalesOrderID, ProductId,SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail sod WITH (INDEX = IX_Index1)
WHERE ProductID = (SELECT AVG(ProductID)
FROM Sales.SalesOrderDetail sod1 WITH (INDEX = IX_Index1)
WHERE sod.SalesOrderID = sod1.SalesOrderID
GROUP BY sod1.SalesOrderID);
/*
(11110 rows affected)
Table 'Worktable'. Scan count 3, logical reads 368495, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '
and I still get the spools plan
Again thanks to Martin Smith who has observed the following;
If we create IX_Index2 and run the query, the bad plan is used (makes sense as it doesn't help the query)
However, if we then create IX_Index1 and run the query, the good plan is used so suggests that it is not the fact that both indexes exist that is the problem but the order in which they are created.
I also watched this video where Pinal is presenting on the same problem. He seems to suggest that indexes reduce SELECT performance and should be dropped completely due to this problem (Unless I am misunderstanding him)
My first question is: what would cause this behaviour?
My second question is, how common is this behaviour (The second video makes me think it would be fairly common if he suggests not creating indexes at all)
