The new query hint NO_PERFORMANCE_SPOOL has been added to SQL Server 2016 to allow users to enforce an execution plan that does not contain a spool operator.

A spool operator in an execution plan does not mean that the plan is suboptimal; it is usually a good choice of Query Optimizer. However, in some cases, it can reduce the overall performance. This happens, for instance, when a query or a stored procedure whose execution plan contains a spool operator is executed by numerous parallel connections. Since the Spool operator uses tempdb, this can lead to tempdb contention when many queries are running at the same time. Using this hint, you can avoid this issue.

To demonstrate the use of this query hint, you will once again use the new WideWorldImporters sample database. Assume that you need to display order details for orders picked up by the sales people provided in an input list. To ensure that the execution plan contains a spool operator, this example uses a user-defined function and not the new SQL Server 2016 function STRING_SPLIT. Use the following code to create the function and filter orders with the salespeople from the list:

USE WideWorldImporters; 
GO 
CREATE OR ALTER FUNCTION dbo.ParseInt 
( 
   @List       VARCHAR(MAX), 
   @Delimiter  CHAR(1) 
) 
RETURNS @Items TABLE 
( 
   Item INT 
) 
AS 
BEGIN 
   DECLARE @Item VARCHAR(30), @Pos  INT; 
   WHILE LEN(@List)>0 
   BEGIN 
       SET @Pos = CHARINDEX(@Delimiter, @List); 
       IF @Pos = 0 SET @Pos = LEN(@List)+1; 
       SET @Item = LEFT(@List, @Pos-1); 
       INSERT @Items SELECT CONVERT(INT, LTRIM(RTRIM(@Item))); 
       SET @List = SUBSTRING(@List, @Pos + LEN(@Delimiter), LEN(@List)); 
       IF LEN(@List) = 0 BREAK; 
   END 
   RETURN; 
END 
GO 
DECLARE @SalesPersonList VARCHAR(MAX) = '3,6,8'; 
SELECT o.* 
FROM Sales.Orders o 
INNER JOIN dbo.ParseInt(@SalesPersonList,',') a ON a.Item = o.SalespersonPersonID  
ORDER BY o.OrderID; 

When you observe the execution plan for this query, you can see the Table Spool operator in it, as shown in the following screenshot:

When you execute exactly the same query, but with the query hint OPTION (NO_PERFORMANCE_SPOOL), you get a different execution plan, without the Spool operator, as shown in the following screenshot:

By observing the execution plan, you can see that the Spool operator has disappeared from it. You can also see that the Estimated Subtree Cost is about 10% higher for the plan without the hint (by comparing the yellow SELECT property boxes). Therefore, the Query Optimizer has chosen the original plan with the Spool operator. Here, we used the hint just for demonstration purposes to show that you can enforce another plan, without the Spool operator.

However, if the Spool operator is required in an execution plan to enforce the validity and correctness, the hint will be ignored. To demonstrate this behavior, you will use the next example. First, you need to create a sample table and insert two rows into it:

USE WideWorldImporters; 
DROP TABLE IF EXISTS dbo.T1 
CREATE TABLE dbo.T1( 
id INT NOT NULL, 
c1 INT NOT NULL, 
); 
GO 
INSERT INTO dbo.T1(id, c1) VALUES(1, 5),(1, 10); 

Now, assume that you want to add some of the existing rows into the table, say the rows where the ID has a value of less than 10. At this point, only one row qualifies for this insertion. The following query implements this requirement:

INSERT INTO dbo.T1(id, c1) 
SELECT id, c1 FROM dbo.T1 
WHERE id < 10; 

The execution plan for this query is shown as follows:

When you observe it, you can see the Table Spool operator proudly staying in the middle of the execution plan. However, when you execute the same statement with the NO_PERFORMANCE_SPOOL hint, you get an identical execution plan; the query hint is simply ignored. The reason for this decision by the Query Optimizer is that the spool operator in this plan is used not for optimization, but to guarantee the correctness of the result. To demonstrate this, execute these two statements:

INSERT INTO dbo.T1(id, c1) 
SELECT id, c1 FROM dbo.T1 
WHERE id < 10; 
 
INSERT INTO dbo.T1(id, c1) 
SELECT id, c1 FROM dbo.T1 
WHERE id < 10 
OPTION (NO_PERFORMANCE_SPOOL); 

The following screenshot shows both plans and it is obvious that this is the same execution plan:

Use the query hint NO_SPOOL_OPERATOR when:

• You want to avoid the spool operator in the execution object
• You know that this is a good idea (performance issue is caused by the Spool operator)
• You cannot achieve this with reasonable effort otherwise