Eager Index Spools Are Judgmental Query Plan Operators In SQL Server

You’re Short


I ran across something similar in a previous post: Index Spools When You Have An Index.

But here we are again, with the optimizer treating us like fools for our index choices.

Let’s say we have this index on the Comments table:

CREATE INDEX lol
    ON dbo.Comments
        (UserId)
    INCLUDE
        (Id, PostId, Score, CreationDate, Text);

Is it a great idea? I dunno. But it’s there, and it should make things okay for this query:

SELECT u.Id, 
       u.DisplayName, 
       u.Reputation, 
       ca.Id, 
       ca.Type, 
       ca.CreationDate
FROM dbo.Users AS u
OUTER APPLY
(
SELECT c.Id,
       DENSE_RANK() 
           OVER ( PARTITION BY c.PostId 
                  ORDER BY c.Score DESC ) AS Type,
        c.CreationDate 
FROM dbo.Comments AS c
WHERE c.UserId = u.Id
) AS ca
WHERE ca.Type = 0;

You’re Round


But when we run the query and collect the plan, something rather astounding happens.

The optimizer uses our index to build a smaller index!

SQL Server Query Plan
hard at work

Digging in on the Eager Index Spool, it’s a nearly identical copy of the index we have, just without the Text column.

SQL Server Query Plan
it’s over

Your Mother Dresses You Funny


Of course, the optimizer being the unreasonable curmudgeon that it is, the only workaround is to also create the more narrow index.

CREATE INDEX lmao
    ON dbo.Comments
        (UserId)
    INCLUDE
        (Id, PostId, Score, CreationDate);

Or add the Text column to the select:

SELECT u.Id, 
       u.DisplayName, 
       u.Reputation, 
       ca.Id, 
       ca.Type, 
       ca.CreationDate,
       ca.Text
FROM dbo.Users AS u
OUTER APPLY
(
SELECT c.Id,
       DENSE_RANK() 
           OVER ( PARTITION BY c.PostId 
                  ORDER BY c.Score DESC ) AS Type,
        c.CreationDate,
        c.Text
FROM dbo.Comments AS c
WHERE c.UserId = u.Id
) AS ca
WHERE ca.Type = 0;

But that has a weird side effect, too. We’ll look at that tomorrow.

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.

SQL Server Index Spools When You Have An Index

PAUL WHITE IS WRONG


Ha ha. Just kidding. He’s never wrong about anything.

But he did write about Eager Index Spools recently, and the post ended with the following statement:

Eager index spools are often a sign that a useful permanent index is missing from the database schema.

I’d like to show you a case where you may see an Eager Index Spool even when you have the index being spooled.

Funboy & Funboy & Funboy


Let’s say we’ve got a query that, for better or worse, was written like so:

SELECT SUM(records)
FROM dbo.Posts AS p 
CROSS APPLY 
(
    SELECT COUNT(p2.Id)
    FROM   dbo.Posts AS p2
    WHERE  p2.LastEditDate >= '20121231'
	AND    p.Id = p2.Id

    UNION ALL

    SELECT COUNT(p2.Id)
    FROM   dbo.Posts AS p2
    WHERE  p2.LastEditDate IS NULL
	AND    p.Id = p2.Id
) x (records);

Right now, we’ve got this index:

CREATE INDEX to_null_or_not_to_null ON dbo.Posts(LastEditDate);

Which means we’ve effectively got an index on (LastEditDate, Id), because of how clustered index key columns are inherited by nonclustered indexes.

The APPLY section of the query plan looks like so:

SQL Server Query Plan
Spooled to death.

Each spool runs for nearly 53 seconds. The entire plan runs for 1:52.

There have been times when I’ve seen index spools created to effectively re-order existing indexes.

Perhaps that’s the case here? Let’s add this index.

CREATE /*UNIQUE*/ INDEX that_is_a_question ON dbo.Posts(Id, LastEditDate);

I’ve got UNIQUE in there in case you’re playing along at home. It makes no difference to the outcome.

I’d expect you to ask about that. I have high expectations of you, dear reader. I love you.

PLEASE DON’T LEAVE ME.

Get Out


The new execution plan looks uh.

SQL Server Query Plan
I’m In It

That’s frustrating, isn’t it? Why would you do that?

When I asked Paul why the optimizer was wrong (I understand that many of you confuse Paul with the optimizer. To wit, they’ve never been seen together.), he said something along the lines of:

The issue is that you have a unique clustered index that prevents the index matching logic from finding the better nonclustered index.

Well okay yeah lemme just go drop that clustered index or something.

Workarounds


There are several workarounds, like using FORCESEEK inside the APPLY logic.

Of course, the better method is just to write the query so there’s no need for the optimizer to join a table to itself a couple times.

SELECT SUM(x.records)
FROM (    
	SELECT COUNT(p.records)
    FROM 
    (
        SELECT 1 AS records
        FROM   dbo.Posts AS p2
        WHERE  p2.LastEditDate >= '20121231'
    ) AS p
    
    UNION ALL
    
    SELECT COUNT(p.records)
    FROM 
    (
        SELECT 1 AS records
        FROM   dbo.Posts AS p2
        WHERE  p2.LastEditDate IS NULL
    ) AS p
) AS x (records);

Which’ll finish in about 1.5 seconds.

But hey, nifty demo.

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.

Too Much Of A Harmless SQL Server Wait Type Can Be Harmful

Absence Of Evidence


There’s a pinky-out saying about wine: don’t hate the grape.

People say it because the same kind of grape can be grown by different people in different places.

Due to many, ahem, local factors, wine made with that grape can taste miraculously different.

It’s with that in mind that I’m going to say this: don’t ignore the wait.

No matter what script you’re using to look at wait stats, try unquoting the ignoreable list and seeing what shows up.

Get curious. Poke around. You might find something interesting.

Twosifer


While experimenting with FROID, I came up with a function and query that generate some weird waits.

Those waits are EXECSYNC, and CXCONSUMER. Now, under normal circumstances, you might be able to ignore them.

But things are rarely normal when you’re experiencing performance problems, are they? If you ignore too much, you can miss big problems.

Going back to running this query, I can see the wait stats that get generated in sys.dm_exec_session_wait_stats when the query is finished.

SELECT u.DisplayName, 
       dbo.TotalScore(u.Id) AS TotalScore
FROM dbo.Users AS u
WHERE u.Reputation >= 200000
ORDER BY u.Id;

Here’s what those waits look like:

SQL Server Wait Stats
Better Off Dead

If one were to follow advice — even advice from Microsoft — one may miss important clues as to what happened.

CXCONSUMER waits being high is fairly tightly correlated to skewed parallelism, and this is no exception.

EXECSYNC represents a serial zone within a parallel plan, in this case building two Eager Index Spools:

SQL Server Query Plan
SPOOL-TA-TOES

When you spend a long time building indexes single threaded, you spend a long time waiting on CXCONSUMER (and not so much time at all waiting on CXPACKET).

Being able to put the waits together with the query plan can help you tune queries more efficiently.

This is especially true if you’re on earlier versions of SQL Server/SSMS where the kind of detail shown in query plans here doesn’t exist.

Thanks for reading!

Eager Index Spool Suggestions For SQL Server

Funtime


I’ve added a couple ideas to User Voice.

The ideas are pretty simple:

Eager Index Spools Should Generate Missing Index Requests


In query plans where an Eager Index Spool is directly after a data access operator, a missing index should be generated in the query plan, and/or missing index DMVs that describes the definition of the index needed to make the spool unnecessary.

I would not expect this to happen when a Lazy Index Spool occurs above a subtree.

I’d appreciate it if you’d consider voting for it. It’s something that I was able to implement pretty easily in sp_BlitzCache.

Eager Index Spools Should Generate Wait Stats


In query plans where an Eager Index Spool is directly after a data access operator, wait stats should be generated while the Spool is built. In a parallel plan, EXECSYNC waits are generated, but in a serial plan, you don’t see anything. Problem scenarios will become more common when FROID is released and adopted.

I would not expect this to happen when a Lazy Index Spool occurs above a subtree.

Thanks for reading!

And voting as many times as possible ?

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.

The Coming Froidpocalypse In SQL Server 2019

Legal Notice


I’ve trademarked: Froidrage, Froidulent, and Froidpocalypse.

If you want to use them, you have to pay me $10,000.

Alright, I’m being told by my lawyer that writing them on cocktail napkins and showing them to confused bartenders doesn’t actually register a trademark.

Nevermind.

Here’s What’s Gonna Happen


And it’s not a problem that you need SQL Server 2019 to see. All you have to do is try to rewrite a function.

Here’s our Villain, a scalar UDF.

CREATE FUNCTION dbo.Villain (@UserId INT)
RETURNS INT
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS	
BEGIN

    DECLARE @Score INT
	SELECT TOP (1)
	         @Score = p.Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = @UserId
	AND   p.PostTypeId = 1
	ORDER BY p.Score DESC;
    
	RETURN @Score;

END
GO

Here’s the query that’s gonna call it:

SELECT      TOP ( 10 )
              u.DisplayName, 
			  u.Reputation, 
			  dbo.Villain(u.Id)
FROM        dbo.Users AS u
ORDER BY    u.Reputation DESC;
GO

I’m Going To Show You Two Things


The estimated plan, and the actual plan.

I need to show you the estimated plan so you can see what the function does, because that’s not included in the actual plan.

Yes, the estimated plan is more accurate than the actual plan.

Marinate on that.

SQL Server Query Plan
On The Dancefloor

The important thing is the second plan, which is the function’s execution plan. Notice that it generated a missing index request, and doesn’t spool anything at all.

It handles the query logic with a Top N Sort.

Here’s the actual plan:

SQL Server Query Plan
A NONCLUSTERED INDEX SCAN!!!

Let’s talk about a couple things:

  • A nonclustered index scan that costs 100% and runs for 0.000s
  • A compute scalar that costs 0% and runs for ~3s

The compute scalar thing is well documented by… Well, not by official Microsoft documentation.

But they’ve been blogged about by Cookies Cunningham, and Paul White.

Thanks, you two.

Any Reasonable Person


Would say “I can rewrite that function and make things better”.

Because of course an inline function is always better than a scalar function.

Enter our Hero.

CREATE FUNCTION dbo.Hero (@UserId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS	
RETURN

	SELECT TOP (1)
	         p.Score
	FROM dbo.Posts AS p
	WHERE p.OwnerUserId = @UserId
	AND   p.PostTypeId = 1
	ORDER BY p.Score DESC;

GO

Here’s the query that’s gonna call it:

SELECT      TOP ( 10 )
              u.DisplayName, 
			  u.Reputation, 
			  h.*
FROM        dbo.Users AS u
CROSS APPLY dbo.Hero(u.Id) AS h
ORDER BY    u.Reputation DESC;

I Only Need To Show You One Thing


Since the function is an inline type, the query processor is honest with us about the full query plan.

SQL Server Query Plan
Spiced Ham

Two things happened here:

  • The “function body” no longer goes parallel
  • The TOP (1) is run against an eager index spool rather than the clustered index

What’s The Point?


This is what FROID does for you without a rewrite. It’ll inline the scalar UDF.

The plan may be better, or it may be worse.

The scalar UDF plan ran for 3 seconds, and the inline version ran for almost 13 seconds.

Stay tuned for tomorrow’s post. I have a couple suggestions for how The SQL Server team can help end users stay on top of these problems in SQL Server 2019.

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.

Hey SQL Server Query, What Were You Waiting On?

Surreal Plans


In a parallel plan with an eager index spool, we can observe EXECSYNC waits for the duration of the spool being built.

In a serial plan, we’re not so lucky. There’s no obvious wait that indicates we built an index.

Let’s run a familiar query:

SELECT      TOP ( 10 )
            u.DisplayName, 
			u.Reputation, 
			ca.*
FROM        dbo.Users AS u
CROSS APPLY 
(   
    SELECT   TOP ( 1 )
			   p.Score
    FROM     dbo.Posts AS p
    WHERE    p.OwnerUserId = u.Id
    AND      p.PostTypeId = 1
    ORDER BY p.Score DESC 
) AS ca
ORDER BY    u.Reputation DESC;

The plan is fully serial:

SQL Server Query Plan
Meat Lovers

It’s obvious looking at the actual plan in SSMS 18 what took a long time.

If we’re not so lucky, and we have a cached plan, it would be less obvious:

SQL Server Query Plan
Back Like That

Look how bad scans are! Ha ha ha!

?

What’s A Wait?


If you wanna figure out the runtime, you have to do some math.

SQL Server Wait Stats
What times what?

If you take the SOS_SCHEDULER_YIELD waits and multiply them by 4ms, you can get about accurate runtime (12,444).

We’re lucky on my laptop that our query isn’t competing with other queries for processor time, so there’s not a long queue to get back on a CPU on each yield.

It’s nice to be able to see this while we’re watching a query, but if we come across it in the plan cache, or if we were looking at wait stats, what would we make of the problem?

Surely an operator that’s only 13% of the plan cost couldn’t be responsible for all that.

??

But There We Have It


A query that runs for a non-trivial amount of time, emits common wait stats, and doesn’t ask for an index when it’s creating one.

And that index creation is what makes up for 99% of the execution time.

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.

How Select List Column Sizes Change How Big Spool Operators Are In SQL Server Query Plans

I’ll Use Those Columns Later, Maybe


This is a short post, since we’re on the subject of index spools this week, to show you that the columns that go into the spool will impact spool size and build time.

I know, that sounds obvious, but once in a while I care about “completeness”.

We’re going to look at two queries that build eager index spools, along with the time the spool takes to build and how many writes we do.

Query 1


On the side of the query where a spool gets built (inside the apply), we’re only selecting one column.

SELECT      TOP ( 10 )
            u.DisplayName, 
			u.Reputation, 
			ca.*
FROM        dbo.Users AS u
CROSS APPLY 
(   
    SELECT   TOP ( 1 )
			   p.Score
    FROM     dbo.Posts AS p
    WHERE    p.OwnerUserId = u.Id
    AND      p.PostTypeId = 1
    ORDER BY p.Score DESC 
) AS ca
ORDER BY    u.Reputation DESC;

In the query plan, we spend 1.4 seconds reading from the Posts table, and 13.5 seconds building the index spool.

SQL Server Query Plan
Work it

We also do 21,085 writes while building it.

SQL Server Extended Events
Insert comma

Query 2


Now we’re going to select every column in the Posts table, except Body.

If I select Body, SQL Server outsmarts me and doesn’t use a spool. Apparently even spools have morals.

SELECT      TOP ( 10 )
              u.DisplayName, 
			  u.Reputation, 
			ca.*
FROM        dbo.Users AS u
CROSS APPLY 
(   
    SELECT   TOP ( 1 )
               p.Id, p.AcceptedAnswerId, p.AnswerCount, p.ClosedDate, 
               p.CommentCount, p.CommunityOwnedDate, p.CreationDate,
               p.FavoriteCount, p.LastActivityDate, p.LastEditDate,
               p.LastEditorDisplayName, p.LastEditorUserId, p.OwnerUserId, 
               p.ParentId, p.PostTypeId, p.Score, p.Tags, p.Title, p.ViewCount
    FROM     dbo.Posts AS p
    WHERE    p.OwnerUserId = u.Id
    AND      p.PostTypeId = 1
    ORDER BY p.Score DESC 
) AS ca
ORDER BY    u.Reputation DESC;
GO

In the query plan, we spend 2.8 seconds reading from the Posts table, and 15.3 seconds building the index spool.

SQL Server Query Plan
Longer

We also do more writes, at 107,686.

SQL Server Extended Events
And more!

This Is Not A Complaint


I just wanted to write this down, because I haven’t seen it written down anywhere else.

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.

Eager Index Spool Annoyances In SQL Server

Don’t Mask Spools


Certain spools in SQL Server can be counterproductive, though well intentioned.

In this case, I don’t mean that “if the spool weren’t there, the query would be faster”.

I mean that… Well, let’s just go look.

Bad Enough Plan Found


Let’s take this query.

SELECT TOP (50) 
    u.DisplayName, 
    u.Reputation, 
    ca.*
FROM dbo.Users AS u
CROSS APPLY 
(
    SELECT TOP (10) 
        p.Id, 
        p.Score, 
        p.Title
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = u.Id
    AND   p.PostTypeId = 1
    ORDER BY 
        p.Score DESC
) AS ca
ORDER BY 
    u.Reputation DESC;

Top N per group is a common enough need.

If it’s not, don’t tell Itzik. He’ll be heartbroken.

The query plan looks like this:

SQL Server Query Plan
Wig Billy

Thanks to the new operator times in SSMS 18, we can see exactly where the chokepoint in this query is.

Building and reading from the eager index spool takes 70 wall clock seconds. Remember that in row mode plans, operator times aggregate across branches, so the 10 seconds on the clustered index scan is included in the index spool time.

One thing I want to point out is that even though the plan says it’s parallel, the spool is built single threaded.

SQL Server Query Plan Properties
One Sided

Reading data from the clustered index on the Posts table and putting it into the index is all run on Thread 2.

If we look at the wait stats generated by this query, a full 242 seconds are spent on EXECSYNC.

SQL Server Wait Stats In Query Plans
Armless

The math mostly works out, because four threads are waiting on the spool to be built.

Even though the scan of the clustered index is serial, reading from the spool occurs in parallel.

SQL Server Query Plan Properties
Spange

Connected


Eager index spools are built per-query, and discarded afterwards. When built for large tables, they can represent quite a bit of work.

In this example query, a 17 million row index is built, and that’ll happen every single time the query executes.

While I’m all on board with the intent behind the index spool, the execution is pretty brutal. Much of query tuning is situational, but I’ll always pay attention to an index spool (especially because you won’t get a missing index request for them anywhere). You’ll wanna look at the spool definition, and potentially create a permanent index to address the issue.

As for EXECSYNC waits, they can be generated by other things, too. If you’re seeing a lot of them, I’m willing to bet you’ll also find parallel queries with spools in them.

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.