AdaptiveQueryProcessingMemoryGrantFeedback_000

Adaptive Query Processing – Memory Grant Feedback

Kontynuujemy naszą małą serię związaną z Adaptive Query Processing czyli elastycznym dostosowaniu zapytania w trakcie jego wykonania. Do tej pory udało mi się opisać dwa mechanizmy wchodzące w skład AQP i były to Interleaved Execution oraz Adaptive Join, dziś opowiemy sobie o kolejnym, który został wprowadzony w SQL Server 2017 i ulepszony w wersji SQL Server 2019 o dosyć ciekawie brzmiącej nazwie tj. Memory Grant Feedback, serdecznie zapraszam do lektury.

Na wstępie powiedzmy sobie kilka słów o tym czym jest  Memory Grant i od czego zależy. Jak sama nazwa wskazuje jest to nic innego jak przydział pamięci nadawany przez SQL Server na potrzeby specjalnych operatorów na planie zapytania. Oczywiście nie każdy operator wymaga takowego przydziału jednakże sposób działania niektórych z nich potrzebuje tej właśnie pamięci. Ogólnie rzecz biorąc  następujące operatory opierają swoje działanie o przydział pamięci:

  • Sort (w tym Sort sam w sobie oraz Sort TOP N) występujący m.in. w następujących składniach TSQL:
    • ORDER BY,
    • GROUP BY,
    • DISTINCT.
  • Hash (w tym Hash Match oraz Hash Match Aggregate) występujący m.in. w następujących składniach TSQL:
    • JOIN,
    • DISTINCT,
    • Funkcje agregujące np. SUM,MAX,MIN,AVG,
    • UNION.
  • Exchange (w tym Distribute Streams, Repartition Streams oraz Gather Streams) występujący na planach wielowątkowych.

Skąd SQL Server wie ile pamięci powinno zostać przydzielone konkretnemu operatorowi? Oczywiście ze statystyk czyli estymowanej liczby wierszy jaką operator będzie przetwarzał oraz szerokości wiersza. Dla przykładu uruchomimy poniższe zapytanie na bazie AdventureWorksDW2017:

select
	 DC.LastName + ' '+DC.FirstName AS Customer
	,AVG([UnitPrice]) AS [UnitPrice]
	,SUM([ExtendedAmount]) AS [ExtendedAmount]
from 
[dbo].[FactInternetSales] AS FIS
JOIN [dbo].DimCustomer AS DC
ON DC.CustomerKey=FIS.CustomerKey
GROUP BY DC.LastName + ' '+DC.FirstName

Na planie zapytania po zatrzymaniu myszy na operatorze SELECT możemy zobaczyć właściwość Memory Grant informującą nas ile pamięci w KB zostało przydzielone operatorom na tym planie zapytania:

Trochę więcej informacji znajdziemy we właściwościach (po wciśnięciu F4) w sekcji Memory Grant Info:

Sam przydział został tutaj rozbity na kilka osobnych właściwości, które przedstawiają się w następujący sposób (wartości w KB):

  • SerialRequiredMemory – minimalna ilość pamięci bez której zapytanie nie wystartuje obliczona dla wykonania na jednym wątku,
  • SerialDesiredMemory – idealna ilość pamięci żeby nie potrzeba było zrzucać niczego na dysk obliczona dla wykonania na jednym wątku,
  • RequiredMemory – to co SerialRequiredMemory z uwzględnieniem MAXDOP,
  • DesiredMemory – to co SerialDesiredMemory z uwzględnieniem MAXDOP,
  • GrantedMemory – faktycznie przydzielona pamięć do zapytania,
  • GrantWaitTime – ile czasu trzeba było czekać na przydział pamięci,
  • MaxQueryMemory – maksymalna ilość pamięci dostępnej do przydzielenia,
  • MaxUsedMemory – rzeczywista ilość użytej pamięci przez zapytanie,
  • RequestedMemory – ilość pamięci żądanej do wykonania zapytania.

Pamięć jest niejako chroniona przez wewnętrzny mechanizm nazwany RESOURCE SEMAPHORE i właśnie do tego mechanizmu trafiają wszelkie “żądania” związane z przydziałem pamięci, jeśli więc zobaczymy statystyki oczekiwań z tą nazwą trzeba mieć na uwadze, że mamy problemy z pamięcią:)

Co ciekawe możemy niejako sterować jaki procent pamięci może zostać przypisany do pojedynczego zapytania. Służy do tego np. wprowadzony w SQL server 2012 SP3 hint min_grant_percent oraz max_grant_percent przyjmujący wartości typu float od 0 do 100.

Testując powyższą opcję możemy zauważyć, że w momencie wywołania zapytania z tym hintem przydzielony grant pamięci to mniej więcej 50% maksymalnej wartości jaka może być przydzielona:

select
	 DC.LastName + ' '+DC.FirstName AS Customer
	,AVG([UnitPrice]) AS [UnitPrice]
	,SUM([ExtendedAmount]) AS [ExtendedAmount]
from 
[dbo].[FactInternetSales] AS FIS
JOIN [dbo].DimCustomer AS DC
ON DC.CustomerKey=FIS.CustomerKey
GROUP BY DC.LastName + ' '+DC.FirstName
OPTION (min_grant_percent=50)

Wartości te można również skonfigurować globalnie używając sp_configure:

sp_configure 'min_memory_per_query'

Można również zmienić to ustawienie w Resource Governor przypisując odpowiednią wartość do odpowiedniej grupy zasobów:

Następnie w ramach Workload groups możemy przypisać ile procent z przypisanej do całej puli zasobów może użyć pojedyncze zapytanie. Oczywiście query hint nadpisuje ustawienie w Resource Governor, a ten nadpisuje ustawienie w sp_configure. Ktoś może zapytać po co nam w ogóle opcja tego typu odpowiadająca za minimalny przydział pamięci do zapytania? Może ona być pomocna w przypadku gdy estymowane liczby wierszy są na tyle niedoszacowane i na tyle częste, że powodują zrzuty do tempdb, a my mając odpowiednie zasoby pamięci możemy właśnie w ten sposób sobie z tym poradzić.

Aby mieć możliwość podejrzenia przydzielonych grantów do odpowiednich zapytań możemy odpytać widok sys.dm_exec_query_memory_grants który bywa niezwykle pomocny w analizie i poszukiwaniu zapytań, które najbardziej obciążają pamięć operacyjną. Możemy również użyć Extended Events, a raczej wydarzenia query_memory_grant_usage. Oczywiście istnieje jeszcze kilka użytecznych sposobów na monitorowanie grantów pamięci jednakże na ten moment pozostaniemy na lapidarnym wymienieniu wyżej wymienionych i przejdziemy dalej.

W SQL Server niemal wszystko opiera się na statystykach, a jak wszyscy wiemy estymacja na ich podstawie bywa zawodna szczególnie gdy nasz plan jest na tyle skomplikowany, że im dalej tym trudniej jest oszacować ilość wierszy.
W praktyce mamy do czynienia z dwoma rodzajami błędnych szacunków tj. z insufficient grant czyli wtedy gdy zbyt mała ilość pamięci została przypisana do operatora przez co do wykonania potrzebne są zrzuty na dysk (disk spill) oraz excessive grant czyli zbyt duża ilość pamięci jest przypisana do operatora niż ten używa przez co zasoby są niepotrzebnie marnotrawione. Opisywany w ramach tego artykułu mechanizm jest pomocny w obu scenariuszach. Co zatem się stanie gdy na podstawie statystyk SQL Server nie doszacował ilości potrzebnej pamięci? Sprawdźmy to!

Aby zasymulować odpowiednie warunki stwórzmy procedurę na podstawie naszego zapytania testowego z parametrem na atrybucie PromotionKey, który ma niejednorodny rozkład wartości co możecie zaobserwować poniżej:

select PromotionKey,count(*) AS NumberOfRows
from [dbo].[FactInternetSales] 
group by PromotionKey

Ciało naszej procedury przedstawia się w następujący sposób:

CREATE PROC dbo.MemoryGrantFeedbackDemo @PromotionKey INt
AS
select
	 DC.LastName + ' '+DC.FirstName AS Customer
	,AVG([UnitPrice]) AS [UnitPrice]
	,SUM([ExtendedAmount]) AS [ExtendedAmount]
from 
[dbo].[FactInternetSales] AS FIS
JOIN [dbo].DimCustomer AS DC
ON DC.CustomerKey=FIS.CustomerKey
WHERE FIS.[PromotionKey] = @PromotionKey
GROUP BY DC.LastName + ' '+DC.FirstName
ORDER BY [ExtendedAmount] DESC

Najpierw zaprezentujmy zachowanie bez Memory Grant Feedback czyli tak jak to działało przed SQL Server 2017, zrobimy to zmieniając Compatibility Level na 130 (odpowiadający SQL Server 2016):

ALTER DATABASE AdventureWorksDW2017 
SET COMPATIBILITY_LEVEL = 130;

Następnie wyczyśćmy cache używając Database Scoped Configuration:

ALTER DATABASE SCOPED CONFIGURATION 
CLEAR PROCEDURE_CACHE;

Po wstępnym przygotowaniu możemy przejść do działania. Wykonajmy zatem procedurę z najmniej liczną wartości parametru czyli 14:

EXEC dbo.MemoryGrantFeedbackDemo 14

Statystyki grantu pamięci przedstawiają się w następujący sposób:

Nie wgłębiając się zbytnio w powyżej otrzymane liczby przejdźmy dalej i spróbujmy uruchomić procedurę dla najbardziej licznego parametru:

EXEC dbo.MemoryGrantFeedbackDemo 1

Po uruchomieniu na planie wykonania możemy spostrzec, że pojawiły się ostrzeżenia związane ze zrzutem do tempdb na obu operatorach Sort. We właściwościach możemy również zauważyć, że rzeczywiście część danych została zapisana i później odczytana z systemowej bazy tymczasowej:

Kluczowe do zrozumienia tego co się stało jest sprawdzenie właściwości Retreived From Cache, które w tym wypadku przyjęło wartość True:

Powyższe znaki doprowadzają nas do wniosku, że mamy do czynienia z klasycznym przypadkiem złego Parameter Sniffingu (który sam w sobie jest dobrym mechanizmem działającym niekorzystnie tylko w niektórych przypadkach).
W skrócie chodzi o to, że w cache zapisany został plan wykonania zoptymalizowany dla wartości parametru, który wybrał znacząco mniej wierszy niż ma to miejsce dla wartości parametru wybranej za drugim razem. Rozwiązań na ten problem jest kilka, a jednym z nich jest opisywany mechanizm Memory Grant Feedback. Aby pokazać go w akcji powróćmy do odpowiedniego Compatibility Level oraz wyczyśćmy cache:

ALTER DATABASE AdventureWorksDW2017 
SET COMPATIBILITY_LEVEL = 140;

ALTER DATABASE SCOPED CONFIGURATION 
CLEAR PROCEDURE_CACHE;

Na ten moment mechanizm działa jedynie w trybie Batch, dlatego też musimy założyć indeks kolumnowy na tabeli używanej w zapytaniu:

CREATE NONCLUSTERED COLUMNSTORE INDEX 
[NCCI_FactInternetSales] ON [dbo].[FactInternetSales]
(
	[ProductKey]
)
WHERE (ProductKey=1 AND ProductKey=2)
GO

Zmodyfikowałem nieco moją procedurę testową tak aby używała FactInternetSales_BIG czyli powiększoną wersję używanej wcześniej tabeli. Zrobiłem to dlatego aby można było zasymulować oba scenariusze niedopasowanego grantu, ogólnie rzecz biorąc obie tabele strukturalnie i rozkładem wartości się nie różnią. Żebyśmy mieli pewność, że zadziałał opisywany mechanizm stworzymy sobie sesję Extended Events używając poniższego skryptu (o samym zdarzeniu nieco później):

CREATE EVENT SESSION [MemoryGrantFeedback_XE] ON SERVER 
ADD EVENT sqlserver.memory_grant_updated_by_feedback
GO

Tak więc spróbujemy uruchomić procedurę dla parametru o mniej licznej wartości:

EXEC dbo.MemoryGrantFeedbackDemo 14

Gdy sprawdzimy plan wykonania dla wartości parametru 14 to zobaczymy, że przydzielony został grant pamięci 95496:

Po ponownym uruchomieniu tej samej procedury możemy zauważyć, że przydział jest zdecydowanie mniejszy:

Dzieje się tak dlatego, że SQL Server niejako uczy się na podstawie poprzednich wykonań. Sesja XEvents pokazuje nam, że optymalizacja została znaleziona zaraz po pierwszym wykonaniu:

W dalszej kolejności uruchomienie procedury dla wartości parametru równej 1 skutkuje kolejnym feedbackiem i dostosowanym grantem przy kolejnym uruchomieniu:

SQL Server niejako “zapamiętał”, że ostatnio z tym zapytaniem były problemy i tym razem przydzielił do zapytania odpowiednią ilość pamięci. W momencie zrzutu na dysk “zaraportowanego” do mechanizmu wywoływane jest wydarzenie XEvents o nazwie spilling_report_to_memory_grant_feedback następnie następuje przekalkulowanie związane z niedopasowanym grantem, które możemy wyśledzić poprzez zdarzenie memory_grant_updated_by_feedback, które właśnie wtedy jest wywoływane. W momencie gdy nasza procedura jest “parameter sensitive” czyli bardzo często zmieniają się dla niej wymogi co do pamięci to Memory Grant Feedback zostanie dla niej wyłączony co również możemy śledzić przy pomocy memory_grant_feedback_loop_disabled.

Aby to sprawdzić stwórzmy sobie pętle, która będzie wykonywać naszą testową procedurę raz podając jeden parametr, a raz drugi przez co memory grant feedback będzie miał mnóstwo roboty. Pętla jest nieskończona i wygląda w następujący sposób:

DECLARE @Counter INT=1
WHILE 1=1
BEGIN
	
EXEC dbo.MemoryGrantFeedbackDemo 14
EXEC dbo.MemoryGrantFeedbackDemo 1
	PRINT 'Loop number: '+CAST(@Counter AS NVARCHAR(10))
	SET @Counter=@Counter+1
END

Nie ma sensu w takim przypadku wyświetlać jej rezultatu dlatego też tymczasowo go wyłączymy:

Sesja XEvents pozwalająca nam wyłapać odpowiednie zdarzenie wygląda tak:

CREATE EVENT SESSION [MemoryGrantFeedback_XE] ON SERVER 
ADD EVENT sqlserver.memory_grant_feedback_loop_disabled
GO

Na 32 wykonania memory grant feedback 31 razy zmieniał przydział pamięci dlatego też świadomy, że mamy do czynienia z kodem “parameter sensitive” został on wyłączony. Powinniśmy uważać na tego typu przypadki i i ewentualnie wyłączyć cały mechanizm dla takich procedur używając np. hintu disable_batch_mode_memore_grant_feedback.

Jak więc widzicie zarówno niedoszacowania jak i przeszacowania mogą zostać rozwiązane przy pomocy Memory Grant Feedback. Warto mieć na uwadze, że w przypadku jeśli przydzielona ilość pamięci dwukrotnie przekroczy ilość rzeczywiście potrzebną to memory grant zostanie ponownie wyliczony i zapisany w cache. Co ciekawe gdy kilkukrotnie wykonamy zapytanie możemy czasem zauważyć, że z każdym kolejnym wykonaniem grant będzie bardziej dopasowany. Tak więc warto sobie uświadomić, że zrzuty na dysk czy za duże przydziały nadal występują jednakże kolejne wykonania będą nadpisywały plan w cache z przeliczonym grantem.

Jak już wspomniałem na moment pisania tego artykułu mechanizm ten jest związany jedynie z przetwarzaniem w trybie Batch czyli będzie miało miejsce tylko w przypadku występowania na jednej z tabel indeksu kolumnowego, jednakże od SQL Server 2019 będzie to również możliwe w trybie ROW. Pamiętać należy również, że cały opisywany mechanizm wiąże się z cache planów zapytań dlatego też jeżeli wyłączyliście całkowicie parameter sniffing lub gdy używacie WITH RECOMPILE to nie będzie on miał żadnego wpływu na zapytanie.

Warto wiedzieć jakimi prawami rządzi się memory grant feedback i kiedy może on być pomocny, a kiedy nie. Dla mnie osobiście każde ulepszenie tego typu jest dużym krokiem naprzód, który pozwoli uniknąć niektórych problemów i jest kolejnym powodem przejścia na wyższą wersję SQL Server lub powędrować do chmury gdzie inteligentne przetwarzanie również zostało zaimplementowane.

Leave a Reply