Rozwiązywanie problemów z niską wydajnością lub małą ilością pamięci spowodowanych przez przydziały pamięci w programie SQL Server
Co to są dotacje na pamięć?
Przydziały pamięci, 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. Program SQL Server przydziela tę pamięć podczas wykonywania zapytania do 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 COLUMNSTORE, ponieważ słowniki skrótów/tabele są używane w czasie wykonywania do tworzenia indeksów (nie jest to typowy problem)
Aby podać jakiś kontekst, w okresie istnienia zapytanie może żądać pamięci z 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 analizowane i kompilowane początkowo, zużywa pamięć kompilacji. Po skompilowaniu zapytania ta pamięć zostanie zwolniona, a wynikowy plan zapytania jest przechowywany w pamięci pamięci podręcznej planu. Po buforowanym planie zapytanie jest gotowe do wykonania. Jeśli zapytanie wykonuje jakiekolwiek operacje sortowania, operacje dopasowania skrótu (JOIN lub agregacje) lub wstawiania do indeksów COLUMNSTORE, używa pamięci z alokatora wykonywania zapytania. Początkowo zapytanie o tę pamięć wykonywania, a później, jeśli ta pamięć zostanie udzielona, zapytanie używa całej lub części pamięci do sortowania wyników lub zasobników skrótu. Ta pamięć przydzielona podczas wykonywania zapytania jest określana jako przydział pamięci. Jak można sobie wyobrazić, po zakończeniu operacji wykonywania zapytania przydział pamięci zostanie zwolniony z powrotem do programu SQL Server do użycia w celu wykonania innej pracy. W związku z tym alokacje przydzielania pamięci są tymczasowe, 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 przydziałem pamięci
Oto przykład zapytania korzystającego z pamięci wykonywania i planu zapytania pokazującego przyznanie:
SELECT *
FROM sys.messages
ORDER BY message_id
To zapytanie wybiera zestaw wierszy z ponad 300 000 wierszy i sortuje go. Operacja sortowania wywołuje żądanie udzielenia pamięci. Jeśli uruchomisz to zapytanie w programie SSMS, możesz wyświetlić jego plan zapytania. Po wybraniu lewego SELECT operatora planu zapytania można wyświetlić informacje o udzielaniu pamięci dla zapytania (naciśnij F4 , aby wyświetlić właściwości):
Ponadto, jeśli klikniesz prawym przyciskiem myszy biały znak w planie zapytania, możesz wybrać polecenie Pokaż kod XML planu wykonania... i zlokalizować element XML, który zawiera te same informacje o udzielaniu 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 tutaj wyjaśnienia. Zapytanie może wymagać określonej ilości pamięci wykonywania (DesiredMemory) i często żąda tej ilości (RequestedMemory). W czasie wykonywania program SQL Server udziela całej lub części żą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ń przeszacował wymaganą ilość pamięci, używa mniej niż żądany rozmiar. Jednak ta pamięć jest marnowana, ponieważ mogła być używana przez inne żądanie. Z drugiej strony, jeśli optymalizator nie docenił wymaganego rozmiaru 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, program 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 przydzielania pamięci w plikach roboczych i tabelach roboczych).
Terminologia
Przejrzyjmy różne terminy, które mogą wystąpić w odniesieniu do tego konsumenta pamięci. Ponownie wszystkie te pojęcia opisują, które odnoszą się do tych samych alokacji pamięci.
Pamięć wykonywania zapytań (pamięć QE): ten termin służy do wyróżniania faktu, że podczas wykonywania zapytania jest używana pamięć sortowania lub skrótu. Często pamięć QE jest największym odbiorcą pamięci w czasie wykonywania zapytania.
Rezerwacje wykonywania zapytań (QE) lub rezerwacje pamięci: jeśli zapytanie wymaga pamięci do operacji sortowania lub skrótu, wysyła żądanie rezerwacji do pamięci. To żądanie rezerwacji jest obliczane w czasie kompilacji na podstawie szacowanej kardynalności. Później, gdy zapytanie jest wykonywane, program SQL Server udziela żądania 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).
Granty pamięci: gdy program SQL Server udziela żądanej pamięci do wykonywania zapytania, oznacza to, że wystą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ę przydziałów pamięci satysfakcjonujących lub oczekujących. Nie uwzględniają rozmiaru przydziału pamięci. Jedno zapytanie mogło zużyć na przykład 4 GB pamięci do wykonania sortowania, ale nie zostało to odzwierciedlone w jednym z tych liczników.Pamięć obszaru roboczego to inny termin, który opisuje tę samą pamięć. Często ten termin może być widoczny w liczniku
Granted Workspace Memory (KB)wydajności , który odzwierciedla ogólną ilość pamięci używanej obecnie do sortowania, skrótu, kopiowania zbiorczego i operacji tworzenia indeksu wyrażonych w kb. InnyMaximum Workspace Memory (KB)licznik odpowiada maksymalnej ilości pamięci obszaru roboczego dostępnej dla żądań, które mogą wymagać wykonania takich operacji tworzenia skrótów, sortowania, kopiowania zbiorczego i tworzenia indeksu. Termin Pamięć obszaru roboczego występuje rzadko poza tymi dwoma licznikami.
Wpływ na wydajność dużego wykorzystania pamięci QE
W większości przypadków, gdy wątek żąda pamięci wewnątrz programu SQL Server, aby coś zrobić, a pamięć jest niedostę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 na udostępnienie pamięci. Jednym z tych scenariuszy jest przyznanie pamięci, a druga to pamięć kompilacji zapytań. Program SQL Server używa obiektu synchronizacji wątków o nazwie semafora , aby śledzić ilość pamięci przydzielonej do wykonywania zapytań. Jeśli program SQL Server zabraknie wstępnie zdefiniowanego obszaru roboczego QE, zamiast niepowodzenia zapytania z powodu błędu braku pamięci, powoduje to oczekiwanie zapytania. Biorąc pod uwagę, że pamięć obszaru roboczego może zająć znaczną część ogólnej pamięci programu SQL Server, oczekiwanie na pamięć w tym miejscu ma poważne konsekwencje dla wydajności. Duża liczba współbieżnych zapytań zażądała pamięci wykonywania, a jednocześnie wyczerpała pulę pamięci QE lub kilka współbieżnych zapytań zażądało bardzo dużych dotacji. Tak czy inaczej, wynikające z tego problemy z wydajnością mogą mieć następujące objawy:
- Strony danych i indeksów z pamięci podręcznej buforu prawdopodobnie zostały opróżnione, aby zwolnić miejsce dla dużych żądań udzielania pamięci. Oznacza to, że odczyty stron pochodzące z żądań zapytania muszą być spełnione z dysku (znacznie wolniejsza operacja).
- Żądania innych alokacji pamięci mogą zakończyć się niepowodzeniem z powodu błędów braku pamięci, ponieważ zasób jest powiązany z operacjami sortowania, skrótu lub tworzenia indeksów.
- Żądania, które wymagają pamięci wykonawczej, oczekują na udostępnienie zasobu i trwają długo. Innymi słowy, dla użytkownika końcowego te zapytania są powolne.
W związku z tym, jeśli obserwujesz oczekiwania na pamięć wykonywania zapytania w programie Perfmon, dynamicznych widokach zarządzania (DMV) lub DBCC MEMORYSTATUS, należy podjąć działania, aby rozwiązać ten problem, szczególnie jeśli problem występuje często. Aby uzyskać więcej informacji, zobacz Co deweloper może zrobić na temat operacji sortowania i skrótu.
Jak zidentyfikować oczekiwania na pamięć wykonywania zapytań
Istnieje wiele sposobów określania oczekiwania na rezerwacje QE. Wybierz te, które najlepiej obsługują, aby zobaczyć większy obraz na poziomie serwera. Niektóre z tych narzędzi mogą nie być dostę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 sprawdzić, które zapytania wymagają dostrajania lub ponownego zapisywania.
Na poziomie serwera użyj następujących metod:
- Semaphore DMV sys.dm_exec_query_resource_semaphores Aby uzyskać więcej informacji, zobacz sys.dm_exec_query_resource_semaphores.
- liczniki monitor wydajności Aby uzyskać więcej informacji, zobacz Obiekt Menedżera pamięci programu SQL Server.
- DBCC MEMORYSTATUS Aby uzyskać więcej informacji, zobacz DBCC MEMORYSTATUS.
- Buforowanie pamięci sys.dm_os_memory_clerks DMV 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 określonych zapytań przy użyciu sys.dm_exec_query_memory_grants: obecnie wykonywanie zapytań. Aby uzyskać więcej informacji, zobacz sys.dm_exec_query_memory_grants.
- Identyfikowanie określonych zapytań przy użyciu sys.dm_exec_requests: obecnie wykonywanie zapytań. Aby uzyskać więcej informacji, zobacz sys.dm_exec_requests.
- Identyfikowanie określonych zapytań przy użyciu 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) za pomocą sys.query_store_runtime_stats: statystyki historyczne dotyczące zapytań z funkcją 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 semaphore zasobów
Ten dynamiczny widok zarządzania dzieli pamięć rezerwacji zapytań według puli zasobów (wewnętrznej, domyślnej i utworzonej przez użytkownika) oraz resource_semaphore (regularne i małe żądania 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 zapytania jest używane przez 22 żądania, a 3 więcej czeka. Odbywa się to w puli domyślnej (pool_id = 2) i semaforu regularnego 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, w których można obserwować aktualnie przyznane żądania (), oczekujące żądania dotacji (Memory Grants OutstandingMemory Grants Pending) i ilość pamięci używanej przez przydziały pamięci (Granted Workspace Memory (KB)). Na poniższej ilustracji zaległe dotacje to 18, oczekujące dotacje to 2, a udzielona pamięć obszaru roboczego wynosi 828 288 KB. Licznik Memory Grants Pending wydajności z wartością niezerową wskazuje, że pamięć została wyczerpana.
Aby uzyskać więcej informacji, zobacz Obiekt Menedżera pamięci programu SQL Server.
- SQLServer, Menedżer pamięci: maksymalna ilość pamięci obszaru roboczego (KB)
- SQLServer, Menedżer pamięci: zaległe przydziały pamięci
- SQLServer, Menedżer pamięci: oczekujące przydziały pamięci
- SQLServer, Menedżer pamięci: udzielona pamięć obszaru roboczego (KB)
DBCC MEMORYSTATUS
Innym miejscem, w którym można zobaczyć szczegóły dotyczące pamięci rezerwacji zapytań, jest DBCC MEMORYSTATUSsekcja Obiekty pamięci zapytań. Dane wyjściowe zapytań użytkownika można wyświetlić Query Memory Objects (default) . Jeśli na przykład włączono zarządcę zasobów z pulą zasobów o nazwie PoolAdmin, możesz przyjrzeć się metodom Query Memory Objects (default) i Query Memory Objects (PoolAdmin).
Oto przykładowe dane wyjściowe z systemu, w którym udzielono 18 żądań pamięci wykonywania zapytań, a 2 żądania oczekują na pamięć. Dostępny licznik wynosi 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, który upłynął w milisekundach, ponieważ żądanie zostało umieszczone w kolejce oczekiwania. Aby uzyskać więcej informacji na temat tych liczników, zobacz Query memory objects (Wykonywanie zapytań o obiekty pamięci).
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 rezerwacji wykonywania zapytań (QE) 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 pamięci
Jeśli potrzebujesz więcej zestawu wyników tabelarycznych, różni się od sekcji opartej DBCC MEMORYSTATUSna sekcji , możesz użyć sys.dm_os_memory_clerks w celu uzyskania podobnych informacji. Poszukaj urzędnika MEMORYCLERK_SQLQERESERVATIONS pamięci. Obiekty pamięci kwerendy nie są jednak dostępne w tym widoku 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 zapewniają informacje o udzielaniu 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. Niepowodzenie uzyskania 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ń zapewniających informacje o udzielaniu pamięci (można jej użyć na przykład w przypadku telemetrii).
- sqlserver.query_memory_grant_resource_semaphores: występuje w pięciu minutach 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łami pamięci ponad 5 MB, aby poinformować użytkowników o niedokładności przyznawania pamięci.
- sqlserver.query_memory_grants: występuje w pięciu minutach dla każdego zapytania z przydziałem pamięci.
Zdarzenia rozszerzone dotyczące udzielania informacji zwrotnych dotyczących pamięci
Aby uzyskać informacje na temat funkcji przekazywania opinii dotyczących pamięci przetwarzania zapytań, zobacz Informacje zwrotne dotyczące udzielania pamięci.
- sqlserver.memory_grant_feedback_loop_disabled: występuje, gdy pętla przekazywania opinii o pamięci jest wyłączona.
- sqlserver.memory_grant_updated_by_feedback: występuje, gdy udzielanie pamięci jest aktualizowane przez opinię.
Ostrzeżenia dotyczące wykonywania zapytań, które odnoszą się do przydziałów pamięci
- sqlserver.execution_warning: występuje, gdy instrukcja T-SQL lub procedura składowana czeka więcej niż jedną sekundę na przyznanie pamięci lub gdy początkowa próba uzyskania pamięci zakończy się niepowodzeniem. Użyj tego zdarzenia w połączeniu ze zdarzeniami, które identyfikują oczekiwania, aby rozwiązać problemy z rywalizacją, które mają wpływ na wydajność.
- sqlserver.hash_spill_details: występuje na końcu przetwarzania skrótu, 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 wygenerowanych planach powoduje rozlanie skrótu. - sqlserver.hash_warning: występuje, gdy brakuje 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 ratowanie skrótu, gdy partycjonowanie danych wejściowych kompilacji przekracza maksymalny poziom rekursji. Użyj tego zdarzenia razem z dowolnym z
query_pre_execution_showplanzdarzeń lubquery_post_execution_showplan, aby określić, która operacja w wygenerowanych planach powoduje ostrzeżenie skrótu. - 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 tworzeniem indeksu tylko w przypadku operacji sortowania w zapytaniu. (Na przykład w
Order ByinstrukcjiSelect). To zdarzenie służy do identyfikowania zapytań, które działają powoli z powodu operacji sortowania, szczególnie gdywarning_type= 2, wskazując, że do sortowania było wymagane wiele przejść.
Planowanie generowania zdarzeń zawierających informacje o udzielaniu pamięci
Następujący plan zapytania generujący zdarzenia rozszerzone zawierają domyślnie 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
Kompilowanie indeksów magazynu kolumn
Jednym z obszarów omówionych za pośrednictwem modułów 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ł niski stan pamięci, a rozmiar grupy wierszy został zmniejszony. Istnieje kilka interesujących tutaj 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 skalowany w dół.
- sqlserver.column_store_index_memory_estimation: pokazuje wynik szacowania pamięci podczas kompilacji grupy wierszy COLUMNSTORE.
Identyfikowanie określonych zapytań
Istnieją dwa rodzaje zapytań, które można znaleźć podczas przeglądania poszczególnych żądań. Zapytania korzystające z dużej ilości pamięci wykonywania zapytań i tych, które oczekują na tę samą pamięć. Ta ostatnia grupa może składać się z żądań o skromnych potrzebach dotacji na pamięć, a jeśli tak, możesz skupić swoją uwagę w innym miejscu. Ale mogą być również sprawcami, 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 jedno konkretne zapytanie jest sprawcą, ale wiele wystąpień jest zduplikowanych. Te wystąpienia, które otrzymują dotacje na pamięć, powodują, że inne wystąpienia tego samego zapytania oczekują na przyznanie. Niezależnie od konkretnych okoliczności, należy ostatecznie zidentyfikować zapytania i rozmiar żądanej pamięci wykonywania.
Identyfikowanie określonych zapytań przy użyciu sys.dm_exec_query_memory_grants
Aby wyświetlić poszczególne żądania i rozmiar pamięci, którego zażądali i zostały przyznane, możesz wykonać zapytanie dotyczące dynamicznego sys.dm_exec_query_memory_grants widoku zarządzania. Ten dynamiczny widok zarządzania zawiera informacje dotyczące aktualnie wykonywanych zapytań, a nie informacji historycznych.
Poniższa instrukcja pobiera dane z widoku 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)
Poniżej przedstawiono skrócone przykładowe dane wyjściowe zapytania podczas aktywnego użycia pamięci QE. Większość zapytań ma udzieloną pamięć, jak pokazano na granted_memory_kb used_memory_kb podstawie wartości liczbowych innych niż NULL. Zapytania, które nie otrzymały udzielonych żądań, oczekują na pamięć wykonania i granted_memory_kb = NULL. Ponadto są one umieszczane w kolejce oczekiwania z = queue_id 6. Ich wait_time_ms wskazuje około 37 sekund oczekiwania. Sesja 72 jest następna w kolejce, aby uzyskać dotację wskazaną przez wait_order = 1, podczas gdy sesja 74 następuje po nim wait_order z = 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ń przy użyciu sys.dm_exec_requests
W programie SQL Server istnieje typ oczekiwania, który wskazuje, że zapytanie oczekuje na przyznanie RESOURCE_SEMAPHOREpamięci . Ten typ oczekiwania może być obserwowany sys.dm_exec_requests dla poszczególnych żądań. Ten ostatni dynamiczny widok zarządzania to najlepszy punkt wyjścia do określenia, które zapytania są ofiarami niewystarczającej ilości pamięci. Możesz również obserwować RESOURCE_SEMAPHORE oczekiwanie w sys.dm_os_wait_stats jako zagregowane punkty danych na poziomie programu SQL Server. Ten typ oczekiwania jest wyświetlany, gdy nie można udzielić żądania pamięci zapytania z powodu innych współbieżnych zapytań korzystających z pamięci. Duża liczba żądań oczekujących i długich czasów oczekiwania wskazuje 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 przyznanie pamięci jest skończony. Po nadmiernym oczekiwaniu (na przykład ponad 20 minut) program SQL Server zwraca błąd 8645 i zgłasza błąd 8645"Wystąpił limit czasu podczas oczekiwania na wykonanie zapytania przez zasoby pamięci. Uruchom ponownie zapytanie". Wartość limitu czasu ustawiona na poziomie timeout_sec serwera może zostać wyświetlona w sys.dm_exec_query_memory_grantspliku . Wartość limitu czasu może się nieznacznie różnić w zależności od wersji programu SQL Server.
Korzystając z programu sys.dm_exec_requests, można sprawdzić, które zapytania otrzymały pamięć i rozmiar tego grantu. Ponadto możesz określić, które zapytania oczekują obecnie na przyznanie pamięci, wyszukując RESOURCE_SEMAPHORE typ oczekiwania. Oto zapytanie, które pokazuje zarówno przyznane, 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 oczekują na dotacje. Kolumna granted_query_memory raportuje rozmiar na 8 KB stron. 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ń przy użyciu sys.dm_exec_query_stats
Jeśli problem z udzielaniem pamięci nie występuje w tej chwili, ale chcesz zidentyfikować obraźliwe zapytania, możesz przyjrzeć się historycznym danym zapytań za pośrednictwem metody sys.dm_exec_query_stats. Okres istnienia danych jest powiązany z planem zapytania każdego zapytania. Gdy plan zostanie usunięty z pamięci podręcznej planu, odpowiednie wiersze zostaną wyeliminowane z tego widoku. Innymi słowy, dynamiczny widok zarządzania przechowuje statystyki w pamięci, które nie są zachowywane po ponownym uruchomieniu programu SQL Server lub po presji pamięci powoduje wydanie pamięci podręcznej planu. Oznacza to, że można znaleźć tutaj cenne informacje, szczególnie w przypadku zagregowanych statystyk zapytań. Ktoś mógł ostatnio zgłosić, że widzi duże przydziały pamięci z zapytań, ale gdy spojrzysz na obciążenie serwera, możesz wykryć, że problem zniknął. W takiej sytuacji może dostarczyć szczegółowych informacji, sys.dm_exec_query_stats których nie mogą wykonywać inne maszyny DVM. Oto przykładowe zapytanie, które może pomóc w znalezieniu 20 najlepszych instrukcji, które zużywały największą ilość 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 oddzielnym wierszem 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 z maksymalnym rozmiarem dotacji 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 zaawansowane szczegółowe informacje można uzyskać, przeglądając zapytania zagregowane przez query_hashelement . W tym przykładzie pokazano, jak znaleźć średnie, maksymalne i minimalne rozmiary 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 zawiera przykład struktury zapytania, która pasuje do skrótu zapytania, ale powinna być odczytywana bez względu na określone wartości w instrukcji . Jeśli na przykład instrukcja zawiera WHERE Id = 5, możesz przeczytać ją w bardziej ogólnym formularzu: 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) przy użyciu sys.query_store_runtime_stats
Jeśli masz włączony magazyn zapytań, możesz skorzystać z utrwalonej statystyki historycznej. W przeciwieństwie do danych z sys.dm_exec_query_statsprogramu te statystyki przetrwają ponowne uruchomienie programu SQL Server lub wykorzystanie pamięci, ponieważ są one przechowywane w bazie danych. QDS ma również limity rozmiaru i zasady przechowywania. Aby uzyskać więcej informacji, zobacz Ustawianie optymalnego trybu przechwytywania magazynu zapytań i Przechowywanie najbardziej odpowiednich danych w sekcjach Magazyn zapytań w sekcji Najlepsze rozwiązania dotyczące zarządzania magazynem zapytań.
Zidentyfikuj, czy bazy danych mają 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)Te zasady są takie same jak
sys.dm_exec_query_statsw przypadku instrukcji . Zobaczysz zagregowane statystyki dla instrukcji. Jednak jedną różnicą jest to, że w przypadku QDS patrzysz tylko na zapytania w zakresie tej bazy danych, a nie na cały program SQL Server. Dlatego może 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 spornych dotacji 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 on bardziej szczegółowy przegląd sesji i ich dotacji 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_semaphoresprogram .
Uwaga
To zapytanie zwróci dwa wiersze na sesję z powodu użycia sys.dm_exec_query_resource_semaphores (jeden wiersz dla zwykłego semafora zasobów, 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 LOOP JOIN jest używana w tym zapytaniu diagnostycznym, aby uniknąć udzielenia pamięci przez samo zapytanie i nie ORDER BY jest używana żadna klauzula. Jeśli zapytanie diagnostyczne kończy się oczekiwaniem na przyznanie sobie, jego cel diagnozowania dotacji na pamięć zostanie pokonany. Wskazówka LOOP JOIN może potencjalnie spowodować spowolnienie zapytania diagnostycznego, ale w tym przypadku ważniejsze jest uzyskanie wyników diagnostycznych.
Poniżej przedstawiono 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 wyraźnie ilustrują, w jaki sposób zapytanie przesłane przez session_id = 60 pomyślnie otrzymało żądaną 9 MB pamięci, ale do pomyślnego uruchomienia wykonywania zapytania wymagane było tylko 7 MB. W końcu zapytanie używało tylko 1 MB z 9 MB odebranych z serwera. Dane wyjściowe pokazują również, że sesje 75 i 86 oczekują na przyznanie pamięci, w związku z RESOURCE_SEMAPHORE wait_typetym . Czas oczekiwania wynosi ponad 1300 sekund (21 minut), a ich granted_memory_mb wartość to NULL.
To zapytanie diagnostyczne jest przykładem, więc możesz go 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 program Microsoft SQL Server.
Narzędzia diagnostyczne
Istnieją narzędzia diagnostyczne używane przez pomoc techniczną programu Microsoft SQL Server do zbierania dzienników i wydajniejszego rozwiązywania problemów. Programy SQL LogScout i Pssdiag Configuration Manager (wraz z narzędziem 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 uruchomisz program SQL LogScout z scenariuszami LightPerf, GeneralPerf lub DetailedPerf, narzędzie zbiera niezbędne dzienniki. Następnie możesz ręcznie zbadać YourServer_PerfStats.out i wyszukać -- dm_exec_query_resource_semaphores -- i -- dm_exec_query_memory_grants -- dane wyjściowe. Lub, zamiast badania ręcznego, można użyć narzędzia SQL Nexus do zaimportowania danych wyjściowych pochodzących z programu SQL LogScout lub PSSDIAG do bazy danych programu SQL Server. Narzędzie 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ęć. Usługi SQL LogScout i PSSDIAG zbierają również dzienniki narzędzia Perfmon w postaci . Pliki BLG, które mogą służyć do przeglądania liczników wydajności opisanych w sekcji liczników monitor wydajności.
Dlaczego pamięć jest ważna dla dewelopera lub administratora bazy danych
W oparciu o środowisko pomocy technicznej firmy Microsoft problemy z udzielaniem pamięci są zwykle jednymi z najczęstszych problemów związanych z pamięcią. Aplikacje często wykonują pozornie proste zapytania, które mogą powodować problemy z wydajnością w programie SQL Server z powodu ogromnego sortowania lub operacji skrótu. Takie zapytania nie tylko zużywają dużo pamięci programu SQL Server, ale także powodują, że inne zapytania oczekują na dostępność pamięci, co powoduje wąskie gardło wydajności.
Korzystając z narzędzi opisanych tutaj (dynamicznych widoków zarządzania, liczników wydajności i rzeczywistych planów zapytań), możesz określić, które zapytania są odbiorcami dużych dotacji. Następnie możesz dostosować lub ponownie napisać te zapytania, aby rozwiązać lub zmniejszyć użycie pamięci obszaru roboczego.
Co deweloper może zrobić w przypadku operacji sortowania i skrótu
Po zidentyfikowaniu określonych zapytań, które zużywają dużą ilość pamięci rezerwacji zapytań, możesz wykonać kroki w celu zmniejszenia przydziału pamięci przez przeprojektowanie tych zapytań.
Co powoduje operacje sortowania i skrótu w zapytaniach
Pierwszym krokiem jest uświadomienie sobie, jakie operacje w zapytaniu mogą prowadzić do przyznania pamięci.
Powody, dla których zapytanie używa operatora SORT:
Funkcja ORDER BY (T-SQL) prowadzi do sortowania wierszy przed przesyłaniem strumieniowym w wyniku końcowym.
Funkcja GROUP BY (T-SQL) może wprowadzać operator sortowania w planie zapytania przed grupowaniem, jeśli indeks bazowy nie jest obecny, aby uporządkować pogrupowane kolumny.
Funkcja DISTINCT (T-SQL) zachowuje się podobnie do
GROUP BY. Aby zidentyfikować odrębne wiersze, wyniki pośrednie są uporządkowane, a następnie usuwane są duplikaty. Optymalizator używa operatora przed tym operatoremSort, jeśli dane nie zostały jeszcze posortowane z powodu uporządkowanego wyszukiwania lub skanowania indeksu.Operator Scalanie sprzężenia, po wybraniu przez optymalizator zapytań, wymaga sortowania obu sprzężonych danych wejściowych. Program SQL Server może wyzwolić sortowanie, jeśli indeks klastrowany nie jest dostępny w kolumnie sprzężenia w jednej z tabel.
Powody, dla których zapytanie używa operatora planu zapytania HASH:
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 program SQL Server ma wybór między trzema operatorami fizycznymi,
Nested Loop,Merge JoiniHash Join. Jeśli program SQL Server kończy wybieranie sprzężenia skrótu, wymaga pamięci QE, aby wyniki pośrednie zostały zapisane i przetworzone. Zazwyczaj brak dobrych indeksów może prowadzić do tego najbardziej kosztownego operatora sprzężenia zasobów,Hash Join. Aby zbadać plan zapytania w celu zidentyfikowaniaHash Match, zobacz Dokumentację operatorów logicznych i fizycznych.DISTINCT (T-SQL):
Hash AggregateOperator może służyć do wyeliminowania duplikatów w zestawie wierszy. Aby wyszukać elementHash Match(Aggregate) w planie zapytania, zobacz Dokumentacja operatorów logicznych i fizycznych.UNION (T-SQL): Jest to podobne do
DISTINCT. Element możeHash Aggregatesłużyć do usuwania duplikatów dla tego operatora.SUM/AVG/MAX/MIN (T-SQL): Dowolna operacja agregacji może być potencjalnie wykonywana
Hash Aggregatejako . Aby wyszukać elementHash Match(Aggregate) w planie zapytania, zobacz Dokumentacja operatorów logicznych i fizycznych.
Znajomość tych typowych przyczyn może pomóc w wyeliminowaniu, jak najwięcej, dużych żądań udzielania pamięci przychodzących do programu SQL Server.
Sposoby zmniejszania operacji sortowania i skrótu lub rozmiaru dotacji
- Zachowaj aktualność 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ń. Program SQL Server określa, jaki rozmiar ma żądać udzielenia pamięci na podstawie statystyk. Nieaktualne statystyki mogą spowodować nadmierne lub niedoszacowanie wniosku o przyznanie dotacji, co prowadzi do niepotrzebnego wysokiego wniosku o przyznanie dotacji lub rozlania wyników na dysk, odpowiednio. Upewnij się, że statystyki automatycznej aktualizacji są włączone w bazach danych i/lub zachowaj statyczne aktualizacje za pomocą funkcji UPDATE STATISTICS lub sp_updatestats.
- Zmniejsz liczbę wierszy pochodzących z tabel. Jeśli używasz bardziej restrykcyjnego filtru WHERE lub JOIN i zmniejszysz liczbę wierszy, kolejna sortowanie w planie zapytania będzie kolejna lub zagregowana mniejszy zestaw wyników. Mniejszy zestaw wyników pośrednich wymaga mniej pamięci zestawu roboczego. Jest to ogólna zasada, że deweloperzy mogą śledzić nie tylko zapisywanie pamięci zestawu roboczego, ale także w celu zmniejszenia procesora CPU i operacji we/wy (ten krok nie zawsze jest możliwy). Jeśli już istnieją dobrze napisane i wydajne pod względem zasobów zapytania, te wytyczne zostały spełnione.
- Tworzenie indeksów w kolumnach sprzężenia w celu pomocy w scalaniu sprzężeń. Operacje pośrednie w planie zapytania mają wpływ na indeksy w tabeli bazowej. Jeśli na przykład tabela nie ma indeksu w kolumnie sprzężenia, a sprzężenie scalania jest najbardziej opłacalnym operatorem sprzężenia, wszystkie wiersze z tej tabeli muszą zostać posortowane przed wykonaniem sprzężenia. Jeśli zamiast tego indeks istnieje w kolumnie, można wyeliminować operację sortowania.
- Utwórz indeksy, aby uniknąć operacji skrótu. Często podstawowe dostrajanie zapytań zaczyna się od sprawdzania, czy zapytania mają odpowiednie indeksy, aby ułatwić im zmniejszenie liczby odczytów i zminimalizowanie lub wyeliminowanie dużych operacji sortowania lub skrótów tam, gdzie 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 nieklastrowanych z brakującymi sugestiami indeksu.
- Użyj indeksów COLUMNSTORE, jeśli jest to odpowiednie dla zapytań agregacji, które używają .
GROUP BYZapytania 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 wykonać pracę. Jeśli indeks nie jest dostępny, który dostarcza uporządkowane wyniki, sortowanie jest automatycznie wprowadzane w planie zapytania. Bardzo duży wynik może prowadzić do kosztownego przyznania pamięci. - Usuń element
ORDER BY, jeśli go nie potrzebujesz. W przypadkach, gdy wyniki są przesyłane strumieniowo do aplikacji, która sortuje wyniki w sposób własny lub umożliwia użytkownikowi modyfikowanie kolejności wyświetlanych danych, nie trzeba wykonywać sortowania po stronie programu SQL Server. Wystarczy przesłać strumieniowo dane do aplikacji w kolejności, w jakiej serwer je generuje i pozwolić użytkownikowi końcowemu posortować je samodzielnie. 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żmy, choć ostrożnie, użycie wskazówki sprzężenia PĘTLI, gdy sprzężenia istnieją w zapytaniu T-SQL. Ta technika może uniknąć sprzężeń skrótów lub scalania korzystających z przydziałów pamięci. 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ężenia zagnieżdżonej pętli mogą nawet nie być opcją. W takim przypadku program SQL Server może zakończyć się niepowodzeniem z powodu błędu MSSQLSERVER_8622" "Procesor zapytań nie może utworzyć planu zapytania z powodu wskazówek zdefiniowanych w tym zapytaniu".
Wskazówka dotycząca udzielania pamięci zapytań
Od programu SQL Server 2012 SP3 istnieje wskazówka dotycząca zapytania, która umożliwia kontrolowanie rozmiaru przydziału pamięci na zapytanie. Oto przykład sposobu 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 konserwatywnych wartości, zwłaszcza w przypadkach, w których oczekuje się, że wiele wystąpień zapytania będzie wykonywanych jednocześnie. Upewnij się, że obciążenie jest testem obciążeniowym, aby dopasować je do środowiska produkcyjnego i określić, jakie wartości mają być używane.
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ń, które powodują przyznanie dużej ilości pamięci, można ograniczyć pamięć używaną przez sesje lub aplikacje. Warto wspomnieć, że default grupa obciążeń umożliwia zapytaniu uzyskanie do 25% pamięci, którą można udzielić w wystąpieniu programu SQL Server. Aby uzyskać więcej informacji, zobacz Resource Governor Resource Pools (Pule zasobów zarządcy zasobów) i CREATE WORKLOAD GROUP (TWORZENIE GRUPY OBCIĄŻEŃ).
Adaptacyjne przetwarzanie zapytań i przekazywanie opinii o pamięci
Program SQL Server 2017 wprowadził funkcję przekazywania opinii o udzielaniu pamięci. Umożliwia on aparatowi wykonywania zapytań dostosowanie udzielenia do zapytania na podstawie poprzedniej historii. Celem jest zmniejszenie rozmiaru dotacji, 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:
- Przekazywanie opinii o pamięci trybu wsadowego w programie SQL Server 2017
- Przekazywanie opinii o pamięci w trybie wiersza w programie SQL Server 2019
- Udzielanie informacji zwrotnych dotyczących trwałości dysku w pamięci przy użyciu przydziału magazynu zapytań i percentylu w programie SQL Server 2022
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ń dotacji. W przypadku tej funkcji, szczególnie w programie SQL Server 2019 i nowszych wersjach, w których jest dostępne przetwarzanie adaptacyjne w trybie wiersza, możesz nawet nie 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 użycie pamięci QE, zastosuj opisane wcześniej kroki w celu ponownego zapisywania zapytań.
Zwiększanie ilości pamięci programu SQL Server lub systemu operacyjnego
Po wykonaniu kroków w celu zmniejszenia niepotrzebnych przydziałów pamięci dla zapytań, jeśli nadal występują problemy z małą ilością pamięci, obciążenie prawdopodobnie wymaga więcej pamięci. W związku z tym rozważ zwiększenie pamięci dla programu SQL Server przy użyciu max server memory ustawienia, jeśli w systemie jest wystarczająca ilość pamięci fizycznej. Postępuj zgodnie z zaleceniami dotyczącymi opuszczania 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 ma wystarczającej ilości pamięci, rozważ dodanie fizycznej pamięci RAM lub jeśli jest to maszyna wirtualna, zwiększ dedykowaną pamięć RAM dla maszyny wirtualnej.
Przydzielanie pamięci wewnętrznych
Aby dowiedzieć się więcej o niektórych elementach wewnętrznych dotyczących pamięci wykonywania zapytań, zobacz wpis w blogu Opis udzielania pamięci programu SQL Server.
Jak utworzyć scenariusz wydajności z dużym użyciem przydziału pamięci
Na koniec poniższy przykład ilustruje sposób symulowania dużego użycia pamięci wykonywania zapytań i wprowadzenia 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 lepiej ją nauczyć.
Na serwerze testowym zainstaluj narzędzia RML i program SQL Server.
Użyj aplikacji klienckiej, takiej jak SQL Server Management Studio, aby obniżyć ustawienie maksymalnej pamięci serwera programu 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 testowego programu SQL Server. 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 udzielaniem pamięci.
Podsumowanie sposobów radzenia sobie z dużymi przydziałami pamięci
- Ponowne zapisywanie zapytań.
- Aktualizuj statystyki i regularnie je aktualizuj.
- Utwórz odpowiednie indeksy dla zidentyfikowanych zapytań lub zapytań. Indeksy mogą zmniejszyć dużą liczbę przetworzonych wierszy, zmieniając
JOINalgorytmy i zmniejszając rozmiar dotacji lub całkowicie je eliminując. OPTIONUżyj wskazówki (min_grant_percent = XX, max_grant_percent = XX).- Użyj zarządcy zasobów.
- Programy SQL Server 2017 i 2019 używają adaptacyjnego przetwarzania zapytań, dzięki czemu mechanizm przekazywania opinii o pamięci umożliwia dynamiczne dostosowywanie rozmiaru przydziału pamięci w czasie wykonywania. Ta funkcja może zapobiec problemom z udzielaniem pamięci w pierwszej kolejności.
- Zwiększ ilość pamięci programu SQL Server lub systemu operacyjnego.