Rozwiązywanie problemów z niską wydajnością lub małą ilością pamięci spowodowanych przez przyznawanie pamięci w SQL Server
Co to są dotacje na pamięć?
Dotacje na pamięć, nazywane również rezerwacjami wykonywania zapytań (QE), pamięcią wykonywania zapytań, pamięcią obszaru roboczego i rezerwacjami pamięci, opisują użycie pamięci w czasie wykonywania zapytania. SQL Server przydziela tę pamięć podczas wykonywania zapytania dla co najmniej jednego z następujących celów:
- Operacje sortowania
- Operacje skrótu
- Operacje kopiowania zbiorczego (nie jest to typowy problem)
- Tworzenie indeksu, w tym wstawianie do indeksów MAGAZYNU KOLUMN, ponieważ słowniki/tabele skrótów są używane w czasie wykonywania do kompilowania indeksu (nie jest to typowy problem)
Aby zapewnić pewien kontekst, w okresie jego istnienia zapytanie może zażądać pamięci od różnych alokatorów pamięci lub urzędników w zależności od tego, co musi zrobić. Na przykład gdy zapytanie jest początkowo analizowane i kompilowane, zużywa pamięć kompilacji. Po skompilowaniu zapytania ta pamięć jest zwalniana, a wynikowy plan zapytania jest przechowywany w pamięci podręcznej planu. Po zapisaniu planu w pamięci podręcznej zapytanie jest gotowe do wykonania. Jeśli zapytanie wykonuje jakiekolwiek operacje sortowania, operacje dopasowania skrótu (JOIN lub agreguje) lub wstawia do indeksów MAGAZYNU KOLUMN, używa pamięci z alokatora wykonywania zapytań. Początkowo zapytanie prosi o tę pamięć wykonywania, a później, jeśli ta pamięć zostanie przyznana, zapytanie używa całej pamięci lub jej części do sortowania wyników lub zasobników skrótów. Ta pamięć przydzielona podczas wykonywania zapytania jest nazywana przydzielaną pamięcią. Jak można sobie wyobrazić, po zakończeniu operacji wykonywania zapytania przyznawanie pamięci jest zwalniane z powrotem do SQL Server do użycia w innych pracach. W związku z tym alokacje przyznawania pamięci mają charakter tymczasowy, ale nadal mogą trwać długo. Jeśli na przykład wykonanie zapytania wykonuje operację sortowania na bardzo dużym zestawie wierszy w pamięci, sortowanie może potrwać wiele sekund lub minut, a udzielona pamięć jest używana przez okres istnienia zapytania.
Przykład zapytania z udzieleniem pamięci
Oto przykład zapytania, które używa pamięci wykonywania i planu zapytania pokazującego udzielenie:
SELECT *
FROM sys.messages
ORDER BY message_id
To zapytanie wybiera zestaw wierszy ponad 300 000 wierszy i sortuje go. Operacja sortowania wywołuje żądanie udzielenia pamięci. Jeśli to zapytanie zostanie uruchomione w programie SSMS, możesz wyświetlić jego plan zapytania. Po wybraniu operatora planu SELECT zapytania po lewej stronie możesz wyświetlić informacje o udzieleniu pamięci dla zapytania (naciśnij klawisz F4 , aby wyświetlić właściwości):
Ponadto, jeśli klikniesz prawym przyciskiem myszy biały znak w planie zapytania, możesz wybrać pozycję Pokaż xml planu wykonywania... i zlokalizować element XML, który pokazuje te same informacje o udzieleniu pamięci.
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
Kilka terminów wymaga wyjaśnienia tutaj. Zapytanie może wymagać pewnej ilości pamięci wykonywania (DesiredMemory) i często żąda tej kwoty (RequestedMemory). W czasie wykonywania SQL Server przyznaje całą lub część żądanej pamięci w zależności od dostępności (GrantedMemory). W końcu zapytanie może używać więcej lub mniej początkowo żądanej pamięci (MaxUsedMemory). Jeśli optymalizator zapytań przecenił ilość wymaganej pamięci, używa mniejszej ilości niż żądany rozmiar. Ale ta pamięć jest marnowana, ponieważ mogła być używana przez inne żądanie. Z drugiej strony, jeśli optymalizator nie docenił rozmiaru wymaganej pamięci, nadmiar wierszy może zostać rozlany na dysk, aby wykonać pracę w czasie wykonywania. Zamiast przydzielać więcej pamięci niż początkowo żądany rozmiar, SQL Server wypycha dodatkowe wiersze na dysk i używa go jako tymczasowego obszaru roboczego. Aby uzyskać więcej informacji, zobacz Workfiles and Worktables in Memory Grant Considerations (Zagadnienia dotyczące przyznawania pamięci w plikach roboczych i tabelach roboczych).
Terminologia
Przejrzyjmy różne terminy, które mogą wystąpić w odniesieniu do tego użytkownika pamięci. Ponownie opisano wszystkie te pojęcia związane z tymi samymi alokacjami pamięci.
Pamięć wykonywania zapytań (pamięć QE): Ten termin służy do wyróżniania faktu, że pamięć sortowania lub skrótu jest używana podczas wykonywania zapytania. Najczęściej pamięć QE jest największym konsumentem pamięci w okresie życia zapytania.
Rezerwacje wykonywania zapytań (QE) lub rezerwacje pamięci: Gdy zapytanie wymaga pamięci na potrzeby operacji sortowania lub skrótu, wysyła żądanie rezerwacji dla pamięci. To żądanie rezerwacji jest obliczane w czasie kompilacji na podstawie szacowanej kardynalności. Później, po wykonaniu zapytania, SQL Server przyznaje to żądanie częściowo lub w pełni w zależności od dostępności pamięci. W końcu zapytanie może używać procentu przyznanej pamięci. Istnieje urzędnik pamięci (księgowy pamięci) o nazwie "MEMORYCLERK_SQLQERESERVATIONS", który śledzi te alokacje pamięci (sprawdź DBCC MEMORYSTATUS lub sys.dm_os_memory_clerks).
Dotacje na pamięć: Gdy SQL Server przyznaje żądaną pamięć do kwerendy wykonującej, mówi się, że nastąpiło przyznanie pamięci. Istnieje kilka liczników wydajności, które używają terminu "grant". Te liczniki
Memory Grants OutstandingiMemory Grants Pending, wyświetlają liczbę wystąpień pamięci, które zostały spełnione lub oczekują. Nie uwzględniają rozmiaru przyznawania pamięci. Jedno zapytanie mogło na przykład użyć 4 GB pamięci do wykonania sortowania, ale nie jest to odzwierciedlone w żadnej z tych liczników.Pamięć obszaru roboczego to kolejny termin opisujący tę samą pamięć. Często ten termin może być wyświetlany w liczniku
Granted Workspace Memory (KB)Perfmon , który odzwierciedla ogólną ilość pamięci używaną obecnie do operacji sortowania, skrótu, kopiowania zbiorczego i tworzenia indeksu wyrażonych w KB. InnyMaximum Workspace Memory (KB)licznik odpowiada za maksymalną ilość dostępnej pamięci obszaru roboczego dla wszystkich żądań, które mogą wymagać wykonania takich operacji tworzenia skrótu, sortowania, kopiowania zbiorczego i tworzenia indeksu. Termin Pamięć obszaru roboczego występuje rzadko poza tymi dwoma licznikami.
Wpływ użycia dużej pamięci QE na wydajność
W większości przypadków, gdy wątek żąda pamięci wewnątrz SQL Server, aby coś zrobić, a pamięć nie jest dostępna, żądanie kończy się niepowodzeniem z powodu błędu braku pamięci. Istnieje jednak kilka scenariuszy wyjątków, w których wątek nie kończy się niepowodzeniem, ale czeka, aż pamięć stanie się dostępna. Jednym z tych scenariuszy jest przyznawanie pamięci, a drugi to pamięć kompilacji zapytań. SQL Server używa obiektu synchronizacji wątków o nazwie semafor, aby śledzić ilość pamięci przyznanej na potrzeby wykonywania zapytania. Jeśli SQL Server zabraknie wstępnie zdefiniowanego obszaru roboczego QE, zamiast kończyć się niepowodzeniem zapytania z błędem braku pamięci, zapytanie będzie czekać. Biorąc pod uwagę, że pamięć obszaru roboczego może przyjmować znaczną część ogólnej SQL Server pamięci, oczekiwanie na pamięć w tej przestrzeni ma poważne konsekwencje dla wydajności. Duża liczba współbieżnych zapytań zażądała pamięci wykonywania i razem wyczerpała pulę pamięci QE lub kilka współbieżnych zapytań zażądało bardzo dużych dotacji. Tak czy inaczej, wynikowe problemy z wydajnością mogą mieć następujące objawy:
- Dane i strony indeksu z pamięci podręcznej buforu zostały prawdopodobnie opróżnione, aby zwolnić miejsce na żądania udzielenia dużej pamięci. Oznacza to, że odczyty stron pochodzące z żądań zapytań muszą być spełnione z dysku (znacznie wolniejsza operacja).
- Żądania dotyczące innych alokacji pamięci mogą kończyć się niepowodzeniem z powodu błędów braku pamięci, ponieważ zasób jest powiązany z operacjami sortowania, skrótu lub kompilowania indeksu.
- Żądania, które wymagają pamięci wykonywania, oczekują, aż zasób stanie się dostępny i ukończenie ich zajmuje dużo czasu. Innymi słowy, dla użytkownika końcowego te zapytania są powolne.
Dlatego jeśli zaobserwujesz oczekiwania na pamięć wykonywania zapytań w narzędziu Perfmon, dynamiczne widoki zarządzania (DMV) lub DBCC MEMORYSTATUS, musisz podjąć działania w celu rozwiązania tego problemu, szczególnie jeśli problem występuje często. Aby uzyskać więcej informacji, zobacz Co może zrobić deweloper w zakresie operacji sortowania i wyznaczania wartości skrótu.
Jak zidentyfikować oczekiwania na pamięć wykonywania zapytań
Istnieje wiele sposobów określania oczekiwania na rezerwacje QE. Wybierz te, które najlepiej Ci służą, aby zobaczyć większy obraz na poziomie serwera. Niektóre z tych narzędzi mogą być niedostępne (na przykład narzędzie Perfmon nie jest dostępne w usłudze Azure SQL Database). Po zidentyfikowaniu problemu należy przejść do szczegółów na poziomie poszczególnych zapytań, aby zobaczyć, które zapytania wymagają dostrajania lub ponownego zapisywania.
Na poziomie serwera użyj następujących metod:
- Sys.dm_exec_query_resource_semaphores DMV semafora zasobów Aby uzyskać więcej informacji, zobacz sys.dm_exec_query_resource_semaphores.
- monitor wydajności liczniki Aby uzyskać więcej informacji, zobacz SQL Server Obiekt Menedżera pamięci.
- DBCC MEMORYSTATUS Aby uzyskać więcej informacji, zobacz DBCC MEMORYSTATUS.
- Sys.dm_os_memory_clerks DMV urzędników ds. pamięci Aby uzyskać więcej informacji, zobacz sys.dm_os_memory_clerks.
- Identyfikowanie dotacji pamięci przy użyciu zdarzeń rozszerzonych (XEvents) Aby uzyskać więcej informacji, zobacz Zdarzenia rozszerzone (XEvents).
Na poziomie poszczególnych zapytań użyj następujących metod:
- Identyfikowanie konkretnych zapytań za pomocą sys.dm_exec_query_memory_grants: Obecnie wykonywane zapytania. Aby uzyskać więcej informacji, zobacz sys.dm_exec_query_memory_grants.
- Identyfikowanie konkretnych zapytań za pomocą sys.dm_exec_requests: obecnie wykonywane zapytania. Aby uzyskać więcej informacji, zobacz sys.dm_exec_requests.
- Identyfikowanie konkretnych zapytań za pomocą sys.dm_exec_query_stats: statystyki historyczne dotyczące zapytań. Aby uzyskać więcej informacji, zobacz sys.dm_exec_query_stats.
- Identyfikowanie określonych zapytań przy użyciu magazynu zapytań (QDS) z sys.query_store_runtime_stats: statystyki historyczne dotyczące zapytań za pomocą usług QDS. Aby uzyskać więcej informacji, zobacz sys.query_store_runtime_stats.
Agregowanie statystyk użycia pamięci
Sys.dm_exec_query_resource_semaphores DMV semafora zasobów
Ten widok DMV dzieli pamięć rezerwacji zapytań według puli zasobów (wewnętrznej, domyślnej i utworzonej przez użytkownika) i resource_semaphore (zwykłych i małych żądań zapytań). Przydatne zapytanie może być następujące:
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
Poniższe przykładowe dane wyjściowe pokazują, że około 900 MB pamięci wykonywania zapytań jest używane przez 22 żądania, a 3 kolejne oczekują. Ma to miejsce w puli domyślnej (pool_id = 2) i zwykłym semaforze zapytania (resource_semaphore_id = 0).
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
liczniki monitor wydajności
Podobne informacje są dostępne za pośrednictwem liczników monitor wydajności, gdzie można obserwować aktualnie udzielone żądania (Memory Grants Outstanding), żądania udzielenia oczekiwania (Memory Grants Pending) i ilość pamięci używanej przez dotacje pamięci (Granted Workspace Memory (KB)). Na poniższej ilustracji zaległe dotacje to 18, oczekujące dotacje to 2, a przyznana pamięć obszaru roboczego to 828 288 KB. Licznik Memory Grants Pending Perfmon z wartością inną niżzer wskazuje, że pamięć została wyczerpana.
Aby uzyskać więcej informacji, zobacz SQL Server Obiekt Menedżera pamięci.
- SQLServer, Menedżer pamięci: maksymalna ilość pamięci obszaru roboczego (KB)
- SQLServer, Menedżer pamięci: Pamięć przyznaje zaległe
- SQLServer, Menedżer pamięci: Oczekujące dotacje na pamięć
- SQLServer, Menedżer pamięci: Przyznano pamięć obszaru roboczego (KB)
DBCC MEMORYSTATUS
Innym miejscem, w którym można zobaczyć szczegóły dotyczące pamięci rezerwacji zapytań, jest DBCC MEMORYSTATUS (sekcja Obiekty pamięci zapytań). Możesz przyjrzeć się danym wyjściowym Query Memory Objects (default) zapytań użytkowników. Jeśli na przykład włączono funkcję Resource Governor z pulą zasobów o nazwie PoolAdmin, możesz przyjrzeć się obu Query Memory Objects (default) elementom i Query Memory Objects (PoolAdmin).
Oto przykładowe dane wyjściowe z systemu, w którym 18 żądań otrzymało pamięć wykonywania zapytań, a 2 żądania oczekują na pamięć. Dostępny licznik to zero, co oznacza, że nie ma więcej dostępnej pamięci obszaru roboczego. Ten fakt wyjaśnia dwa oczekujące żądania. Pokazuje Wait Time czas upłynął w milisekundach od momentu umieszczenia żądania w kolejce oczekiwania. Aby uzyskać więcej informacji na temat tych liczników, zobacz Obiekty pamięci zapytań.
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS Wyświetla również informacje o urzędniku pamięci, który śledzi pamięć wykonywania zapytań. Poniższe dane wyjściowe pokazują, że strony przydzielone do wykonywania zapytań (QE) rezerwacje przekraczają 800 MB.
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
Sys.dm_os_memory_clerks DMV urzędników ds. pamięci
Jeśli potrzebujesz więcej tabelarycznego zestawu wyników, innego niż zestaw wyników oparty na DBCC MEMORYSTATUSsekcjach, możesz użyć sys.dm_os_memory_clerks , aby uzyskać podobne informacje. Poszukaj urzędnika MEMORYCLERK_SQLQERESERVATIONS pamięci. Obiekty pamięci zapytań nie są jednak dostępne w tym DMV.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Oto przykładowe dane wyjściowe:
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Identyfikowanie dotacji pamięci przy użyciu zdarzeń rozszerzonych (XEvents)
Istnieje wiele zdarzeń rozszerzonych, które dostarczają informacji o przyznawaniu pamięci i umożliwiają przechwytywanie tych informacji za pośrednictwem śledzenia:
- sqlserver.additional_memory_grant: występuje, gdy zapytanie próbuje uzyskać więcej przydziału pamięci podczas wykonywania. Nie można uzyskać tego dodatkowego przydziału pamięci może spowodować spowolnienie zapytania.
- sqlserver.query_memory_grant_blocking: występuje, gdy zapytanie blokuje inne zapytania podczas oczekiwania na udzielenie pamięci.
- sqlserver.query_memory_grant_info_sampling: występuje na końcu losowo próbkowanych zapytań dostarczających informacje o przyznawaniu pamięci (może być używana na przykład do telemetrii).
- sqlserver.query_memory_grant_resource_semaphores: występuje w odstępach pięciu minut dla każdej puli zasobów zarządcy zasobów.
- sqlserver.query_memory_grant_usage: Występuje na końcu przetwarzania zapytań dla zapytań z przydziałem pamięci powyżej 5 MB, aby poinformować użytkowników o nieścisłościach udzielania pamięci.
- sqlserver.query_memory_grants: występuje w odstępach pięciu minut dla każdego zapytania z przydziałem pamięci.
Zdarzenia rozszerzone przekazywania opinii o pamięci
Aby uzyskać informacje na temat funkcji przekazywania opinii o przyznawaniu pamięci przetwarzania zapytań, zobacz Opinie dotyczące przyznawania pamięci.
- sqlserver.memory_grant_feedback_loop_disabled: występuje, gdy pętla sprzężenia zwrotnego przyznawania pamięci jest wyłączona.
- sqlserver.memory_grant_updated_by_feedback: występuje, gdy przyznawanie pamięci jest aktualizowane przez opinię.
Ostrzeżenia dotyczące wykonywania zapytań, które odnoszą się do dotacji na pamięć
- sqlserver.execution_warning: występuje, gdy instrukcja języka T-SQL lub procedura składowana czeka więcej niż jedną sekundę na udzielenie pamięci lub gdy początkowa próba uzyskania pamięci kończy się niepowodzeniem. Użyj tego zdarzenia w połączeniu ze zdarzeniami identyfikującymi oczekiwania na rozwiązywanie problemów z rywalizacją, które mają wpływ na wydajność.
- sqlserver.hash_spill_details: występuje na końcu przetwarzania skrótów, jeśli nie ma wystarczającej ilości pamięci do przetworzenia danych wejściowych kompilacji sprzężenia skrótu. Użyj tego zdarzenia razem z dowolnym zdarzeniem
query_pre_execution_showplanlubquery_post_execution_showplan, aby określić, która operacja w wygenerowanym planie powoduje rozlanie skrótu. - sqlserver.hash_warning: występuje, gdy nie ma wystarczającej ilości pamięci do przetworzenia danych wejściowych kompilacji sprzężenia skrótu. Powoduje to rekursję skrótu, gdy dane wejściowe kompilacji są partycjonowane, lub akcja ratunkowa skrótu, gdy partycjonowanie danych wejściowych kompilacji przekracza maksymalny poziom rekursji. Użyj tego zdarzenia razem z dowolnym zdarzeniem
query_pre_execution_showplanlubquery_post_execution_showplan, aby określić, która operacja w wygenerowanym planie powoduje ostrzeżenie o skrótie. - sqlserver.sort_warning: występuje, gdy operacja sortowania wykonywanego zapytania nie mieści się w pamięci. To zdarzenie nie jest generowane dla operacji sortowania spowodowanych przez tworzenie indeksu, tylko w przypadku operacji sortowania w zapytaniu. (Na przykład w
Order BySelectinstrukcji). To zdarzenie służy do identyfikowania zapytań, które działają powoli z powodu operacji sortowania, szczególnie gdywarning_typeparametr = 2 wskazuje, że do sortowania wymagane jest wielokrotne przekazanie danych.
Planowanie generowania zdarzeń zawierających informacje o przyznawaniu pamięci
Poniższy plan zapytania generujący zdarzenia rozszerzone domyślnie zawiera pola granted_memory_kb i ideal_memory_kb :
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
Tworzenie indeksu magazynu kolumn
Jednym z obszarów objętych przez XEvents jest pamięć wykonywania używana podczas kompilowania magazynu kolumn. Jest to lista dostępnych zdarzeń:
- sqlserver.column_store_index_build_low_memory: Aparat magazynu wykrył stan małej ilości pamięci, a rozmiar grupy wierszy został zmniejszony. Istnieje kilka interesujących kolumn.
- sqlserver.column_store_index_build_memory_trace: śledzenie użycia pamięci podczas kompilacji indeksu.
- sqlserver.column_store_index_build_memory_usage_scale_down: Aparat magazynu został przeskalowany w dół.
- sqlserver.column_store_index_memory_estimation: pokazuje wynik szacowania pamięci podczas kompilacji grupy wierszy MAGAZYNU KOLUMN.
Identyfikowanie konkretnych zapytań
Istnieją dwa rodzaje zapytań, które można znaleźć podczas przeglądania poszczególnych poziomów żądań. Zapytania, które zużywają dużą ilość pamięci wykonywania zapytań i te, które oczekują na tę samą pamięć. Ta ostatnia grupa może składać się z żądań o niewielkich potrzebach dotyczących dotacji na pamięć, a jeśli tak, możesz skupić swoją uwagę gdzie indziej. Ale mogą być również winowajcami, jeśli żądają ogromnych rozmiarów pamięci. Skoncentruj się na nich, jeśli okaże się, że tak jest. Często zdarza się, że jednym z konkretnych zapytań jest sprawca, ale wiele jego wystąpień jest zrodziło się. Wystąpienia, które uzyskują dotacje na pamięć, powodują, że inne wystąpienia tego samego zapytania czekają na udzielenie. Niezależnie od konkretnych okoliczności, ostatecznie należy zidentyfikować zapytania i rozmiar żądanej pamięci wykonywania.
Identyfikowanie określonych zapytań za pomocą sys.dm_exec_query_memory_grants
Aby wyświetlić pojedyncze żądania i rozmiar pamięci, których zażądali i których udzielono, możesz wykonać zapytanie w dynamicznym sys.dm_exec_query_memory_grants widoku zarządzania. Ten widok DMV zawiera informacje o aktualnie wykonywanych zapytaniach, a nie o informacjach historycznych.
Poniższa instrukcja pobiera dane z DMV, a także pobiera tekst zapytania i plan zapytania w wyniku:
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Oto skrócone przykładowe dane wyjściowe zapytania podczas aktywnego użycia pamięci QE. Większość zapytań ma przyznaną pamięć, jak pokazano i granted_memory_kbused_memory_kb jest wartościami liczbowymi bez wartości NULL. Zapytania, które nie otrzymały udzielonego żądania, oczekują na pamięć wykonywania i .granted_memory_kb = NULL Ponadto są one umieszczane w kolejce oczekiwania z wartością queue_id = 6. Ich wait_time_ms wskazuje około 37 sekund oczekiwania. Sesja 72 jest następna w wierszu, aby uzyskać grant wskazany przez wait_order = 1, podczas gdy sesja 74 jest po niej z wait_order = 2.
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
Identyfikowanie określonych zapytań za pomocą sys.dm_exec_requests
Istnieje typ oczekiwania w SQL Server, który wskazuje, że zapytanie czeka na udzielenie RESOURCE_SEMAPHOREpamięci . Ten typ oczekiwania może być obserwowany w sys.dm_exec_requests przypadku poszczególnych żądań. Ten ostatni widok DMV jest najlepszym punktem wyjścia do określenia, które zapytania są ofiarami niewystarczającej ilości pamięci przyznawania. Możesz również obserwować RESOURCE_SEMAPHORE oczekiwanie w sys.dm_os_wait_stats jako zagregowane punkty danych na poziomie SQL Server. Ten typ oczekiwania jest wyświetlany, gdy nie można udzielić żądania pamięci zapytania z powodu użycia pamięci przez inne zapytania współbieżne. Duża liczba żądań oczekiwania i długi czas oczekiwania wskazują na nadmierną liczbę współbieżnych zapytań przy użyciu pamięci wykonywania lub dużych rozmiarów żądań pamięci.
Uwaga
Czas oczekiwania na udzielenie pamięci jest skończony. Po nadmiernym oczekiwaniu (na przykład ponad 20 minut) SQL Server przekroczenie limitu czasu zapytania i wystąpienie błędu 8645: "Wystąpił przekroczenie limitu czasu podczas oczekiwania na wykonanie zapytania przez zasoby pamięci. Uruchom ponownie zapytanie". Wartość limitu czasu ustawiona na poziomie serwera może zostać wyświetlona, patrząc na timeout_sec wartość w sys.dm_exec_query_memory_grantspliku . Wartość limitu czasu może się nieznacznie różnić w zależności od wersji SQL Server.
Za pomocą polecenia sys.dm_exec_requestsmożna zobaczyć, które zapytania otrzymały pamięć i rozmiar tego przyznania. Ponadto możesz określić, które zapytania obecnie oczekują na udzielenie pamięci, poszukując RESOURCE_SEMAPHORE typu oczekiwania. Oto zapytanie, które pokazuje zarówno udzielone, jak i oczekujące żądania:
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
Przykładowe dane wyjściowe pokazują, że dwa żądania otrzymały pamięć, a dwa tuziny innych czeka na dotacje. Kolumna granted_query_memory raportuje rozmiar na stronach 8 KB. Na przykład wartość 34 709 oznacza 34 709 * 8 KB = 277 672 KB przyznanej pamięci.
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
Identyfikowanie określonych zapytań za pomocą sys.dm_exec_query_stats
Jeśli problem z udzielaniem pamięci nie występuje w tej chwili, ale chcesz zidentyfikować nieprawidłowe zapytania, możesz zapoznać się z danymi zapytań historycznych za pośrednictwem sys.dm_exec_query_stats. Okres istnienia danych jest powiązany z planem zapytania każdego zapytania. Po usunięciu planu z pamięci podręcznej planu odpowiednie wiersze zostaną wyeliminowane z tego widoku. Innymi słowy, DMV przechowuje statystyki w pamięci, które nie są zachowywane po ponownym uruchomieniu SQL Server lub po naciśnięciu pamięci powoduje wydanie pamięci podręcznej planu. To powiedziawszy, informacje można znaleźć tutaj cenne, szczególnie w przypadku zagregowanych statystyk zapytań. Ktoś mógł ostatnio zgłaszać, że widzi duże przydziały pamięci z zapytań, ale gdy spojrzysz na obciążenie serwera, możesz odkryć, że problem zniknął. W tej sytuacji może dostarczyć szczegółowych informacji, sys.dm_exec_query_stats których nie mogą uzyskać inne pliki DVM. Oto przykładowe zapytanie, które może pomóc w znalezieniu 20 najpopularniejszych instrukcji, które zużywały największe ilości pamięci wykonywania. Te dane wyjściowe wyświetlają poszczególne instrukcje, nawet jeśli ich struktura zapytań jest taka sama. Na przykład SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 jest to oddzielny wiersz od SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (zmienia się tylko wartość predykatu filtru). Zapytanie pobiera 20 pierwszych instrukcji o maksymalnym rozmiarze przydziału większym niż 5 MB.
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
Jeszcze bardziej zaawansowaną analizę można uzyskać, analizując zapytania zagregowane przez query_hash. W tym przykładzie przedstawiono sposób znajdowania średnich, maksymalnych i minimalnych rozmiarów dotacji dla instrukcji zapytania we wszystkich jej wystąpieniach, ponieważ plan zapytania został po raz pierwszy zapisany w pamięci podręcznej.
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
Kolumna Sample_Statement_Text przedstawia przykład struktury zapytań, która jest zgodna z skrótem zapytania, ale powinna być odczytywana bez względu na określone wartości w instrukcji . Jeśli na przykład instrukcja zawiera WHERE Id = 5polecenie , możesz ją odczytać w bardziej ogólnej postaci: WHERE Id = @any_value.
Oto skrócone przykładowe dane wyjściowe zapytania z wyświetlonymi tylko wybranymi kolumnami:
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
Identyfikowanie określonych zapytań przy użyciu magazynu zapytań (QDS) z sys.query_store_runtime_stats
Jeśli masz włączony magazyn zapytań, możesz skorzystać z jego utrwalonych statystyk historycznych. W przeciwieństwie do danych z sys.dm_exec_query_statsprogramu te statystyki przetrwają SQL Server ponownego uruchomienia lub obciążenia pamięci, ponieważ są one przechowywane w bazie danych. Usługa QDS ma również limity rozmiaru i zasady przechowywania. Aby uzyskać więcej informacji, zobacz Sekcje Ustawianie optymalnego trybu przechwytywania magazynu zapytań i Przechowywanie najbardziej odpowiednich danych w magazynie zapytań w sekcji Najlepsze rozwiązania dotyczące zarządzania magazynem zapytań.
Określ, czy w bazach danych jest włączony magazyn zapytań przy użyciu tego zapytania:
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Uruchom następujące zapytanie diagnostyczne w kontekście określonej bazy danych, którą chcesz zbadać:
SELECT MAX(qtxt.query_sql_text) AS sample_sql_text ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb ,SUM(count_executions) AS count_query_executions FROM sys.query_store_runtime_stats rts JOIN sys.query_store_plan p ON p.plan_id = rts.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id LEFT OUTER JOIN sys.query_store_query_text qtxt ON q.query_text_id = qtxt.query_text_id GROUP BY q.query_hash HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB ORDER BY SUM(avg_query_max_used_memory) DESC OPTION (MAX_GRANT_PERCENT = 5)Zasady w tym miejscu są takie same jak
sys.dm_exec_query_stats; zobaczysz zagregowane statystyki dla instrukcji. Jednak jedna różnica polega na tym, że w przypadku usług QDS patrzysz tylko na zapytania w zakresie tej bazy danych, a nie na cały SQL Server. Może więc być konieczne zapoznanie się z bazą danych, w której zostało wykonane określone żądanie udzielenia pamięci. W przeciwnym razie uruchom to zapytanie diagnostyczne w wielu bazach danych, dopóki nie znajdziesz dużego przydziału pamięci.Oto skrócone przykładowe dane wyjściowe:
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ---------------------- SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14 SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2 insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16 SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2 SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1 select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9 SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1 (@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
Niestandardowe zapytanie diagnostyczne
Oto zapytanie, które łączy dane z wielu widoków, w tym trzy wymienione wcześniej. Zapewnia bardziej szczegółowy wgląd w sesje i ich dotacje za pośrednictwem sys.dm_exec_requests i sys.dm_exec_query_memory_grants, oprócz statystyk na poziomie serwera dostarczonych przez sys.dm_exec_query_resource_semaphores.
Uwaga
To zapytanie zwróci dwa wiersze na sesję ze względu na użycie sys.dm_exec_query_resource_semaphores (jeden wiersz dla semafora zwykłego zasobu, a drugi dla semafora zasobu małego zapytania).
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
Uwaga
Wskazówka jest używana LOOP JOIN w tym zapytaniu diagnostycznym, aby uniknąć przyznawania pamięci przez samo zapytanie i nie ORDER BY jest używana żadna klauzula. Jeśli zapytanie diagnostyczne kończy się oczekiwaniem na samą dotację, jego cel diagnozowania dotacji pamięci zostanie pokonany. Wskazówka LOOP JOIN może potencjalnie spowodować wolniejsze działanie zapytania diagnostycznego, ale w tym przypadku ważniejsze jest uzyskanie wyników diagnostycznych.
Oto skrócone przykładowe dane wyjściowe z tego zapytania diagnostycznego z tylko wybranymi kolumnami.
| Session_id | wait_time | wait_type | requested_memory_mb | granted_memory_mb | required_memory_mb | max_used_memory_mb | resource_pool_id |
|---|---|---|---|---|---|---|---|
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
Przykładowe dane wyjściowe jasno pokazują, w jaki sposób zapytanie przesłane przez session_id program = 60 pomyślnie otrzymało żądane udzielenie 9 MB pamięci, ale tylko 7 MB było wymagane do pomyślnego uruchomienia wykonywania zapytania. W końcu zapytanie używało tylko 1 MB z 9 MB otrzymanych od serwera. Dane wyjściowe pokazują również, że sesje 75 i 86 oczekują na dotacje na pamięć, w związku z RESOURCE_SEMAPHOREwait_typeczym . Ich czas oczekiwania wynosi ponad 1300 sekund (21 minut), a ich granted_memory_mb czas oczekiwania to NULL.
To zapytanie diagnostyczne jest przykładem, dlatego możesz je modyfikować w dowolny sposób, który odpowiada Twoim potrzebom. Wersja tego zapytania jest również używana w narzędziach diagnostycznych używanych przez firmę Microsoft SQL Server pomocy technicznej.
Narzędzia diagnostyczne
Istnieją narzędzia diagnostyczne używane przez firmę Microsoft SQL Server pomocy technicznej do zbierania dzienników i wydajniejszego rozwiązywania problemów. Sql LogScout i Pssdiag Configuration Manager (wraz z sqldiag) zbierają dane wyjściowe wcześniej opisanych widoków DMV i liczników monitor wydajności, które mogą pomóc w diagnozowaniu problemów z udzielaniem pamięci.
Jeśli uruchamiasz narzędzie SQL LogScout ze scenariuszami LightPerf, GeneralPerf lub DetailedPerf , narzędzie zbiera niezbędne dzienniki. Następnie można ręcznie sprawdzić YourServer_PerfStats.out i wyszukać -- dm_exec_query_resource_semaphores -- i -- dm_exec_query_memory_grants -- dane wyjściowe. Możesz też zaimportować dane wyjściowe pochodzące z biblioteki SQL LogScout lub PSSDIAG do bazy danych SQL Server za pomocą narzędzia SQL Nexus. Usługa SQL Nexus tworzy dwie tabele tbl_dm_exec_query_resource_semaphores i tbl_dm_exec_query_memory_grants, które zawierają informacje potrzebne do zdiagnozowania dotacji na pamięć. Biblioteki SQL LogScout i PSSDIAG również zbierają dzienniki narzędzia Perfmon w postaci . Pliki BLG, których można użyć do przeglądania liczników wydajności opisanych w sekcji liczników monitor wydajności.
Dlaczego przyznawanie pamięci jest ważne dla dewelopera lub administratora bazy danych
W oparciu o środowisko pomocy technicznej firmy Microsoft problemy z udzielaniem pamięci zwykle są najczęstszymi problemami związanymi z pamięcią. Aplikacje często wykonują pozornie proste zapytania, które mogą powodować problemy z wydajnością na SQL Server z powodu ogromnych operacji sortowania lub skrótu. Takie zapytania nie tylko zużywają dużo pamięci SQL Server, ale także powodują, że inne zapytania czekają na udostępnienie pamięci, co powoduje wąskie gardło wydajności.
Korzystając z narzędzi opisanych tutaj (DMV, liczniki perfmonów i rzeczywiste plany zapytań), możesz określić, które zapytania są użytkownikami z dużym przydziałem. Następnie możesz dostroić lub ponownie zapisać te zapytania, aby rozwiązać lub zmniejszyć użycie pamięci obszaru roboczego.
Co deweloper może zrobić z operacjami sortowania i wyznaczania wartości skrótu
Po zidentyfikowaniu konkretnych zapytań, które zużywają dużą ilość pamięci rezerwacji zapytań, możesz podjąć kroki w celu zmniejszenia ilości pamięci przez przeprojektowanie tych zapytań.
Co powoduje operacje sortowania i skrótu w zapytaniach
Pierwszym krokiem jest zapoznanie się z operacjami w zapytaniu, które mogą prowadzić do przyznawania pamięci.
Powody, dla których zapytanie miałoby używać operatora SORT:
Polecenie ORDER BY (T-SQL) prowadzi do sortowania wierszy przed przesyłaniem strumieniowym w wyniku końcowym.
Grupa według (T-SQL) może wprowadzić operator sortowania w planie zapytania przed grupowaniem, jeśli bazowy indeks nie jest obecny, który porządkuje pogrupowane kolumny.
DISTINCT (T-SQL) zachowuje się podobnie do
GROUP BY. Aby zidentyfikować odrębne wiersze, wyniki pośrednie są uporządkowane, a następnie duplikaty są usuwane. Optymalizator używaSortoperatora przed tym operatorem, jeśli dane nie są jeszcze posortowane z powodu uporządkowanego wyszukiwania indeksu lub skanowania.Operator scalania sprzężenia wybrany przez optymalizator zapytań wymaga sortowania obu sprzężonych danych wejściowych. SQL Server może wyzwolić sortowanie, jeśli indeks klastrowany nie jest dostępny w kolumnie sprzężenia w jednej z tabel.
Przyczyny użycia operatora planu zapytania HASH przez zapytanie:
Ta lista nie jest wyczerpująca, ale zawiera najczęściej spotykane przyczyny operacji skrótu. Przeanalizuj plan zapytania, aby zidentyfikować operacje dopasowania skrótu.
JOIN (T-SQL): podczas łączenia tabel SQL Server ma wybór między trzema operatorami fizycznymi,
Nested Loop,Merge JoiniHash Join. Jeśli SQL Server kończy się wybraniem sprzężenia skrótu, potrzebuje pamięci QE, aby wyniki pośrednie były przechowywane i przetwarzane. Zazwyczaj brak dobrych indeksów może prowadzić do tego najbardziej kosztownego dla zasobów operatora sprzężenia .Hash JoinAby sprawdzić plan zapytania w celu zidentyfikowaniaHash Match, zobacz Dokumentacja operatorów logicznych i fizycznych.DISTINCT (T-SQL): Operator
Hash Aggregatemoże służyć do eliminowania duplikatów w zestawie wierszy. Aby wyszukaćHash Matchelement (Aggregate) w planie zapytania, zobacz Dokumentacja operatorów logicznych i fizycznych.UNION (T-SQL): jest to podobne do
DISTINCT. ElementHash Aggregatemoże służyć do usuwania duplikatów dla tego operatora.SUM/AVG/MAX/MIN (T-SQL): Dowolną operację agregacji można potencjalnie wykonać jako
Hash Aggregate. Aby wyszukaćHash Matchelement (Aggregate) w planie zapytania, zobacz Dokumentacja operatorów logicznych i fizycznych.
Znajomość tych typowych przyczyn może pomóc wyeliminować w miarę możliwości żądania udzielenia dużej ilości pamięci przychodzące do SQL Server.
Sposoby zmniejszania operacji sortowania i skrótu lub rozmiaru przyznawania
- Aktualizowanie statystyk . Ten podstawowy krok, który zwiększa wydajność zapytań na wielu poziomach, zapewnia, że optymalizator zapytań ma najdokładniejsze informacje podczas wybierania planów zapytań. SQL Server określa, jakiego rozmiaru żądać udzielenia pamięci na podstawie statystyk. Nieaktualne statystyki mogą powodować nadmierne przeszacowanie lub niedoszacowanie wniosku o udzielenie dotacji, co prowadzi odpowiednio do niepotrzebnie wysokiego żądania udzielenia dotacji lub rozlania wyników na dysk. Upewnij się, że statystyka automatycznej aktualizacji jest włączona w bazach danych i/lub czy dane statyczne są aktualizowane przy użyciu funkcji UPDATE STATISTICS lub sp_updatestats.
- Zmniejsz liczbę wierszy pochodzących z tabel. Jeśli używasz bardziej restrykcyjnego filtru WHERE lub join i zmniejszasz liczbę wierszy, kolejne sortowanie w planie zapytania pobiera kolejność lub agreguje mniejszy zestaw wyników. Mniejszy zestaw wyników pośrednich wymaga mniejszej ilości pamięci zestawu roboczego. Jest to ogólna reguła, którą deweloperzy mogą przestrzegać nie tylko w celu zapisania pamięci zestawu roboczego, ale także w celu zmniejszenia użycia procesora CPU i operacji we/wy (ten krok nie zawsze jest możliwy). Jeśli dobrze napisane i efektywne pod względem zasobów zapytania są już wdrożone, te wytyczne zostały spełnione.
- Utwórz indeksy w kolumnach sprzężenia, aby ułatwić scalanie sprzężeń. Na operacje pośrednie w planie zapytania mają wpływ indeksy w tabeli bazowej. Jeśli na przykład tabela nie ma indeksu w kolumnie sprzężenia, a sprzężenie scalania okaże się najbardziej ekonomicznym operatorem sprzężenia, wszystkie wiersze z tej tabeli muszą zostać posortowane przed wykonaniem sprzężenia. Jeśli zamiast tego w kolumnie istnieje indeks, można wyeliminować operację sortowania.
- Tworzenie indeksów w celu uniknięcia operacji skrótu. Często podstawowe dostrajanie zapytań rozpoczyna się od sprawdzenia, czy zapytania mają odpowiednie indeksy, aby ułatwić im zmniejszenie liczby operacji odczytu i zminimalizowanie lub wyeliminowanie dużych rodzajów lub operacji skrótu, jeśli jest to możliwe. Sprzężenia skrótów są często wybierane do przetwarzania dużych, niesortowanych i nieindeksowanych danych wejściowych. Tworzenie indeksów może zmienić tę strategię optymalizatora i przyspieszyć pobieranie danych. Aby uzyskać pomoc dotyczącą tworzenia indeksów, zobacz Doradca dostrajania aparatu bazy danych i Dostrajanie indeksów nieklastruowanych z brakującymi sugestiami indeksu.
- Użyj indeksów MAGAZYNU KOLUMN, jeśli jest to odpowiednie dla zapytań agregacji korzystających z programu
GROUP BY. Zapytania analityczne, które zajmują się bardzo dużymi zestawami wierszy i zwykle wykonują agregacje "grupuj według", mogą wymagać dużych fragmentów pamięci, aby można było wykonać pracę. Jeśli indeks nie jest dostępny, który zapewnia uporządkowane wyniki, sortowanie jest automatycznie wprowadzane w planie zapytania. Rodzaj bardzo dużego wyniku może prowadzić do kosztownego przyznania pamięci. - Usuń element ,
ORDER BYjeśli nie jest potrzebny. W przypadkach, gdy wyniki są przesyłane strumieniowo do aplikacji, która sortuje wyniki na swój sposób lub umożliwia użytkownikowi modyfikowanie kolejności wyświetlanych danych, nie musisz wykonywać sortowania po stronie SQL Server. Wystarczy przesłać strumieniowo dane do aplikacji w kolejności, w jakiej serwer ją tworzy i pozwolić użytkownikowi końcowemu na samodzielne sortowanie. Aplikacje raportowania, takie jak Power BI lub Reporting Services, to przykłady takich aplikacji, które umożliwiają użytkownikom końcowym sortowanie danych. - Rozważ, choć ostrożnie, użycie wskazówki SPRZĘŻENIE PĘTLI , gdy sprzężenia istnieją w zapytaniu T-SQL. Ta technika może uniknąć sprzężeń skrótu lub scalania korzystających z dotacji na pamięć. Jednak ta opcja jest sugerowana tylko w ostateczności, ponieważ wymuszanie sprzężenia może prowadzić do znacznie wolniejszego zapytania. Przetestuj obciążenie, aby upewnić się, że jest to opcja. W niektórych przypadkach sprzężenie pętli zagnieżdżonej może nawet nie być opcją. W takim przypadku SQL Server może zakończyć się niepowodzeniem z powodu błędu MSSQLSERVER_8622 "Procesor zapytań nie może utworzyć planu zapytania ze względu na wskazówki zdefiniowane w tym zapytaniu".
Wskazówka dotycząca zapytania dotyczącego udzielania pamięci
Od SQL Server 2012 r. z dodatkiem SP3 istnieje wskazówka dotycząca zapytania, która umożliwia kontrolowanie rozmiaru przydziału pamięci na zapytanie. Oto przykład użycia tej wskazówki:
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
Zalecamy używanie w tym miejscu wartości konserwatywnych, zwłaszcza w przypadkach, gdy oczekujesz, że wiele wystąpień zapytania zostanie wykonanych jednocześnie. Upewnij się, że przetestujesz obciążenie, aby dopasować je do środowiska produkcyjnego i określić wartości do użycia.
Aby uzyskać więcej informacji, zobacz MAX_GRANT_PERCENT i MIN_GRANT_PERCENT.
Zarządca zasobów
Pamięć QE to pamięć, którą zarządca zasobów faktycznie ogranicza, gdy są używane ustawienia MIN_MEMORY_PERCENT i MAX_MEMORY_PERCENT . Po zidentyfikowaniu zapytań powodujących duże przydziały pamięci można ograniczyć pamięć używaną przez sesje lub aplikacje. Warto wspomnieć, że default grupa obciążeń umożliwia wykonywanie zapytania do 25% pamięci, którą można udzielić w wystąpieniu SQL Server. Aby uzyskać więcej informacji, zobacz Pule zasobów zarządcy zasobów i TWORZENIE GRUPY OBCIĄŻEŃ.
Adaptacyjne przetwarzanie zapytań i udzielanie opinii o pamięci
SQL Server 2017 r. wprowadzono funkcję przekazywania opinii o przyznawaniu pamięci. Umożliwia aparatowi wykonywania zapytań dostosowanie przyznawania do zapytania na podstawie wcześniejszej historii. Celem jest zmniejszenie rozmiaru przyznawania, jeśli jest to możliwe, lub zwiększenie go, gdy potrzebna jest większa ilość pamięci. Ta funkcja została wydana w trzech falach:
- Opinie dotyczące udzielania pamięci w trybie wsadowym w SQL Server 2017 r.
- Udzielanie opinii w trybie wiersza pamięci w SQL Server 2019 r.
- Udzielanie opinii o pamięci na dysku przy użyciu magazynu zapytań i przyznawania percentyla w SQL Server 2022 r.
Aby uzyskać więcej informacji, zobacz Opinie dotyczące udzielania pamięci. Funkcja przyznawania pamięci może zmniejszyć rozmiar przydziału pamięci dla zapytań w czasie wykonywania, a tym samym zmniejszyć problemy wynikające z dużych żądań udzielenia. Ta funkcja jest dostępna, szczególnie w przypadku wersji SQL Server 2019 i nowszych, w których jest dostępne przetwarzanie adaptacyjne w trybie wiersza, nie można nawet zauważyć żadnych problemów z pamięcią pochodzących z wykonywania zapytania. Jeśli jednak masz tę funkcję (domyślnie włączoną) i nadal widzisz duże zużycie pamięci QE, zastosuj kroki omówione wcześniej w celu ponownego zapisywania zapytań.
Zwiększanie SQL Server lub pamięci systemu operacyjnego
Po wykonaniu kroków w celu zmniejszenia niepotrzebnych dotacji na pamięć dla zapytań, jeśli nadal występują powiązane problemy z małą ilością pamięci, obciążenie prawdopodobnie wymaga więcej pamięci. W związku z tym należy rozważyć zwiększenie pamięci dla SQL Server przy użyciu max server memory ustawienia, jeśli w systemie jest wystarczająca ilość pamięci fizycznej, aby to zrobić. Postępuj zgodnie z zaleceniami dotyczącymi pozostawienia około 25% pamięci dla systemu operacyjnego i innych potrzeb. Aby uzyskać więcej informacji, zobacz Opcje konfiguracji pamięci serwera. Jeśli w systemie nie jest dostępna wystarczająca ilość pamięci, rozważ dodanie fizycznej pamięci RAM lub jeśli jest to maszyna wirtualna, zwiększ dedykowaną pamięć RAM dla maszyny wirtualnej.
Elementy wewnętrzne udzielania pamięci
Aby dowiedzieć się więcej na temat niektórych elementów wewnętrznych dotyczących pamięci wykonywania zapytań, zobacz wpis w blogu Understanding SQL server memory grant (Opis przyznawania pamięci serwera SQL ).
Jak utworzyć scenariusz wydajności z dużym użyciem przyznawania pamięci
Na koniec poniższy przykład ilustruje sposób symulowania dużego użycia pamięci wykonywania zapytań i wprowadzania zapytań oczekujących na RESOURCE_SEMAPHORE. Możesz to zrobić, aby dowiedzieć się, jak używać narzędzi diagnostycznych i technik opisanych w tym artykule.
Ostrzeżenie
Nie używaj tego w systemie produkcyjnym. Ta symulacja pomaga zrozumieć koncepcję i ułatwić jej lepsze poznanie.
Na serwerze testowym zainstaluj narzędzia RML i SQL Server.
Użyj aplikacji klienckiej, takiej jak SQL Server Management Studio, aby obniżyć maksymalne ustawienie pamięci serwera SQL Server do 1500 MB:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREOtwórz wiersz polecenia i zmień katalog na folder narzędzi RML:
cd C:\Program Files\Microsoft Corporation\RMLUtilsUżyj ostress.exe, aby zduplikować wiele równoczesnych żądań względem SQL Server testowych. W tym przykładzie użyto 30 równoczesnych sesji, ale można zmienić tę wartość:
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30Użyj opisanych wcześniej narzędzi diagnostycznych, aby zidentyfikować problemy z przyznawaniem pamięci.
Podsumowanie sposobów radzenia sobie z dużymi dotacjami na pamięć
- Ponowne zapisywanie zapytań.
- Aktualizuj statystyki i regularnie je aktualizuje.
- Utwórz odpowiednie indeksy dla zidentyfikowanego zapytania lub zapytań. Indeksy mogą zmniejszyć dużą liczbę przetworzonych wierszy, zmieniając
JOINalgorytmy i zmniejszając rozmiar dotacji lub całkowicie eliminując je. OPTIONUżyj wskazówki (min_grant_percent = XX, max_grant_percent = XX).- Użyj funkcji Resource Governor.
- SQL Server 2017 i 2019 r. korzystają z adaptacyjnego przetwarzania zapytań, co umożliwia mechanizmowi przekazywania opinii o pamięci dynamiczne dostosowywanie rozmiaru przyznawania pamięci w czasie wykonywania. Ta funkcja może przede wszystkim zapobiegać problemom z udzielaniem pamięci.
- Zwiększ SQL Server lub pamięć systemu operacyjnego.
Opinia
Dostępne już wkrótce: W 2024 r. będziemy stopniowo wycofywać zgłoszenia z serwisu GitHub jako mechanizm przesyłania opinii na temat zawartości i zastępować go nowym systemem opinii. Aby uzyskać więcej informacji, sprawdź: https://aka.ms/ContentUserFeedback.
Prześlij i wyświetl opinię dla