Do Spills Get Logged?

Ever see a SELECT query with WRITELOG waits? I did, and exclaimed, “Oooh, interesting!” to the annoyance of all my cube neighbors. There was no data changing to require logging, so where was it coming from? After a little thought, I hypothesized that a spill to tempdb generates log activity.

But can I prove it?

The first ingredient for this experiment is a spill, and probably the most common operator to spill would be the sort, so let’s build a table to sort:

DROP TABLE IF EXISTS #Valdez
CREATE TABLE #Valdez (
ID INT IDENTITY PRIMARY KEY,
gooey BINARY(16)
)

INSERT #Valdez
SELECT TOP 10000 NEWID()
FROM master..spt_values a
CROSS JOIN master..spt_values b

Ok, now we need a query that will spill. A spill occurs when SQL Server doesn’t provide enough memory to an operator, and it has to use space in tempdb to complete the operation. So we need a way to get SQL Server to under-provision memory. The most common way this occurs in the wild is when the actual number of rows passed to a sort is far higher than what the optimizer estimated. But this is SCIENCE[!] so let’s screw with the memory grant instead.

SELECT ID
FROM #Valdez
ORDER BY gooey
OPTION(
MAX_GRANT_PERCENT = .000001
)

Now we need to read the log generated. Start with a CHECKPOINT command. Since tempdb is in simple recovery (and there are no other ongoing transactions because this is a test instance…right?), this will clear the logs. Then run the spilling query (I use a trash variable to prevent displaying results) and follow it up with the handy-dandy undocumented fn_dblog().

USE tempdb
GO

CHECKPOINT
GO

DECLARE @trash int

SELECT @trash = ID
FROM #Valdez
ORDER BY gooey
OPTION(
MAX_GRANT_PERCENT = .000001
)

SELECT Operation, Context, [Page ID], [Transaction Name], Description, [Log Record Length]
FROM sys.fn_dblog(NULL,NULL)

 

QED

I see a lot of activity on PFS, GAM, and IAM pages that makes me think this is minimally efficiently logged – only the allocations without the content of the spill. It’s even possible to find one of these sort pages by checking allocated pages mentioned in the description column. You can go digging for one yourself, if it’s the kind of thing to make you exclaim, “Oooh, interesting!”