Category: Spools

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:

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.

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.

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.

We also do 21,085 writes while building it.

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.

We also do more writes, at 107,686.

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.