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])