Tags
26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to handle large number of insertions in SQL Server?, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, Spools in Execution Plan?, Spools in SQL Server, sql - How do I avoid functions like UPPER in where clause, SQL - Spools, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, SQL Performance Tuning and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server 2008 Query Performance Tuning Distilled, SQL Server Interview Question - How to handle large number of insertions in SQL Server?, SQL SERVER Interview questions, SQL Server Optimization - Parallel Execution Plans, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL Server Query Optimization: Parallel Plans, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?
Types of Spools in Execution Plan – Deep Dive
Today let’s talk about types of spools in detail. The Spool operator saves an intermediate query result to the tempdb database. Means you get all of your data in tempdb and then queries it. Query optimizer uses spools when it thinks that it’s better to put data in the temp table rather than hitting a table again and again using seeks and scans. So what it does is, it picks data from the table and saves that in the temp table as a hidden table and uses it whenever query optimizer requires it. Note – Please note that this hidden table is not same as temp table. Consider this as a hidden table (Work table) for a particular query. I have explained spool operator in details here (https://msbiskills.com/2015/07/26/spools-in-execution-plan-are-they-bad/). Today I am going to talk about the types of spools. So the types of spools are-
1. Table Spool
2. Index Spool
3. Row Count Spool
4. Window Spool
5. Eager Spool
6. Lazy Spool
Let’s first understand each of the above spool in details and then we will proceed for examples-
Table Spool
1. Table Spool is a physical operator.
2. The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database. In tempdb it is stored as a Worktable.
3. It exists only for the lifetime of the query. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.
Index Spool
1. Index Spool is a physical operator
2. The Index Spool operator scans its input rows, placing a copy of each row in a hidden spool file (stored in the tempdb database as a worktable and existing only for the lifetime of the query)
3. After this optimizer creates a Nonclustered index on the rows. This will give the optimizer to use the seeking capability of indexes to output only those rows that satisfy the SEEK:() predicate.
4. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.
Row Count Spool
1. Row Count Spool is a physical Operator
2. The Row Count Spool operator scans the input, counting how many rows are present and returning the same number of rows without any data in them.
3. This operator is used when it is important to check for the existence of rows, rather than the data contained in the rows.
4. For example, if a Nested Loops operator performs a left semi join operation and the join predicate applies to inner input, a row count spool may be placed at the top of the inner input of the Nested Loops operator. Then the Nested Loops operator can determine how many rows are output by the row count spool (because the actual data from the inner side is not needed) to determine whether to return the outer row.
Window Spool
1. Window spool is a logical and physical operator.
2. The Window Spool operator expands each row into the set of rows that represents the window associated with it.
3. Example – In a query, the OVER clause defines the window within a query result set and a window function then computes a value for each row in the window.
4. When using the default window frame (Range Unbounded preceding) window spool operator uses an on disk worktable to process the window, but it doesn’t not spill the data to tempdb.
Eager Spool
1. Eager Spool is a logical operator.
2. The Eager Spool operator takes the entire input, storing each row in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.
3. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.
4. The Eager Spool operator builds its spool file in an “eager” manner: when the spool’s parent operator asks for the first row, the spool operator consumes all rows from its input operator and stores them in the spool.
Lazy Spool
1. Lazy Spool is a logical operator.
2. The Lazy Spool logical operator stores each row from its input in a hidden temporary object stored in the tempdb database.
3. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.
4. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.
5. The Lazy Spool operator builds its spool file in a “lazy” manner, that is, each time the spool’s parent operator asks for a row, the spool operator gets a row from its input operator and stores it in the spool, rather than consuming all rows at once.
Some common examples where we find these kind of spools
1. Let’s start with Table and Index Spool. We have a simple recursive CTE query of a very common business problem i.e. Manager and the employee. Check out the query below-
--
;WITH CTE(EmpName , EmpId, Level,FullyQualifiedName) AS (
Select E.EmpName, E.EmpID, 0 Level
, Cast('.'+E.EmpName+'.' as Varchar(MAX)) FullyQualifiedName
From Employees E Where E.ReportsTo IS NULL
UNION ALL
Select E.EmpName, E.EmpID, c.Level + 1 , c.FullyQualifiedName+'.'+E.EmpName+'.' FullyQualifiedName
From Employees E INNER JOIN CTE c on c.EmpID = e.ReportsTo
)
SELECT SPACE(LEVEL*4) + H.EmpName Hierarchy,SPACE(LEVEL*4) + CAST(H.EmpID AS VARCHAR(MAX)) Hierarchy , * FROM CTE H
ORDER BY H.FullyQualifiedName
--
|
Let’s go through the execution plan. Here we have 2 spool operators – Table and index spool
 Pawan Khowal – Table & Index Spool |
2. Window Spool
-- SELECT * , SUM(ID) OVER (PARTITION BY ID ORDER BY FromPoint) rnk FROM dbo.Area -- |
Let’s go through the execution plan. Here we have 1 spool operator – Window Spool
 Pawan Khowal – Window Spool |
3. Row Count Spool
--
SELECT ID, Vendor
FROM Products
WHERE NOT EXISTS(SELECT 1
FROM Products
WHERE Dates = '20050101'
AND Quantiy > 10)
OPTION(MAXDOP 1)
--
|
Let’s go through the execution plan. Here we have 1 spool operator – Row Count Spool
 Pawan Khowal – Row Count |
Cheers, Thanks for reading !
-Pawan Khowal
MSBISkills.com
Improving my SQL BI Skills 
You must be logged in to post a comment.