Category: Spools

Spoolwork

---

I wanted to show you two situations with two different kinds of spools, and how they differ with the amount of work they do.

I’ll also show you how you can tell the difference between the two.

Two For The Price Of Two

---

I’ve got a couple queries. One generates a single Eager Index Spool, and the other generates two.

    SELECT TOP (1) 
            u.DisplayName,
            (SELECT COUNT_BIG(*) 
             FROM dbo.Badges AS b 
             WHERE b.UserId = u.Id 
             AND u.LastAccessDate >= b.Date) AS [Whatever],
            (SELECT COUNT_BIG(*) 
             FROM dbo.Badges AS b 
             WHERE b.UserId = u.Id) AS [Total Badges]
    FROM dbo.Users AS u
    ORDER BY [Total Badges] DESC;
    GO 

    SELECT TOP (1) 
            u.DisplayName,
            (SELECT COUNT_BIG(*) 
             FROM dbo.Badges AS b 
             WHERE b.UserId = u.Id 
             AND u.LastAccessDate >= b.Date ) AS [Whatever],
            (SELECT COUNT_BIG(*) 
             FROM dbo.Badges AS b 
             WHERE b.UserId = u.Id 
             AND u.LastAccessDate >= b.Date) AS [Whatever],
            (SELECT COUNT_BIG(*) 
             FROM dbo.Badges AS b 
             WHERE b.UserId = u.Id) AS [Total Badges]
    FROM dbo.Users AS u
    ORDER BY [Total Badges] DESC;
    GO

The important part of the plans are here:

The important thing to note here is that both index spools have the same definition.

The two COUNT(*) subqueries have identical logic and definitions.

Fire Sale

---

The other type of plan is a delete, but with a different number of indexes.

/*Add these first*/
CREATE INDEX ix_whatever1 ON dbo.Posts(OwnerUserId);
CREATE INDEX ix_whatever2 ON dbo.Posts(OwnerUserId);
/*Add these next*/
CREATE INDEX ix_whatever3 ON dbo.Posts(OwnerUserId);
CREATE INDEX ix_whatever4 ON dbo.Posts(OwnerUserId);

BEGIN TRAN
DELETE p
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ROLLBACK

Differences?

---

Using Extended Events to track batch completion, we can look at how many writes each of these queries will do.

For more on that, check out the Stack Exchange Q&A.

The outcome is pretty interesting!

• The select query with two spools does twice as many reads (and generally twice as much work) as the query with one spool
• The delete query with four spools does identical writes as the one with two spools, but more work overall (twice as many indexes need maintenance)

Looking at the details of each select query, we can surmise that the two eager index spools were populated and read from separately.

In other words, we created two indexes while this query ran.

For the delete queries, we can surmise that a single spool was populated, and read from either two or four times (depending on the number of indexes that need maintenance).

Another way to look at it, is that in the select query plans, each spool has a child operator (the clustered index scan of Badges). In the delete plans, three of the spool operators have no child operator. Only one does, which signals that it was populated and reused (for Halloween protection).

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Spools Of A Feather

---

There are plenty of spools in query plans, and they’re all pretty well labeled.

• Index
• Table
• Rowcount
• Window

They can be either eager or lazy.

An eager spool will take all the rows at once and cache them, and a lazy spool will only go get rows as needed.

But what else can act like a spool?

Phases On

---

In general, a blocking operator, or as my upside down friend Paul calls them, “phase separators” can act as a spool.

A spool, after all, is just something that keeps track of some rows, which is exactly what a Sort or a Hash do.

They keep track of rows that arrive, and either sort them according to a need, or create a hash table of the value.

While either of these happen, any downstream work in the query have to wait for them to complete. This is why they’re called blocking, stop and go, or, more eloquently, phase separators.

Eager spools have the same basic feature: wait for all the rows from downstream to arrive, and perform an action (or just feed them to another operator).

Here’s an example of a Sort acting as a spool:

DROP TABLE IF EXISTS #waypops;

CREATE TABLE #waypops
(
  UserId INT
  , PRIMARY KEY NONCLUSTERED (UserId) WITH (IGNORE_DUP_KEY = ON)
);

INSERT #waypops WITH(TABLOCKX) 
       ( UserId)
SELECT b.UserId
FROM dbo.Badges AS b
WHERE b.Name = N'Popular Question';

The Sort is in the same order as the index it’s reading from, but just reading from the index wouldn’t provide any separation.

Just Passing By

---

This is weird, niche stuff. That’s why I’m posting it on a Friday. That, and I wanna bully someone into writing about using a hash join to do the same thing.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.