SQL Server Performance Tuning – a SQL Server tuning story: Avoid That Damn Table Spool!

SQL Server Performance Series – Execution Plan Deep Dive

Hi SQL Server Guys,

Welcome to a SQL Server tuning story.

This post is inspired by something that happens very often in real projects:

a query that technically works… but performs terribly.

During my work I spent a lot of time optimizing queries, sometimes by adding indexes, sometimes by rewriting them.

And the interesting part is that sometimes a very small rewrite can completely change the execution plan.

Today's example is exactly one of those situations.

Enjoy the reading! 👍

PS: In case you missed my last post: The SQL Server Query That Looks Fast ….Until It Hits Production. Why?

The Problem: Table Spool

A few days ago I analyzed a query whose execution time was considered too slow by a client.

When looking at the execution plan, one operator immediately caught my attention:

Table Spool

Table Spool operators are not always bad.

But in many cases they indicate that SQL Server is forced to store intermediate results in a temporary structure, which can become expensive.

Reproducing the Scenario

First let's create a small environment to reproduce the problem.


Create table PriceList 
(
    Id int identity(1,1) primary key,
    Code varchar(20)
)

Insert into PriceList(code)
select top 1000 right('0000'+cast(row_number() over(order by id) as varchar(4)),4)
from artico

Insert into PriceList(code)
select top 4000 right('1000'+cast(row_number() over(order by id) as varchar(4)),4)
from artico

Now the Orders table.


Create table OrdersList
(
    Id int identity(1,1) primary key,
    Idpl_Normal int,
    Idpl_Special int,
    Idcustomer int
)

Insert into OrdersList(idpl_Normal,Idpl_Special,idcustomer) values (1,2,1)
Insert into OrdersList(idpl_Normal,Idpl_Special,idcustomer) values (1,2,2)
go 100

Insert into OrdersList(idpl_Normal,Idpl_Special,idcustomer) values (1,2,3)
go 200

Insert into OrdersList(idpl_Normal,Idpl_Special,idcustomer) values (1,2,4)
go 9699

The Problematic Query

Here is the original query:


Select 
  id
From PriceList n
Where Exists
(
    Select d.Id
    From OrdersList d
    Where (d.idcustomer = 1)
    and
    (
        (d.idpl_Normal = n.ID)
        or
        (d.Idpl_Special = n.ID)
    )
)

Looking at the execution plan we see something interesting.

OrdersList table scan
Table Spool operator

The Table Spool alone is responsible for about 87% of the execution time.

Logical Reads

Pages read for the original query:


Table 'OrdersList'. logical reads 33
Table 'PriceList'. logical reads 15

The query works — but the execution plan is clearly not optimal.

Rewriting the Query

Now let's try a slightly different formulation.


select id
from PriceList n
where exists
(
    select d.Id
    from OrdersList d
    where d.idcustomer = 1
    and d.idpl_Normal = n.ID
)
or exists
(
    select d.Id
    from OrdersList d
    where d.idcustomer = 1
    and d.Idpl_Special = n.ID
)

The logic is identical.

But the execution plan changes dramatically.

The Table Spool operator disappears.

Logical Reads After Rewrite


Table 'OrdersList'. logical reads 66
Table 'PriceList'. logical reads 4

Yes, the OrdersList table is read twice.

But the removal of the Table Spool drastically improves performance.

Query Cost Comparison

Estimated cost comparison:

Original Query: 0.594
Rewritten Query: 0.097

That is a huge difference.

A quick benchmark showed around:

~60% faster execution time

The Real Lesson

SQL Server performance tuning is often about experimentation.

Small changes in query structure can produce very different execution plans.

And sometimes the optimizer chooses a plan that is not the best possible one.

That is where experience and a bit of creativity help.

Final Takeaway

Table Spool operators are not always bad.

But when you see one dominating the execution plan cost, it is often worth testing alternative query formulations.

Sometimes a simple rewrite is enough to dramatically improve performance.

*** P.S. If Table Spool operators are not always bad, OR operators can represent a problem. An OR operator can led SQL SERVER to use an index in scan mode and not in seek mode... We will see in the next posts how to solve this... ***

And remember…

No SQL Server was harmed during these benchmarks 😉

See you in the next deep dive!

Search This Blog

SQL Server Performance Blog where milliseconds matter