Spool operator and trace flag 8690
If you have seen enough query plans, you for sure ran into spool operators (index spool or table spool). It is documented in https://technet.microsoft.com/en-us/library/ms181032(v=sql.105).aspx
The spool operator helps improve a query performance because it stores intermediate results so that SQL doesn’t have to rescan or re-compute for repeated uses. Spool operator has many usage.
For this blog, I’m talking about spool on the inner side of nested loop. If your query plan has this type of spool, you will see something similar like below:
Spools improve performance in majority of the cases. But it’s based on estimates. Sometimes, this can be incorrect due to unevenly distributed or skewed data, causing slow performance.
You can actually disable the spool on the inner side of nested loop with trace flag 8690. This trace flag helped two of my customers last week. I want to point out this is an exception (that I resolved two issues this way in one week). In vast majority of situations, you don’t need to manually disable spool with this trace flag.
I don’t recommend you to disable table spool server wide. But you can use querytraceon to localize a single query if you exhaust other ways to tune the query and find disable table spool helps you.
Jack Li |Senior Escalation Engineer | Microsoft SQL Server