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 ...
