vk-kirov (4/22/2010)
Hugo Kornelis (4/20/2010)
If SQL Server had an auto-tune feature that automatically adds indexes it beliefs will benefit performance, than you would be right. But no current version of SQL Server has such a feature (and based on recommendations I sometimes see from the DTA, that might as well be for the best).Sometimes SQL Server automatically creates temporary indexes if the optimizer finds out that it will improve query performance.
Ref: Nonclustered Index Spool Showplan Operator http://msdn.microsoft.com/en-us/library/ms189611.aspx
Of course, the index spool operation will not happen in the statement 'SELECT * FROM Persons WHERE PersonID = @PersonID', but it may happen in some statement with many JOINs. And of course, it would be less effective than explicit index creation on PersonID and using that index 🙂
Thanks, vk-kirov. I was not aware of this operator.
From the limited information I could find on it, it will be used if the same data is used at least twice in a query, the second needs only a subset of the rows needed for the first (otherwise a normal spool would be used), and the subset is based on a predicate that is sargable and selective enough to earn back the cost of creating the index.
Do you think you can post a repro script that actually uses this operator? Have you ever seen it used in your code?