Behebung von Problemen mit langsamer Leistung oder geringem Speicherplatz, die durch Speicherzuweisungen in SQL Server verursacht werden
Was sind Speichererteilungen?
Speichererteilungen, die auch als QE-Reservierungen (Query Execution, Query Execution Memory, Workspace Memory und Memory Reservations) bezeichnet werden, beschreiben die Verwendung des Speichers zur Abfrageausführungszeit. SQL Server weist diesen Speicher während der Abfrageausführung für einen oder mehrere der folgenden Zwecke zu:
- Sortiervorgänge
- Hashvorgänge
- Massenkopievorgänge (kein häufiges Problem)
- Indexerstellung, einschließlich Einfügen in COLUMNSTORE-Indizes, da Hashwörterbücher/Tabellen zur Laufzeit für die Indexerstellung verwendet werden (kein häufiges Problem)
Zur Bereitstellung eines kontextabhängigen Kontexts kann eine Abfrage je nach Bedarf Speicher von unterschiedlichen Speicherzuordnungen oder Sachbearbeitern anfordern. Wenn eine Abfrage z. B. zunächst analysiert und kompiliert wird, verbraucht sie Kompilierungsspeicher. Sobald die Abfrage kompiliert wurde, wird dieser Speicher freigegeben, und der resultierende Abfrageplan wird im Cachespeicher des Plans gespeichert. Sobald ein Plan zwischengespeichert wurde, ist die Abfrage für die Ausführung bereit. Wenn die Abfrage Sortiervorgänge, Hash-Abgleichsvorgänge (JOIN- oder Aggregatvorgänge) oder Einfügungen in einen COLUMNSTORE-Indizes durchführt, verwendet sie Speicher aus dem Abfrageausführungsverteiler. Zunächst fragt die Abfrage nach diesem Ausführungsspeicher und später, wenn dieser Speicher gewährt wird, verwendet die Abfrage den gesamten oder einen Teil des Speichers für Sortierergebnisse oder Hash-Buckets. Dieser während der Abfrageausführung zugewiesene Speicher wird als Speichererteilung bezeichnet. Wie Sie sich vorstellen können, wird die Speichererteilung nach Abschluss des Abfrageausführungsvorgangs wieder auf SQL Server freigegeben, um sie für andere Aufgaben zu verwenden. Daher sind Speicherzuteilungen vorübergehend, können aber dennoch lange dauern. Wenn beispielsweise eine Abfrageausführung einen Sortiervorgang für ein sehr großes Rowset im Arbeitsspeicher ausführt, kann die Sortierung viele Sekunden oder Minuten dauern, und der gewährte Arbeitsspeicher wird für die Lebensdauer der Abfrage verwendet.
Beispiel für eine Abfrage mit einer Speichererteilung
Nachfolgend sehen Sie ein Beispiel für eine Abfrage, die den Ausführungsspeicher und den Abfrageplan verwendet, der die Gewährung zeigt:
SELECT *
FROM sys.messages
ORDER BY message_id
Diese Abfrage wählt ein Rowset mit über 300.000 Zeilen aus und sortiert sie. Der Sortiervorgang führt zu einer Speichererteilungsanforderung. Wenn Sie diese Abfrage in SSMS ausführen, können Sie dessen Abfrageplan anzeigen. Wenn Sie den am weitesten links angeordneten SELECT Operator des Abfrageplans auswählen, können Sie die Informationen zur Speichererteilung für die Abfrage anzeigen (drücken Sie F4, um Eigenschaften anzuzeigen):
Wenn Sie im Abfrageplan mit der rechten Maustaste auf den Leerraum klicken, können Sie auch "Ausführungsplan-XML anzeigen" auswählen ... und ein XML-Element suchen, das dieselben Informationen zur Speichererteilung anzeigt.
<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" />
Hier benötigen mehrere Begriffe Erläuterungen. Eine Abfrage möchte möglicherweise einen bestimmten Ausführungsspeicher (DesiredMemory) und würde diesen Betrag häufig anfordern (RequestedMemory). Zur Laufzeit gewährt SQL Server je nach Verfügbarkeit (GrantedMemory) den gesamten oder einen Teil des angeforderten Arbeitsspeichers. Am Ende kann die Abfrage mehr oder weniger des anfänglich angeforderten Speichers (MaxUsedMemory) verwenden. Wenn der Abfrageoptimierer die erforderliche Arbeitsspeichermenge überschätzt hat, wird weniger als die angeforderte Größe verwendet. Dieser Speicher wird jedoch verschwendet, da er von einer anderen Anforderung verwendet worden sein könnte. Wenn der Optimierer andererseits die benötigte Größe des Arbeitsspeichers unterschätzt hat, werden die überschüssigen Zeilen möglicherweise auf den Datenträger übergelaufen, um die Arbeit zur Ausführungszeit zu erledigen. Anstatt mehr Arbeitsspeicher als die anfänglich angeforderte Größe zu zuordnen, überträgt SQL Server die zusätzlichen Zeilen auf den Datenträger und verwendet ihn als temporären Arbeitsbereich. Weitere Informationen finden Sie unter "Arbeitsdateien und Arbeitstabellen" in Überlegungen zur Speichererteilung.
Terminologie
Sehen wir uns die verschiedenen Begriffe an, die bei diesem Speicherverbraucher auftreten können. Auch hier werden alle diese Konzepte beschrieben, die sich auf dieselben Speicherzuweisungen beziehen.
Abfrageausführungsspeicher (QE-Speicher): Dieser Begriff wird verwendet, um die Tatsache hervorzuheben, dass sortier- oder Hashspeicher während der Ausführung einer Abfrage verwendet wird. Häufig ist der QE-Speicher der größte Speicherverbraucher während der Lebensdauer einer Abfrage.
QE-Reservierungen (Query Execution) oder Speicherreservierungen: Wenn eine Abfrage Speicher für Sortier- oder Hashvorgänge benötigt, stellt sie eine Reservierungsanforderung für den Arbeitsspeicher vor. Diese Reservierungsanforderung wird zur Kompilierungszeit basierend auf der geschätzten Kardinalität berechnet. Wenn die Abfrage ausgeführt wird, gewährt SQL Server diese Anforderung je nach Speicherverfügbarkeit teilweise oder vollständig. Am Ende kann die Abfrage einen Prozentsatz des gewährten Arbeitsspeichers verwenden. Es gibt einen Speicherkaufmann (Buchhalter des Speichers) mit dem Namen "MEMORYCLERK_SQLQERESERVATIONS", der diese Speicherzuweisungen nachverfolgt (checken Sie DBCC MEMORYSTATUS oder sys.dm_os_memory_clerks).
Memory Grants: Wenn SQL Server dem angeforderten Speicher eine ausgeführte Abfrage zugibt, wird gesagt, dass eine Speichererteilung aufgetreten ist. Es gibt einige Leistungsindikatoren, die den Begriff "Grant" verwenden. Diese Leistungsindikatoren und
Memory Grants Pendingdie Anzahl der SpeichererteilungenMemory Grants Outstandingwerden erfüllt oder warten angezeigt. Sie berücksichtigen nicht die Größe der Speicherzuteilung. Eine einzige Abfrage könnte beispielsweise 4 GB Arbeitsspeicher verbraucht haben, um eine Sortierung durchzuführen, die jedoch nicht in einem dieser Leistungsindikatoren widerzuspiegeln ist.Arbeitsbereichsspeicher ist ein weiterer Begriff, der denselben Arbeitsspeicher beschreibt. Häufig wird dieser Begriff im Perfmon-Zähler
Granted Workspace Memory (KB)angezeigt, der den Gesamtspeicher wiedergibt, der zurzeit für Sortier-, Hash-, Massenkopie- und Indexerstellungsvorgänge verwendet wird, ausgedrückt in KB. DerMaximum Workspace Memory (KB), ein weiterer Leistungsindikator, stellt die maximale Anzahl von Arbeitsbereichsspeichern für alle Anforderungen dar, die solche Hash-, Sortier-, Massenkopie- und Indexerstellungsvorgänge ausführen müssen. Der Begriff "Arbeitsbereichsspeicher" ist selten außerhalb dieser beiden Leistungsindikatoren aufgetreten.
Leistungsauswirkungen einer großen QE-Speicherauslastung
In den meisten Fällen schlägt die Anforderung fehl, wenn ein Thread Speicher innerhalb von SQL Server anfordert, um etwas zu erledigen, und der Speicher nicht verfügbar ist, schlägt die Anforderung mit einem Nicht-Arbeitsspeicherfehler fehl. Es gibt jedoch einige Ausnahmeszenarien, in denen der Thread nicht fehlschlägt, aber wartet, bis der Arbeitsspeicher verfügbar ist. Eines dieser Szenarien ist Speichererteilungen, und der andere ist der Speicher für die Abfragekompilierung. SQL Server verwendet ein Threadsynchronisierungsobjekt namens Semaphor , um nachzuverfolgen, wie viel Arbeitsspeicher für die Abfrageausführung gewährt wurde. Wenn SQL Server nicht mehr im vordefinierten QE-Arbeitsbereich ausgeführt wird, anstatt die Abfrage mit einem Nichtspeicherfehler zu verfehlen, wird die Abfrage warten. Da der Arbeitsbereichsspeicher einen erheblichen Prozentsatz des gesamten SQL Server-Arbeitsspeichers belegen darf, hat das Warten auf den Arbeitsspeicher in diesem Bereich schwerwiegende Auswirkungen auf die Leistung. Eine große Anzahl gleichzeitiger Abfragen hat den Ausführungsspeicher angefordert, und zusammen haben sie den QE-Speicherpool erschöpft, oder einige gleichzeitige Abfragen haben jeweils sehr große Zuschüsse angefordert. Auf beide Weise können die resultierenden Leistungsprobleme die folgenden Symptome haben:
- Daten- und Indexseiten aus einem Puffercache wurden wahrscheinlich geleert, um Platz für die großen Speichererteilungsanforderungen zu schaffen. Dies bedeutet, dass Seitenlesevorgänge, die aus Abfrageanforderungen stammen, vom Datenträger erfüllt werden müssen (ein deutlich langsamerer Vorgang).
- Anforderungen für andere Speicherzuordnungen können fehlschlagen, da die Ressource mit Sortier-, Hash- oder Indexerstellungsvorgängen verknüpft ist.
- Anforderungen, die Ausführungsspeicher benötigen, warten darauf, dass die Ressource verfügbar ist und eine lange Zeit in Anspruch nimmt. Anders ausgedrückt: Für den Endbenutzer sind diese Abfragen langsam.
Wenn Sie daher die Wartezeiten auf den Abfrageausführungsspeicher in Perfmon, dynamischen Verwaltungsansichten (DMVs) oder DBCC MEMORYSTATUSbeobachten, müssen Sie dieses Problem beheben, insbesondere, wenn das Problem häufig auftritt. Weitere Informationen finden Sie unter What can a developer do about sort and hash operations.
Identifizieren von Wartezeiten für den Abfrageausführungsspeicher
Es gibt mehrere Möglichkeiten zum Ermitteln von Wartezeiten für QE-Reservierungen. Wählen Sie diejenigen aus, die Ihnen am besten dienen, um das größere Bild auf Serverebene zu sehen. Einige dieser Tools stehen Ihnen möglicherweise nicht zur Verfügung (beispielsweise ist Perfmon in Azure SQL-Datenbank nicht verfügbar). Nachdem Sie das Problem identifiziert haben, müssen Sie einen Drilldown auf der einzelnen Abfrageebene ausführen, um zu sehen, welche Abfragen Optimierungen oder Neuschreibungen benötigen.
Verwenden Sie auf Serverebene die folgenden Methoden:
- Ressourcensemaphor DMV sys.dm_exec_query_resource_semaphores Weitere Informationen finden Sie unter sys.dm_exec_query_resource_semaphores.
- Leistungsmonitor Zähler Weitere Informationen finden Sie unter SQL Server Memory Manager-Objekt.
- DBCC MEMORYSTATUS Weitere Informationen finden Sie unter DBCC MEMORYSTATUS.
- Speicherkaufleute DMV sys.dm_os_memory_clerks Weitere Informationen finden Sie unter sys.dm_os_memory_clerks.
- Identifizieren von Speichererteilungen mithilfe von erweiterten Ereignissen (XEvents) Weitere Informationen finden Sie unter "Erweiterte Ereignisse (XEvents)".
Verwenden Sie auf der einzelnen Abfrageebene die folgenden Methoden:
- Identifizieren Sie bestimmte Abfragen mit sys.dm_exec_query_memory_grants: Derzeit ausgeführte Abfragen. Weitere Informationen finden Sie unter sys.dm_exec_query_memory_grants.
- Identifizieren Sie bestimmte Abfragen mit sys.dm_exec_requests: Derzeit ausgeführte Abfragen. Weitere Informationen finden Sie unter sys.dm_exec_requests.
- Identifizieren Sie bestimmte Abfragen mit sys.dm_exec_query_stats: Historische Statistiken zu Abfragen. Weitere Informationen finden Sie unter sys.dm_exec_query_stats.
- Identifizieren Sie bestimmte Abfragen mithilfe von Abfragespeicher (QDS) mit sys.query_store_runtime_stats: Historische Statistiken zu Abfragen mit QDS. Weitere Informationen finden Sie unter sys.query_store_runtime_stats.
Aggregierte Arbeitsspeicherauslastungsstatistiken
Ressourcensemaphor DMV-sys.dm_exec_query_resource_semaphores
Dieser DMV bricht den Speicher der Abfragereservierung nach Ressourcenpool (intern, standard und vom Benutzer erstellt) und resource_semaphore (reguläre und kleine Abfrageanforderungen) auf. Eine nützliche Abfrage kann sein:
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
Die folgende Beispielausgabe zeigt, dass rund 900 MB Abfrageausführungsspeicher von 22 Anforderungen verwendet wird und 3 weitere warten. Dies erfolgt im Standardpool (pool_id = 2) und dem regulären Abfragesemaphor (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)
Leistungsüberwachungsindikatoren
Ähnliche Informationen stehen über Leistungsmonitor Zähler zur Verfügung, in denen Sie die aktuell gewährten Anforderungen (Memory Grants Outstanding), die Wartende Erteilungsanforderungen (Memory Grants Pending) und die Menge an Arbeitsspeicher, die von Speicherzuschüssen (Granted Workspace Memory (KB)) verwendet werden, beobachten können. In der folgenden Abbildung sind die ausstehenden Zuschüsse 18, die ausstehenden Zuschüsse 2, und der gewährte Arbeitsbereichspeicher beträgt 828.288 KB. Der Memory Grants Pending Perfmon-Zähler mit einem Wert ungleich Null gibt an, dass der Speicher erschöpft wurde.
Weitere Informationen finden Sie unter SQL Server Memory Manager-Objekt.
- SQLServer, Speicher-Manager: Maximaler Arbeitsbereichsspeicher (KB)
- SQLServer, Speicher-Manager: Arbeitsspeicher gewährt ausstehende
- SQLServer, Speicher-Manager: Arbeitsspeicher gewährt ausstehend
- SQLServer, Speicher-Manager: Gewährter Arbeitsbereichsspeicher (KB)
DBCC MEMORYSTATUS
Ein weiterer Ort, an dem Details zum Abfragereservierungsspeicher DBCC MEMORYSTATUS angezeigt werden können (Abschnitt "Abfragespeicherobjekte"). Sie können die Query Memory Objects (default) Ausgabe für Benutzerabfragen anzeigen. Wenn Sie den Ressourcengouverneur mit einem Ressourcenpool namens PoolAdmin aktiviert haben, können Sie sich beispielsweise beide und beides Query Memory Objects (default) Query Memory Objects (PoolAdmin)ansehen.
Hier ist eine Beispielausgabe eines Systems, in dem 18 Anforderungen dem Abfrageausführungsspeicher gewährt wurden, und 2 Anforderungen warten auf Arbeitsspeicher. Der verfügbare Leistungsindikator ist null, was angibt, dass kein Arbeitsbereichsspeicher verfügbar ist. In dieser Tatsache werden die beiden Warteanforderungen erläutert. Dies Wait Time zeigt die verstrichene Zeit in Millisekunden an, da eine Anforderung in die Warteschleife gesetzt wurde. Weitere Informationen zu diesen Leistungsindikatoren finden Sie unter Abfragespeicherobjekte.
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 zeigt außerdem Informationen zum Speicherkaufmann an, der den Speicher für die Abfrageausführung nachverfolgt. Die folgende Ausgabe zeigt, dass die für die Abfrageausführung (QE) reservierten Seiten 800 MB überschreiten.
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
Speicherkaufmann DMV sys.dm_os_memory_clerks
Wenn Sie mehr von einem tabellarischen Resultset benötigen, anders als der abschnittsbasierteDBCC MEMORYSTATUS, können Sie sys.dm_os_memory_clerks für ähnliche Informationen verwenden. Suchen Sie nach dem MEMORYCLERK_SQLQERESERVATIONS Speicherkaufmann. Die Abfragespeicherobjekte sind in diesem DMV jedoch nicht verfügbar.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Hier ist eine Beispielausgabe:
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Identifizieren von Speichererteilungen mithilfe von erweiterten Ereignissen (XEvents)
Es gibt mehrere erweiterte Ereignisse, die Informationen zur Speichererteilung bereitstellen und es Ihnen ermöglichen, diese Informationen über eine Ablaufverfolgung zu erfassen:
- sqlserver.additional_memory_grant: Tritt auf, wenn eine Abfrage versucht, während der Ausführung mehr Speichererteilung zu erhalten. Fehler beim Abrufen dieser zusätzlichen Speichererteilung kann zu einer Verlangsamung der Abfrage führen.
- sqlserver.query_memory_grant_blocking: Tritt auf, wenn eine Abfrage andere Abfragen blockiert, während sie auf eine Speichererteilung warten.
- sqlserver.query_memory_grant_info_sampling: Tritt am Ende der zufällig abgefragten Abfragen auf, die Informationen zur Speichererteilung bereitstellen (z. B. für Telemetrie).
- sqlserver.query_memory_grant_resource_semaphores: Tritt in fünfminütigen Intervallen für jeden Ressourcenverwaltungsressourcenpool auf.
- sqlserver.query_memory_grant_usage: Tritt am Ende der Abfrageverarbeitung für Abfragen mit Speichererteilungen von mehr als 5 MB auf, um Benutzern informationen zu Ungenauigkeiten der Speichererteilung zu geben.
- sqlserver.query_memory_grants: Tritt in fünfminütigen Intervallen für jede Abfrage mit einer Speichererteilung auf.
Erweiterte Ereignisse zur Speichererteilung
Informationen zu Den Feedbackfeatures für die Speicherverarbeitung finden Sie unter "Feedback zur Speichererteilung".
- sqlserver.memory_grant_feedback_loop_disabled: Tritt auf, wenn die Feedbackschleife für die Speichererteilung deaktiviert ist.
- sqlserver.memory_grant_updated_by_feedback: Tritt auf, wenn die Speichererteilung durch Feedback aktualisiert wird.
Abfrageausführungswarnungen, die sich auf Speichererteilungen beziehen
- sqlserver.execution_warning: Tritt auf, wenn eine T-SQL-Anweisung oder gespeicherte Prozedur mehr als eine Sekunde auf eine Speichererteilung wartet oder wenn der anfängliche Versuch, Arbeitsspeicher abzurufen, fehlschlägt. Verwenden Sie dieses Ereignis in Kombination mit Ereignissen, die Warten identifizieren, um Inhaltsprobleme zu beheben, die sich auf die Leistung auswirken.
- sqlserver.hash_spill_details: Tritt am Ende der Hashverarbeitung auf, wenn nicht genügend Arbeitsspeicher vorhanden ist, um die Buildeingabe einer Hash-Verknüpfung zu verarbeiten. Verwenden Sie dieses Ereignis zusammen mit einem der
query_pre_execution_showplanEreignisse oderquery_post_execution_showplanEreignisse, um zu bestimmen, welcher Vorgang im generierten Plan den Hash-Überlauf verursacht. - sqlserver.hash_warning: Tritt auf, wenn nicht genügend Arbeitsspeicher vorhanden ist, um die Buildeingabe einer Hash-Verknüpfung zu verarbeiten. Dies führt entweder zu einer Hash-Rekursion, wenn die Buildeingabe partitioniert wird, oder ein Hash-Bailout, wenn die Partitionierung der Buildeingabe die maximale Rekursionsebene überschreitet. Verwenden Sie dieses Ereignis zusammen mit einem der
query_pre_execution_showplanEreignisse oderquery_post_execution_showplanEreignisse, um zu bestimmen, welcher Vorgang im generierten Plan die Hashwarnung verursacht. - sqlserver.sort_warning: Tritt auf, wenn der Sortiervorgang für eine ausgeführte Abfrage nicht in den Arbeitsspeicher passt. Dieses Ereignis wird nicht für Sortiervorgänge generiert, die durch die Indexerstellung verursacht werden, nur für Sortiervorgänge in einer Abfrage. (Beispiel: eine
Order Byin einerSelectAnweisung.) Verwenden Sie dieses Ereignis, um Abfragen zu identifizieren, die aufgrund des Sortiervorgangs langsam ausgeführt werden, insbesondere wenn derwarning_type= 2, der angibt, dass mehrere Übergaben über die Daten zum Sortieren erforderlich waren.
Planen der Generierung von Ereignissen, die Informationen zur Speichererteilung enthalten
Der folgende Abfrageplan, der erweiterte Ereignisse generiert, enthält standardmäßig granted_memory_kb und ideal_memory_kb Felder:
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
Gebäude des Spaltenspeicherindexes
Einer der bereiche, die über XEvents abgedeckt werden, ist der Ausführungsspeicher, der beim Erstellen des Spaltenspeichers verwendet wird. Dies ist eine Liste der verfügbaren Ereignisse:
- sqlserver.column_store_index_build_low_memory: Das Speichermodul hat eine geringe Arbeitsspeicherbedingung festgestellt, und die Zeilengruppengröße wurde reduziert. Hier gibt es mehrere interessante Spalten.
- sqlserver.column_store_index_build_memory_trace: Ablaufverfolgungsspeichernutzung während des Indexbuilds.
- sqlserver.column_store_index_build_memory_usage_scale_down: Speichermodul skaliert nach unten.
- sqlserver.column_store_index_memory_estimation: Zeigt das Speicherschätzungsergebnis während des COLUMNSTORE-Zeilengruppenbuilds an.
Identifizieren bestimmter Abfragen
Es gibt zwei Arten von Abfragen, die Sie beim Betrachten der einzelnen Anforderungsebene finden können. Die Abfragen, die eine große Menge an Abfrageausführungsspeicher verbrauchen, und diejenigen, die auf denselben Speicher warten. Die letztere Gruppe kann aus Anfragen mit bescheidenen Anforderungen für Speichererteilungen bestehen, und wenn ja, können Sie ihre Aufmerksamkeit an anderer Stelle konzentrieren. Aber sie könnten auch die Schuldigen sein, wenn sie große Speichergrößen anfordern. Konzentrieren Sie sich darauf, wenn Sie feststellen, dass dies der Fall ist. Es kann vorkommen, dass eine bestimmte Abfrage der Täter ist, aber viele Instanzen davon werden spawniert. Diese Instanzen, die die Speichererteilungen erhalten, führen dazu, dass andere Instanzen derselben Abfrage auf die Erteilung warten. Unabhängig von bestimmten Umständen müssen Sie letztendlich die Abfragen und die Größe des angeforderten Ausführungsspeichers identifizieren.
Identifizieren bestimmter Abfragen mit sys.dm_exec_query_memory_grants
Zum Anzeigen einzelner Anforderungen und der angeforderten Speichergröße können Sie die sys.dm_exec_query_memory_grants dynamische Verwaltungsansicht abfragen. Dieser DMV zeigt Informationen über derzeit ausgeführte Abfragen und keine historischen Informationen an.
Die folgende Anweisung ruft Daten aus dem DMV ab und ruft außerdem den Abfragetext und den Abfrageplan als Ergebnis ab:
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)
Hier ist eine abgekürzte Beispielausgabe der Abfrage während der aktiven QE-Speicherauslastung. Die meisten Abfragen haben ihren Arbeitsspeicher gewährt, wie dargestellt und granted_memory_kb used_memory_kb sind nicht NULL numerische Werte. Die Abfragen, für die ihre Anforderung nicht gewährt wurde, warten auf den Ausführungsspeicher und den granted_memory_kb = NULL. Außerdem werden sie in einer Warteschleife mit einem queue_id = 6 platziert. Sie wait_time_ms deuten auf etwa 37 Sekunden Wartezeit hin. Sitzung 72 steht als nächstes in der Zeile, um eine Finanzhilfe zu erhalten, wie durch wait_order = 1 angegeben, während Sitzung 74 danach mit wait_order = 2 kommt.
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
Identifizieren bestimmter Abfragen mit sys.dm_exec_requests
Es gibt einen Wartetyp in SQL Server, der angibt, dass eine Abfrage auf die Speichererteilung RESOURCE_SEMAPHOREwartet. Sie können diesen Wartetyp sys.dm_exec_requests für einzelne Anforderungen beobachten. Letzterer DMV ist der beste Ausgangspunkt, um zu ermitteln, welche Abfragen Opfer unzureichender Bewilligungsspeicher sind. Sie können die RESOURCE_SEMAPHORE Wartezeit auch in sys.dm_os_wait_stats als aggregierte Datenpunkte auf SQL Server-Ebene beobachten. Dieser Wartetyp wird angezeigt, wenn eine Abfragespeicheranforderung aufgrund anderer gleichzeitiger Abfragen, die den Arbeitsspeicher verwendet haben, nicht gewährt werden kann. Eine hohe Anzahl von Warteanforderungen und lange Wartezeiten deuten auf eine übermäßige Anzahl gleichzeitiger Abfragen mit Ausführungsspeicher oder großen Speicheranforderungsgrößen hin.
Notiz
Die Wartezeit für Speichererteilungen ist endlich. Nach einer übermäßigen Wartezeit (z. B. über 20 Minuten) zeitigt SQL Server die Abfrage und löst Fehler 8645 aus, "Beim Warten auf Speicherressourcen auf die Ausführung der Abfrage ist ein Timeout aufgetreten. Führen Sie die Abfrage erneut aus." Möglicherweise wird der auf Serverebene festgelegte Timeoutwert angezeigt, indem Sie in der Datei sys.dm_exec_query_memory_grantsnachschauentimeout_sec. Der Timeoutwert kann zwischen SQL Server-Versionen geringfügig variieren.
Mit der Verwendung von sys.dm_exec_requests, können Sie sehen, welche Abfragen arbeitsspeicher und die Größe dieser Gewährung gewährt wurden. Außerdem können Sie ermitteln, welche Abfragen derzeit auf eine Speichererteilung warten, indem Sie nach dem RESOURCE_SEMAPHORE Wartetyp suchen. Hier ist eine Abfrage, die Sowohl die gewährten als auch die Warteanforderungen anzeigt:
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'
Eine Beispielausgabe zeigt, dass zwei Anforderungen Arbeitsspeicher erhalten haben, und zwei Dutzend andere warten auf Zuschüsse. Die granted_query_memory Spalte meldet die Größe auf 8 KB Seiten. Beispielsweise bedeutet ein Wert von 34.709 34.709 * 8 KB = 277.672 KB Arbeitsspeicher.
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)
Identifizieren bestimmter Abfragen mit sys.dm_exec_query_stats
Wenn das Problem mit der Speichererteilung derzeit nicht auftritt, Sie aber die beleidigenden Abfragen identifizieren möchten, können Sie sich historische Abfragedaten über sys.dm_exec_query_statsansehen. Die Lebensdauer der Daten ist an den Abfrageplan jeder Abfrage gebunden. Wenn ein Plan aus dem Plancache entfernt wird, werden die entsprechenden Zeilen aus dieser Ansicht entfernt. Mit anderen Worten, der DMV behält Statistiken im Arbeitsspeicher bei, die nach einem SQL Server-Neustart oder nach dem Arbeitsspeicherdruck nicht beibehalten werden, führt zu einer Plancachefreigabe. Das heißt, Sie finden die Informationen hier wertvoll, insbesondere für aggregierte Abfragestatistiken. Möglicherweise hat jemand kürzlich berichtet, dass große Speichererteilungen von Abfragen angezeigt werden, aber wenn Sie die Serverarbeitsauslastung betrachten, können Sie feststellen, dass das Problem nicht mehr besteht. In dieser Situation können die Erkenntnisse bereitgestellt werden, sys.dm_exec_query_stats die andere DVMs nicht haben. Hier ist eine Beispielabfrage, mit der Sie die wichtigsten 20 Anweisungen finden können, die die größten Mengen des Ausführungsspeichers verbraucht haben. Diese Ausgabe zeigt einzelne Anweisungen auch dann an, wenn ihre Abfragestruktur identisch ist. Beispielsweise SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 ist eine separate Zeile von SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (nur der Filter-Prädikatwert variiert). Die Abfrage ruft die obersten 20 Anweisungen mit einer maximalen Grant-Größe ab, die größer als 5 MB ist.
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
Noch leistungsfähigere Einblicke können sie gewinnen, indem Sie die abfragen, die aggregiert werden query_hash. In diesem Beispiel wird veranschaulicht, wie Sie die durchschnittliche, maximale und minimale Erteilungsgrößen für eine Abfrageausweisung in allen Instanzen ermitteln, seit der Abfrageplan zuerst zwischengespeichert wurde.
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)
Die Sample_Statement_Text Spalte zeigt ein Beispiel für die Abfragestruktur, die dem Abfragehash entspricht, aber es sollte ohne Bezug auf bestimmte Werte in der Anweisung gelesen werden. Wenn eine Anweisung beispielsweise enthält WHERE Id = 5, können Sie sie in der generischeren Form lesen: WHERE Id = @any_value.
Hier sehen Sie eine gekürzte Beispielausgabe der Abfrage mit nur ausgewählten Spalten:
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
Identifizieren bestimmter Abfragen mithilfe von Abfragespeicher (QDS) mit sys.query_store_runtime_stats
Wenn Sie Abfragespeicher aktiviert haben, können Sie die beibehaltenen historischen Statistiken nutzen. Im Gegensatz zu Daten aus sys.dm_exec_query_statsdiesen Statistiken überleben diese Statistiken einen SQL Server-Neustart oder arbeitsspeicherdruck, da sie in einer Datenbank gespeichert sind. QDS verfügt auch über Größenbeschränkungen und eine Aufbewahrungsrichtlinie. Weitere Informationen finden Sie im Set the optimal Abfragespeicher Capture Mode and Keep the most relevant data in Abfragespeicher sections in Best practices for managing the Abfragespeicher.
Ermitteln Sie, ob Ihre Datenbanken mithilfe dieser Abfrage Abfragespeicher aktiviert haben:
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Führen Sie die folgende Diagnoseabfrage im Kontext einer bestimmten Datenbank aus, die Sie untersuchen möchten:
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)Die Prinzipien sind hier identisch mit
sys.dm_exec_query_stats; Sie sehen aggregierte Statistiken für die Aussagen. Ein Unterschied besteht jedoch darin, dass Sie bei QDS nur Abfragen im Bereich dieser Datenbank betrachten, nicht den gesamten SQL Server. Möglicherweise müssen Sie also die Datenbank kennen, in der eine bestimmte Speichererteilungsanforderung ausgeführt wurde. Führen Sie andernfalls diese Diagnoseabfrage in mehreren Datenbanken aus, bis Sie die sizierbaren Speichererteilungen finden.Hier ist eine abgekürzte Beispielausgabe:
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
Benutzerdefinierte Diagnoseabfrage
Hier ist eine Abfrage, die Daten aus mehreren Ansichten kombiniert, einschließlich der drei zuvor aufgeführten. Es bietet eine gründlichere Übersicht über die Sitzungen und deren Finanzhilfen über sys.dm_exec_requests und sys.dm_exec_query_memory_grants, zusätzlich zu den statistiken auf Serverebene, die von sys.dm_exec_query_resource_semaphores.
Notiz
Diese Abfrage würde zwei Zeilen pro Sitzung aufgrund der Verwendung von sys.dm_exec_query_resource_semaphores (einer Zeile für das reguläre Ressourcensemaphor und ein weiteres für das Semaphor der Kleinen Abfrageressource) zurückgeben.
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 )
Notiz
Der LOOP JOIN Hinweis wird in dieser Diagnoseabfrage verwendet, um eine Speichererteilung durch die Abfrage selbst zu vermeiden, und es wird keine ORDER BY Klausel verwendet. Wenn die Diagnoseabfrage endet, die auf eine Erteilung selbst wartet, wird der Zweck der Diagnose von Speichererteilungen besiegt. Der LOOP JOIN Hinweis könnte dazu führen, dass die Diagnoseabfrage möglicherweise langsamer ist, aber in diesem Fall ist es wichtiger, die Diagnoseergebnisse zu erhalten.
Hier ist eine gekürzte Beispielausgabe aus dieser Diagnoseabfrage mit nur ausgewählten Spalten.
| 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 |
Die Beispielausgabe veranschaulicht eindeutig, wie eine von session_id = 60 übermittelte Abfrage die angeforderte 9-MB-Speicherzuteilung erhalten hat, aber nur 7 MB erforderlich waren, um die Abfrageausführung erfolgreich zu starten. Am Ende verwendete die Abfrage nur 1 MB der 9 MB, die sie vom Server empfangen hat. Die Ausgabe zeigt auch, dass Sitzungen 75 und 86 auf Speicherzuschüsse warten, also die RESOURCE_SEMAPHORE wait_type. Ihre Wartezeit beträgt über 1.300 Sekunden (21 Minuten), und ihre granted_memory_mb Wartezeit ist NULL.
Diese Diagnoseabfrage ist ein Beispiel, daher können Sie sie auf jede Weise ändern, die Ihren Anforderungen entspricht. Eine Version dieser Abfrage wird auch in Diagnosetools verwendet, die von Microsoft SQL Server unterstützt werden.
Diagnosetools
Es gibt Diagnosetools, die der technische Support von Microsoft SQL Server verwendet, um Protokolle zu sammeln und Probleme effizienter zu beheben. SQL LogFinder und Pssdiag Configuration Manager (zusammen mit SQLDiag) sammeln Ausgaben der zuvor beschriebenen DMVs und Leistungsmonitor Indikatoren, die Ihnen helfen können, Probleme mit der Speichererteilung zu diagnostizieren.
Wenn Sie SQL LogFinder mit LightPerf-, GeneralPerf- oder DetailedPerf-Szenarien ausführen, sammelt das Tool die erforderlichen Protokolle. Anschließend können Sie die YourServer_PerfStats.out manuell untersuchen und nach ausgaben suchen -- dm_exec_query_resource_semaphores -- und -- dm_exec_query_memory_grants -- ausgeben. Oder statt manueller Untersuchung können Sie SQL Nexus verwenden, um die Ausgabe aus SQL LogFinder oder PSSDIAG in eine SQL Server-Datenbank zu importieren. SQL Nexus erstellt zwei Tabellen und tbl_dm_exec_query_memory_grantsenthält die informationen, tbl_dm_exec_query_resource_semaphores die zum Diagnostizieren von Speichererteilungen erforderlich sind. SQL LogFinder und PSSDIAG sammeln auch Perfmon-Protokolle in Form von . BLG-Dateien, die verwendet werden können, um die im Abschnitt Leistungsmonitor Leistungsindikatoren beschriebenen Leistungsindikatoren zu überprüfen.
Warum sind Speichererteilungen für Entwickler oder DBA wichtig
Basierend auf der Microsoft-Supporterfahrung sind Speichererteilungsprobleme in der Regel einige der am häufigsten auftretenden Speicherprobleme. Anwendungen führen oft scheinbar einfache Abfragen aus, die aufgrund großer Sortier- oder Hashvorgänge zu Leistungsproblemen auf dem SQL Server führen können. Solche Abfragen verbrauchen nicht nur viel SQL Server-Arbeitsspeicher, sondern führen auch dazu, dass andere Abfragen warten, bis der Arbeitsspeicher verfügbar ist, und damit der Leistungsengpässe.
Mithilfe der hier beschriebenen Tools (DMVs, Perfmon-Leistungsindikatoren und tatsächliche Abfragepläne) können Sie ermitteln, welche Abfragen großgeschützt sind. Anschließend können Sie diese Abfragen optimieren oder neu schreiben, um die Arbeitsspeicherauslastung des Arbeitsbereichs aufzulösen oder zu reduzieren.
Was kann ein Entwickler über Sortier- und Hashvorgänge tun?
Nachdem Sie bestimmte Abfragen identifiziert haben, die einen großen Speicher für die Reservierung von Abfragen belegen, können Sie Schritte ausführen, um die Speichererteilungen zu reduzieren, indem Sie diese Abfragen neu entwerfen.
Was bewirkt Sortier- und Hashvorgänge in Abfragen
Der erste Schritt besteht darin, sich bewusst zu werden, welche Vorgänge in einer Abfrage zu Speichererteilungen führen können.
Gründe, warum eine Abfrage einen SORT-Operator verwenden würde:
ORDER BY (T-SQL) führt dazu, dass Zeilen sortiert werden, bevor sie als Endergebnis gestreamt werden.
GROUP BY (T-SQL) kann einen Sortieroperator in einem Abfrageplan vor der Gruppierung einführen, wenn kein zugrunde liegender Index vorhanden ist, der die gruppierten Spalten anordnet.
DISTINCT (T-SQL) verhält sich ähnlich wie
GROUP BY. Um unterschiedliche Zeilen zu identifizieren, werden die Zwischenergebnisse sortiert und dann Duplikate entfernt. Der Optimierer verwendet einenSortOperator vor diesem Operator, wenn die Daten aufgrund einer sortierten Indexsuche oder -überprüfung noch nicht sortiert sind.Der Zusammenführungsoperator , wenn er vom Abfrageoptimierer ausgewählt wird, erfordert, dass beide verknüpften Eingaben sortiert sind. SQL Server kann eine Sortierung auslösen, wenn ein gruppierter Index in der Verknüpfungsspalte in einer der Tabellen nicht verfügbar ist.
Gründe, warum eine Abfrage einen HASH-Abfrageplanoperator verwenden würde:
Diese Liste ist nicht vollständig, enthält aber die am häufigsten auftretenden Gründe für Hashvorgänge. Analysieren Sie den Abfrageplan , um die Hash-Übereinstimmungsvorgänge zu identifizieren.
JOIN (T-SQL):Sql Server hat beim Verknüpfen von Tabellen eine Auswahl zwischen drei physischen Operatoren,
Nested Loop, undHash JoinMerge Join. Wenn SQL Server schließlich eine Hashbeitrittsauswahl auswählt, benötigt er QE-Speicher, damit Zwischenergebnisse gespeichert und verarbeitet werden. In der Regel kann ein Mangel an guten Indizes zu diesem ressourcenintensiven Verknüpfungsoperator führen.Hash JoinInformationen zum Untersuchen des Abfrageplans zur IdentifizierungHash Matchfinden Sie unter Referenz zu logischen und physischen Operatoren.DISTINCT (T-SQL):Ein
Hash AggregateOperator kann verwendet werden, um Duplikate in einem Rowset zu beseitigen. Informationen zum Suchen nach (Hash MatchAggregate) im Abfrageplan finden Sie unter Referenz zu logischen und physischen Operatoren.UNION (T-SQL): Dies ist vergleichbar mit
DISTINCT. EineHash Aggregatekann verwendet werden, um die Duplikate für diesen Operator zu entfernen.SUMME/AVG/MAX/MIN (T-SQL): Jeder Aggregatvorgang kann als Aggregatvorgang ausgeführt
Hash Aggregatewerden. Informationen zum Suchen nach (Hash MatchAggregate) im Abfrageplan finden Sie unter Referenz zu logischen und physischen Operatoren.
Wenn Sie diese allgemeinen Gründe kennen, können Sie die großen Speicherzuteilungsanforderungen, die an SQL Server kommen, so weit wie möglich beseitigen.
Möglichkeiten zum Reduzieren von Sortier- und Hashvorgängen oder der Grant-Größe
- Halten Sie Statistiken auf dem neuesten Stand. Dieser grundlegende Schritt, der die Leistung für Abfragen auf vielen Ebenen verbessert, stellt sicher, dass der Abfrageoptimierer beim Auswählen von Abfrageplänen die genauesten Informationen enthält. SQL Server bestimmt, welche Größe auf der Grundlage von Statistiken für die Speichererteilung angefordert werden soll. Veraltete Statistiken können zu einer Überschätzung oder Unterschätzung des Erteilungsantrags führen und somit zu einer unnötig hohen Erteilungsanforderung oder zum Überlaufen von Ergebnissen auf den Datenträger führen. Stellen Sie sicher, dass die Statistik für die automatische Aktualisierung in Ihren Datenbanken aktiviert ist und/oder statische Daten mit UPDATE STATISTICS oder sp_updatestats aktualisiert werden.
- Verringern Sie die Anzahl der Zeilen, die aus Tabellen stammen. Wenn Sie einen restriktiveren WHERE-Filter oder einen JOIN-Filter verwenden und die Anzahl der Zeilen reduzieren, wird eine nachfolgende Sortierung im Abfrageplan zum Sortieren oder Aggregieren eines kleineren Resultsets verwendet. Ein kleinerer Zwischenergebnissatz erfordert weniger Arbeitsspeicher. Dies ist eine allgemeine Regel, die Entwickler nicht nur zum Speichern des Arbeitssatzspeichers befolgen können, sondern auch, um CPU und E/A zu reduzieren (dieser Schritt ist nicht immer möglich). Wenn bereits gut geschriebene und ressourceneffiziente Abfragen vorhanden sind, wurde diese Richtlinie erfüllt.
- Erstellen Sie Indizes für Verknüpfungsspalten, um Verknüpfungen zu unterstützen. Die Zwischenvorgänge in einem Abfrageplan sind von den Indizes in der zugrunde liegenden Tabelle betroffen. Wenn z. B. eine Tabelle keinen Index für eine Verknüpfungsspalte aufweist und eine Zusammenführungsverknnung der kosteneffizienteste Verknüpfungsoperator ist, müssen alle Zeilen aus dieser Tabelle sortiert werden, bevor die Verknüpfung ausgeführt wird. Wenn stattdessen ein Index in der Spalte vorhanden ist, kann ein Sortiervorgang entfernt werden.
- Erstellen Sie Indizes, um Hashvorgänge zu vermeiden. Im Allgemeinen beginnt die grundlegende Abfrageoptimierung mit der Überprüfung, ob Ihre Abfragen über geeignete Indizes verfügen, um sie dabei zu unterstützen, die Lesevorgänge zu reduzieren und große Sortierungen oder Hashvorgänge möglichst zu minimieren oder zu beseitigen. Hash-Verknüpfungen werden häufig ausgewählt, um große, nicht sortierte und nicht indizierte Eingaben zu verarbeiten. Das Erstellen von Indizes kann diese Optimiererstrategie ändern und den Datenempfang beschleunigen. Hilfe beim Erstellen von Indizes finden Sie unter Datenbankoptimierungsratgeber und Optimieren nicht gruppierter Indizes mit fehlenden Indexvorschlägen.
- Verwenden Sie COLUMNSTORE-Indizes, sofern dies für Aggregationsabfragen geeignet ist, die verwendet werden
GROUP BY. Analyseabfragen, die sehr große Rowsets verarbeiten und in der Regel "Gruppieren nach"-Aggregationen ausführen, benötigen möglicherweise große Speicherblöcke, um Die Arbeit zu erledigen. Wenn kein Index verfügbar ist, der sortierte Ergebnisse bereitstellt, wird automatisch eine Sortierung im Abfrageplan eingeführt. Eine Art sehr großes Ergebnis kann zu einer teuren Speichererteilung führen. - Entfernen Sie die
ORDER BYOption, wenn Sie sie nicht benötigen. In Fällen, in denen Ergebnisse auf eine Anwendung gestreamt werden, die die Ergebnisse auf eigene Weise sortiert oder dem Benutzer die Änderung der Reihenfolge der angezeigten Daten ermöglicht, müssen Sie keine Sortierung auf der SQL Server-Seite durchführen. Streamen Sie die Daten einfach in der Reihenfolge, in der der Server sie produziert, und lassen Sie den Endbenutzer die Daten selbst sortieren. Berichterstellungsanwendungen wie Power BI oder Reporting Services sind Beispiele für solche Anwendungen, mit denen Endbenutzer ihre Daten sortieren können. - Berücksichtigen Sie, wenn auch vorsichtig, die Verwendung eines LOOP JOIN-Hinweises , wenn Verknüpfungen in einer T-SQL-Abfrage vorhanden sind. Mit dieser Technik können Hash- oder Zusammenführungsbeitritte vermieden werden, die Speichererteilungen verwenden. Diese Option wird jedoch nur als letzte Möglichkeit vorgeschlagen, da das Erzwingen einer Verknüpfung zu einer erheblich langsameren Abfrage führen kann. Stress testen Sie Ihre Arbeitsauslastung, um sicherzustellen, dass dies eine Option ist. In einigen Fällen ist eine geschachtelte Schleifenbeitritt möglicherweise nicht einmal eine Option. In diesem Fall schlägt SQL Server möglicherweise mit einem Fehler MSSQLSERVER_8622 fehl, "Der Abfrageprozessor konnte aufgrund der in dieser Abfrage definierten Hinweise keinen Abfrageplan erstellen."
Hinweis zur Speichererteilungsabfrage
Seit SQL Server 2012 SP3 ist ein Abfragehinweis vorhanden, mit dem Sie die Größe der Speichererteilung pro Abfrage steuern können. Hier ist ein Beispiel für die Verwendung dieses Hinweises:
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
Es wird empfohlen, hier konservative Werte zu verwenden, insbesondere in den Fällen, in denen sie erwarten, dass viele Instanzen ihrer Abfrage gleichzeitig ausgeführt werden. Stellen Sie sicher, dass Sie Ihre Arbeitsauslastung auf Ihre Produktionsumgebung testen und bestimmen, welche Werte verwendet werden sollen.
Weitere Informationen finden Sie unter MAX_GRANT_PERCENT und MIN_GRANT_PERCENT.
Ressourcenkontrolle
Der QE-Speicher ist der Speicher, den die Ressourcenkontrolle tatsächlich begrenzt, wenn die einstellungen MIN_MEMORY_PERCENT und MAX_MEMORY_PERCENT verwendet werden. Nachdem Sie Abfragen identifiziert haben, die zu großen Speichererteilungen führen, können Sie den von Sitzungen oder Anwendungen verwendeten Arbeitsspeicher einschränken. Es ist erwähnenswert, dass die default Workloadgruppe eine Abfrage bis zu 25 % des Arbeitsspeichers belegen kann, der für eine SQL Server-Instanz gewährt werden kann. Weitere Informationen finden Sie unter Resource Governor Resource Pools und CREATE WORKLOAD GROUP.
Feedback zur adaptiven Abfrageverarbeitung und Speichererteilung
SQL Server 2017 hat das Feedbackfeature zur Speichererteilung eingeführt. Es ermöglicht dem Abfrageausführungsmodul, die gewährte Gewährung basierend auf dem vorherigen Verlauf der Abfrage anzupassen. Ziel ist es, die Größe der Gewährung nach Möglichkeit zu verringern oder zu erhöhen, wenn mehr Arbeitsspeicher benötigt wird. Dieses Feature wurde in drei Wellen veröffentlicht:
- Feedback zur Speichererteilung im Batchmodus in SQL Server 2017
- Feedback zur Speichererteilung im Zeilenmodus in SQL Server 2019
- Feedback zur Speichererteilung auf Datenträgerpersistenz mithilfe der Abfragespeicher- und Quantilserteilung in SQL Server 2022
Weitere Informationen finden Sie unter Feedback zur Speicherzuweisung. Die Speichererteilungsfunktion kann die Größe der Speichersubventionen für Abfragen zur Ausführungszeit verringern und so die Probleme verringern, die von großen Erteilungsanforderungen stammen. Mit diesem Feature, insbesondere in SQL Server 2019 und höheren Versionen, in denen die adaptive Verarbeitung im Zeilenmodus verfügbar ist, stellen Sie möglicherweise nicht einmal fest, dass speicherprobleme auftreten, die von der Abfrageausführung stammen. Wenn Sie dieses Feature (standardmäßig aktiviert) haben und weiterhin eine große QE-Speicherauslastung sehen, wenden Sie die zuvor beschriebenen Schritte zum Umschreiben von Abfragen an.
Erhöhen des SQL Server- oder Betriebssystemspeichers
Nachdem Sie die Schritte unternommen haben, um unnötige Speichererteilungen für Ihre Abfragen zu reduzieren, erfordert die Workload wahrscheinlich mehr Arbeitsspeicher, wenn weiterhin probleme mit geringem Arbeitsspeicher auftreten. Erwägen Sie daher, den Arbeitsspeicher für SQL Server mithilfe der max server memory Einstellung zu erhöhen, wenn genügend physischer Arbeitsspeicher auf dem System vorhanden ist. Befolgen Sie die Empfehlungen, um 25 % des Arbeitsspeichers für das Betriebssystem und andere Anforderungen zu verlassen. Weitere Informationen finden Sie unter Serverspeicherkonfigurationsoptionen. Wenn kein ausreichender Arbeitsspeicher auf dem System verfügbar ist, sollten Sie physischen RAM hinzufügen oder wenn es sich um einen virtuellen Computer handelt, den dedizierten RAM für Ihren virtuellen Computer erhöhen.
Interne Speichererteilung
Weitere Informationen zu einigen Internen im Abfrageausführungsspeicher finden Sie im Blogbeitrag zur Sql Server-Speichererteilung .
So erstellen Sie ein Leistungsszenario mit hoher Speicherzuteilung
Schließlich veranschaulicht das folgende Beispiel, wie sie einen großen Verbrauch des Abfrageausführungsspeichers simulieren und Abfragen einführen können, auf die RESOURCE_SEMAPHOREgewartet wird. Sie können dies tun, um zu erfahren, wie Sie die in diesem Artikel beschriebenen Diagnosetools und -techniken verwenden.
Warnung
Verwenden Sie dies nicht in einem Produktionssystem. Diese Simulation wird bereitgestellt, um Ihnen zu helfen, das Konzept zu verstehen und Ihnen zu helfen, es besser zu lernen.
Installieren Sie auf einem Testserver RML Utilities und SQL Server.
Verwenden Sie eine Clientanwendung wie SQL Server Management Studio, um die maximale Serverspeichereinstellung Ihres SQL Server auf 1.500 MB zu senken:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREÖffnen Sie eine Eingabeaufforderung, und ändern Sie das Verzeichnis in den Ordner "RML-Hilfsprogramme":
cd C:\Program Files\Microsoft Corporation\RMLUtilsVerwenden Sie ostress.exe , um mehrere gleichzeitige Anforderungen für Ihren SQL Server-Test zu erstellen. In diesem Beispiel werden 30 gleichzeitige Sitzungen verwendet, aber Sie können diesen Wert ändern:
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30Verwenden Sie die zuvor beschriebenen Diagnosetools, um die Probleme mit der Speichererteilung zu identifizieren.
Zusammenfassung der Möglichkeiten zum Umgang mit großen Speichererteilungen
- Schreiben Sie Abfragen neu.
- Aktualisieren Sie Statistiken, und halten Sie sie regelmäßig auf dem neuesten Stand.
- Erstellen Sie geeignete Indizes für die identifizierte Abfrage oder Abfragen. Indizes können die große Anzahl der verarbeiteten Zeilen verringern, wodurch die
JOINAlgorithmen geändert und die Größe von Finanzhilfen reduziert oder vollständig eliminiert wird. - Verwenden Sie den
OPTIONHinweis (min_grant_percent = XX, max_grant_percent = XX). - Verwenden Sie den Ressourcengouverneur.
- SQL Server 2017 und 2019 verwenden adaptive Abfrageverarbeitung, sodass der Speicherzuteilungs-Feedbackmechanismus die Größe der Speichererteilung dynamisch zur Laufzeit anpassen kann. Dieses Feature kann Probleme mit der Speichererteilung an erster Stelle verhindern.
- Erhöhen Sie den SQL Server- oder Betriebssystemspeicher.