The Eager Index Spool aka The Spiteful Spool

Think of a time when you went to someone for help on a problem. Rather than walk you through how to solve the problem, that someone just groans, speeds through solving it for you, and sends you on your way without any explanation.

Did you learn anything? No.

Will you need help again next time that same problem comes up? You bet.

This is kind of what an eager index spool is. It does the work for you and sends you on your way without explaining what you can do to fix it the next time around.

In SQL Server terms, an eager index spool in an execution plan means that a set of data was loaded into tempdb and indexed in order to return a result. That doesn’t sound like the worst thing in the world since SQL Server works with indexes all the time. But the next time the query runs, that index is going to need created in tempdb all over again, all while not giving you a flashy hint that creating an index permanently would help.

Let’s look at an example.

Eagerly Anticipating

We’ll start by running this ugly stored procedure I added to the StackOverflow2013 database. If we call it to grab some stats on user post counts for the user Batman4Ever, it takes a bit to run:

These are our stats when we plug our messages into Statistics Parser:

and this is our plan:

Grab a Shovel

We have no missing index recommendations; however, highlighted is the Eager Index Spool which tells us we can probably improve our performance with an index. Let’s take a look at the XML of the plan to see more details on the eager index spool.

The first part that jumps out is OwnerUserId on the Posts table which we do not have an index for. For the sake of testing, let’s add an index on OwnerUserId:

CREATE INDEX OwnerUserId on Posts (OwnerUserId);

We’ll run our stored procedure again and see what changed:

Our query before the index was almost 56 million logical reads taking about 2 minutes to complete. After only the OwnerUserId index? 14 logical reads and completing in under a second.

We can do even better. Our new index for OwnerUserId was used and we no longer have the eager index spool; however, we still needed a key lookup for the LastActivityDate which we also saw earlier in our eager index spool XML. What if our index is this instead:

CREATE INDEX OwnerUserId_LastActivityDate on Posts (OwnerUserId) 
INCLUDE (LastActivityDate);

Running our stored procedure one more time gets us to:

Down to 9 logical reads and no more key lookup.

What Did We Learn?

Just because SQL Server isn’t giving you missing index recommendations doesn’t mean missing indexes can’t make a dramatic difference.

Thanks for reading!