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.
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.
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!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.