Operator użył tempdb do rozlewania danych podczas wykonywania z poziomem rozlewania 2

Usiłuję zminimalizować koszty sortowania w planie zapytań z ostrzeżeniem Operator usedtempdbto spill data during execution with spill level 2

Znalazłem kilka postów związanych z danymi wycieków podczas wykonywania z poziomem wycieku 1 , ale nie w poziomie 2. Poziom 1 wydaje się być spowodowany nieaktualnymi statystykami , a co z poziomem 2? Nie mogłem znaleźć niczego związanego z level 2.

Uważam ten artykuł za bardzo interesujący związany z ostrzeżeniami dotyczącymi sortowania:

Nigdy nie ignoruj ​​ostrzeżenia dotyczącego sortowania w programie SQL Server

Mój serwer SQL?

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 17 czerwca 2016 19:14:09 Prawa autorskie (c) Microsoft Corporation Enterprise Edition (64-bit) w systemie Windows NT 6.3 (kompilacja 9600:) (Hypervisor)

Mój sprzęt?

uruchomienie poniższego zapytania w celu znalezienia harware:

- Informacje o sprzęcie z SQL Server 2012

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_kb/1024 AS [Physical Memory (MB)], affinity_type_desc, 
virtual_machine_type_desc, sqlserver_start_time
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

aktualnie przydzielona pamięć

SELECT
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;

kiedy uruchamiam zapytanie z rocznym zakresem, nie otrzymuję żadnego ostrzeżenia, jak na zdjęciu poniżej:

Ale gdy uruchomię go tylko na 1 dzień, otrzymuję to ostrzeżenie on the sort operator:

to jest zapytanie:

    DECLARE @FromDate SMALLDATETIME = '19-OCT-2016 11:00'
    DECLARE @ToDate   SMALLDATETIME = '20-OCT-2016 12:00'




    SELECT      DISTINCT
                a.strAccountCode ,
                a.strAddressLine6 ,
                a.strPostalCode ,
                CASE    WHEN a.strCountryCode IN ('91','92') THEN 'GB-Int'
                        ELSE a.strCountryCode
                        END AS [strCountryCode]
    FROM        Bocss2.dbo.tblBAccountParticipant AS ap
    INNER JOIN  Bocss2.dbo.tblBAccountParticipantAddress AS apa ON ap.lngParticipantID = apa.lngParticipantID
                                                                AND apa.sintAddressTypeID = 2
    INNER JOIN  Bocss2.dbo.tblBAccountHolder AS ah ON ap.lngParticipantID = ah.lngParticipantID
    INNER JOIN  Bocss2.dbo.tblBAddress AS a ON apa.lngAddressID = a.lngAddressID
                                            AND a.blnIsCurrent = 1
    INNER JOIN  Bocss2.dbo.tblBOrder AS o ON ap.lngParticipantID = o.lngAccountParticipantID
                                        AND o.sdtmOrdCreated >= @FromDate
                                        AND o.sdtmOrdCreated < @ToDate

OPTION(RECOMPILE)

plan zapytań jest tutaj

plan zapytań za pomocą pastetheplan

Pytania: 1) w planie zapytań widzę to:

StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" 

dlaczego 70 Korzystam z serwera SQL 2014

2) Jak pozbyć się operatora sortowania (jeśli w ogóle jest to możliwe)?

3) Widziałem, że oczekiwanie na życie strony jest dość niskie, oprócz dodania większej ilości pamięci do tego serwera, czy jest coś jeszcze, na co mogę spojrzeć, czy mogę zapobiec temu ostrzeżeniu?

Twoje zdrowie

Aktualizacja po odpowiedzi Shanky i Paula White'a

Sprawdziłem statystyki zgodnie z poniższym skryptem i wszystkie wydają się poprawne i zaktualizowane.

są to wszystkie indeksy i tabele używane w tym zapytaniu.

DBCC SHOW_STATISTICS ('dbo.tblBAddress','IDXF_tblBAddress_lngAddressID__INC')
GO
DBCC SHOW_STATISTICS  ('dbo.tblBOrder','IX_tblBOrder_sdtmOrdCreated_INCL')
GO
DBCC SHOW_STATISTICS ('dbo.tblBAccountHolder','PK_tblAccountHolder')
GO
DBCC SHOW_STATISTICS ('dbo.tblBAccountParticipant','PK_tblBAccountParticipants')
GO
DBCC SHOW_STATISTICS ('dbo.tblBAccountParticipantAddress','IDXF_tblBAccountParticipantAddress_lngParticipantID')
GO

oto co otrzymałem:

To częściowe wyniki, ale ponownie je wszystkie odwiedziłem.

Do aktualizacji statystyk mam obecnie Ola Hallengren

zadanie optymalizacji indeksu - zaplanowane do uruchomienia raz w tygodniu - w niedziele

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'USER_DATABASES,-%Archive', 
@Indexes = 'ALL_INDEXES' , 
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@PageCountLevel=1000,
@StatisticsSample =100
,@UpdateStatistics = 'Index', 
@OnlyModifiedStatistics = 'Y',
@TimeLimit=10800, 
@LogToTable = 'Y'

Chociaż statystyki wydawały się być aktualizowane Po uruchomieniu następującego skryptu, nie otrzymałem już żadnego ostrzeżenia dla operatora sortowania.

UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder]  WITH FULLSCAN
--1 hour  04 min 14 sec

UPDATE STATISTICS [Bocss2].[dbo].tblBAddress  WITH FULLSCAN
-- 45 min 29 sec

UPDATE STATISTICS  [Bocss2].[dbo].tblBAccountHolder WITH FULLSCAN
-- 26 SEC

UPDATE STATISTICS  [Bocss2].[dbo].tblBAccountParticipant WITH FULLSCAN
-- 4 min

UPDATE STATISTICS  [Bocss2].[dbo].tblBAccountParticipantAddress WITH FULLSCAN
-- 7 min 3 sec

co z poziomem 2? Nie mogłem znaleźć niczego związanego z poziomem 2.

Zgodnie z tym dokumentem Old MS Doc liczba w wyciekach Tempdb oznacza liczbę przejść wymaganych przez dane w celu posortowania danych. Rozlanie 1 oznacza więc, że musi minąć 1 raz, aby posortować dane, a 2 oznacza, że ​​musi przejść 2 razy.

Cytowanie z bloga:

Jeśli zapytanie obejmujące operację sortowania generuje klasę zdarzenia Ostrzeżenia sortowania z wartością poziomu rozlewania 2, wydajność kwerendy może ulec zmianie, ponieważ do posortowania danych wymagane jest wielokrotne przejście danych. W poniższym przykładzie widzimy wartość poziomu wycieku równą 1, co oznacza, że ​​jedno przejście danych wystarczyło, aby zakończyć sortowanie.

dlaczego 70 Korzystam z serwera SQL 2014

Wynika to z faktu, że poziom zgodności bazy danych na zdjęciu NIE wynosi 120 (co oznacza poziom zgodności bazy danych z 2014 r.), Ponieważ nie jest to 120, zapytanie zostanie przetworzone przy użyciu starego modelu szacowania liczności (CE), który jest nazywany CardinalityEstimationModelVersion="70". Jestem pewien, że wiesz, że od SQL Server 2014 mamy nowy CE.

jak pozbyć się operatora sortowania (jeśli to w ogóle możliwe)?

Wyraźne polecenie, którego używasz, powoduje operację sortowania. Sortowane dane nie mieszczą się w pamięci, więc są przenoszone do tempdb, a gdy tak się dzieje, w planie wykonania jest wyświetlane ostrzeżenie o sortowaniu z żółtym wykrzyknikiem. Ostrzeżenia dotyczące sortowania nie zawsze stanowią problem.

W planie wykonania widać, że szacowana liczba wierszy do posortowania wynosi 1, ale w czasie wykonywania napotkano 16 353 wierszy. Ilość pamięci zarezerwowanej dla sortowania zależy od oczekiwanego (szacowanego) rozmiaru danych wejściowych i nie może wzrosnąć podczas wykonywania (w tym przypadku).

Przydział małej pamięci dla zapytania (1632 KB) jest także współdzielony przez współbieżnie wykonujących się operatorów zajmujących pamięć (sortowanie i „zoptymalizowane” sprzężenia pętli). W twoim planie oznacza to, że 33,33% (544 KB) jest dostępne do sortowania podczas odczytu wierszy (ułamek pamięci wejściowej). To za mało pamięci, aby posortować 16 353 wierszy, więc rozlewa się do tempdb . Pojedynczy poziom wycieku nie wystarcza do ukończenia sortowania, dlatego potrzebny jest drugi poziom wycieku (więcej informacji na temat poziomów wycieku znajduje się w odnośniku na końcu).

_{Sortuj właściwości według widoku w Eksploratorze planów SQL Sentry}

Aktualizacja statystyk prawdopodobnie pomoże w problemie z oszacowaniem liczności. Być może masz problem z rosnącym kluczem, szczególnie na stole tblBOrder. Prosty wybór z tej tabeli z literalnymi datami z twojego pytania prawdopodobnie oszacuje teraz jeden wiersz.

Widziałem, że oczekiwanie na życie strony jest dość niskie, oprócz dodawania większej ilości pamięci do tego serwera, czy jest coś jeszcze, na co mogę spojrzeć, czy mogę zapobiec temu ostrzeżeniu?

PLE jest wskaźnikiem ilości aktywności I / O, czy wzrosła? Dzieje się tak często lub tylko wtedy, gdy uruchomisz określone zapytanie lub miało to miejsce dopiero dzisiaj. Unikaj szarpnięcia kolana, najpierw musimy upewnić się, że naprawdę odczuwasz presję pamięci lub jakieś nieuczciwe zapytanie, które generuje zbyt wiele operacji we / wy. W każdym razie masz już 97 G pamięci przypisanej do SQL Server.

Aby uzyskać więcej informacji na temat poziomów wycieków i rosnącego problemu kluczowego, zobacz:

• Rodzaj, który rozlewa się na poziom 15 000 według Paula White'a
• Statystyki dotyczące Rosnących kolumn według Fabiano Amorima