Spool And Spool Alike

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.

The important part of the plans are here:

Uno
Dos

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.

With two indexes

With four indexes

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!

1 thought on “Spool And Spool Alike”

Leave a Reply

Your email address will not be published. Required fields are marked *