Sortuj wycieki do tempdb, ale szacowane wiersze są równe rzeczywistym wierszom

Na SQL Server 2016 SP2 z maksymalną pamięcią ustawioną na 25 GB mamy zapytanie, które wykonuje się około 80 razy w ciągu minuty. Kwerenda obejmuje około 4000 stron do tempdb. Powoduje to wiele IO na dysku tempdb.

Gdy spojrzysz na plan zapytań (zapytanie uproszczone), zobaczysz, że liczba szacowanych wierszy jest równa liczbie faktycznych wierszy, ale nadal występują wycieki. Nieaktualne statystyki nie mogą być przyczyną problemu.

Zrobiłem kilka testów i następujących wycieków zapytania do Tempdb:

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

Ale jeśli wybiorę inną kolumnę, nie nastąpi wyciek:

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

Próbowałem więc „powiększyć” rozmiar kolumny id:

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

Wtedy również nie następuje rozlanie.

Dlaczego unikalny identyfikator przelewa się do tempdb, a kolumna z datatime nie? Kiedy usuwam około 20000 rekordów, to po wybraniu kolumny id nie dochodzi do rozlania.

Za pomocą następującego skryptu możesz odtworzyć 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])

— Frederik Vanderhaegen
źródło

Włącz flagę śledzenia 7470.

POPRAWKA: Operator sortowania przelewa się do tempdb w SQL Server 2012 lub SQL Server 2014, gdy szacowana liczba wierszy i ich rozmiar są poprawne

Jak napisałem w odpowiedzi na pytanie dotyczące planu zapytań :

Ta flaga śledzenia koryguje niedopatrzenie w obliczeniach. Jest dość bezpieczny w użyciu i moim zdaniem powinien być domyślnie włączony. Zmiana jest chroniona przez flagę śledzenia, aby uniknąć nieoczekiwanych zmian w planie.

— Paul White 9
źródło