14

On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of tempdb.

When you take a look at the query plan (simplied query) you'll see that the number of estimated rows is equal to the number of actual rows but still spills occur. So out-of-date statistics can't be the cause of the problem.

I did some testing and following query spills to Tempdb:

select id --uniqueidentifier
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)

But if I select a different column no spills occur:

select startdate --datetime
from SortProblem
where [status] ='A'
order by SequenceNumber asc 
option (maxdop 1)

So I tried to 'enlarge' the size of the id column:

select CONVERT(nvarchar(512),id)
from SortProblem
where [status] ='A'
order by SequenceNumber asc 
option (maxdop 1)

Then also no spilling occurs.

Why is the uniqueidentifier spilling to tempdb and a datatime column not? When I delete about 20000 records then also no spilling happens when I select the id column.

With following script you can reproduce the problem:

CREATE TABLE SortProblem
  (
     id             UNIQUEIDENTIFIER,
     startdate      DATETIME,
     sequencenumber BIGINT,
     status         VARCHAR(50),
     PRIMARY KEY CLUSTERED(id)
  )

SET nocount ON;

WITH nums(num)
     AS (SELECT TOP 103000 ROW_NUMBER()
                             OVER (
                               ORDER BY 1/0)
         FROM   sys.all_objects o1,
                sys.all_objects o2)
INSERT INTO SortProblem
SELECT newid(),
       DATEADD(millisecond, num, GETDATE()),
       num,
       CASE
         WHEN num <= 100000 THEN 'A'
         WHEN num <= 101000 THEN 'B'
         WHEN num <= 102000 THEN 'C'
         WHEN num <= 103000 THEN 'D'
       END
FROM   nums

CREATE NONCLUSTERED INDEX [IX_Status]
  ON [dbo].[SortProblem]([status] ASC)
  INCLUDE ([sequencenumber]) 
14

Enable trace flag 7470.

FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct

As I wrote in answer to Query Plan question:

This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to avoid unexpected plan changes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.