Deep Dive On Index Spools

Hugo Kornelis takes a look at index spools:

The Index Spool operator is one of the four spool operators that SQL Server supports. It retains a copy of all data it reads in an indexed worktable (in tempdb), and can then later return subsets of these rows without having to call its child operators to produce them again.

The Index Spool operator is quite similar to Table Spool, except that Index Spool indexes its data, giving it the option to return a subset, and Index Spool lacks the option to read data from a spool created by another operator. The other two spool operators are quite different: Row Count Spool is optimized for specific cases where the rows to be returned are empty, and Window Spool is used to support the ROWS and RANGE specifications of windowing functions.

Eager and Lazy Spool operators rank high on my list of “troublesome when I see them” operators.  The reason is not so much that eager or lazy spools are inherently bad—they’re not, as they are efficient ways to perform a particular query given the constraints of that query—but if I see one of them in conjunction with a slowly-performing query, it’s a good sign that I want to optimize away the need for spooling.

Related Posts

Testing Inline Scalar UDF Performance

Erik Darling whips up a performance test covering scalar UDF changes in SQL Server 2019: This is a slightly different take on yesterday’s post, which is also a common problem I see in queries today. Someone wrote a function to figure out if a user is trusted, or has the right permissions, and sticks it […]

Read More

Making Non-SARGable Queries SARGable with an Index

Denis Gobo violates Betteridge’s Law of Headlines: This question came up the other day from a co-worker, he said he couldn’t change a query but was there a way of making the same query produce a better plan by doing something else perhaps (magic?) He said his query had a WHERE clause that looked like […]

Read More

Categories

November 2018
MTWTFSS
« Oct Dec »
 1234
567891011
12131415161718
19202122232425
2627282930