Behandeln von Problemen mit langsamer Leistung oder unzureichendem Arbeitsspeicher aufgrund von Speicherzuweisungen in SQL Server
Was sind Speicherzuweisungen?
Speicherzuweisungen, die auch als QE-Reservierungen (Query Execution, Abfrageausführungsspeicher), Arbeitsbereichsspeicher und Speicherreservierungen bezeichnet werden, beschreiben die Nutzung des Arbeitsspeichers zur Abfrageausführungszeit. SQL Server ordnet diesen Arbeitsspeicher während der Abfrageausführung für einen oder mehrere der folgenden Zwecke zu:
- Sortiervorgänge
- Hashvorgänge
- Massenkopiervorgä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)
Um Kontext bereitzustellen, kann eine Abfrage während ihrer Lebensdauer Arbeitsspeicher von verschiedenen Speicherbelegungen oder -schreibern anfordern, je nachdem, was sie tun muss. Wenn beispielsweise eine Abfrage anfänglich analysiert und kompiliert wird, belegt sie Kompilierungsspeicher. Nachdem die Abfrage kompiliert wurde, wird dieser Arbeitsspeicher freigegeben, und der resultierende Abfrageplan wird im Cachespeicher des Plans gespeichert. Sobald ein Plan zwischengespeichert wurde, kann die Abfrage ausgeführt werden. Wenn die Abfrage Sortiervorgänge, Hash-Übereinstimmungsvorgänge (JOIN oder Aggregate) oder Einfügungen in columnstore-Indizes ausführt, verwendet sie Arbeitsspeicher aus der Zuweisung der Abfrageausführung. Zunächst fragt die Abfrage nach diesem Ausführungsspeicher, und später, wenn dieser Arbeitsspeicher gewährt wird, verwendet die Abfrage den gesamten oder einen Teil des Arbeitsspeichers zum Sortieren von Ergebnissen oder Hashbuckets. Dieser während der Abfrageausführung zugeordnete Arbeitsspeicher wird als Speicherzuweisungen bezeichnet. Wie Sie sich vorstellen können, wird die Speicherzuweisung nach Abschluss des Abfrageausführungsvorgangs wieder an SQL Server freigegeben, um sie für andere Aufgaben zu verwenden. Daher sind Speicherzuweisungen vorübergehender Natur, können aber noch 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 Speicherzuweisung
Hier sehen Sie ein Beispiel für eine Abfrage, die den Ausführungsspeicher und den zugehörigen Abfrageplan verwendet, der die Gewährung anzeigt:
SELECT *
FROM sys.messages
ORDER BY message_id
Diese Abfrage wählt ein Rowset mit über 300.000 Zeilen aus und sortiert es. Der Sortiervorgang löst eine Speicherzuweisungsanforderung aus. Wenn Sie diese Abfrage in SSMS ausführen, können Sie ihren Abfrageplan anzeigen. Wenn Sie den Operator ganz SELECT links des Abfrageplans auswählen, können Sie die Speicherzuweisungsinformationen für die Abfrage anzeigen (drücken Sie F4 , um Eigenschaften anzuzeigen):
Wenn Sie außerdem mit der rechten Maustaste auf den Leerraum im Abfrageplan klicken, können Sie Ausführungsplan-XML anzeigen... auswählen und nach einem XML-Element suchen, das die gleichen Speicherzuweisungsinformationen 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 müssen einige Begriffe erläutert werden. Eine Abfrage benötigt möglicherweise eine bestimmte Menge an Ausführungsspeicher (DesiredMemory) und fordert diesen Betrag häufig an (RequestedMemory). Zur Laufzeit gewährt SQL Server je nach Verfügbarkeit den gesamten oder einen Teil des angeforderten Arbeitsspeichers (GrantedMemory). Am Ende kann die Abfrage mehr oder weniger des ursprünglich angeforderten Arbeitsspeichers (MaxUsedMemory) verwenden. Wenn der Abfrageoptimierer die menge des benötigten Arbeitsspeichers überschätzt hat, verwendet er weniger als die angeforderte Größe. Dieser Speicher wird jedoch verschwendet, da er von einer anderen Anforderung hätte verwendet werden können. Wenn der Optimierer hingegen die erforderliche Arbeitsspeichergröße unterschätzt hat, können die überschüssigen Zeilen auf den Datenträger übergeben werden, um die Arbeit zur Ausführungszeit zu erledigen. Anstatt mehr Arbeitsspeicher als die ursprünglich angeforderte Größe zuzuweisen, ü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 unter Überlegungen zur Speicherzuweisung.
Begrifflichkeiten
Sehen wir uns die verschiedenen Begriffe an, die in Bezug auf diesen Speicherverbraucher auftreten können. Auch hier beschreiben alle Konzepte, die sich auf die gleichen Speicherbelegungen beziehen.
Abfrageausführungsspeicher (QE-Arbeitsspeicher): Dieser Begriff wird verwendet, um die Tatsache hervorzuheben, dass der Sortier- oder Hashspeicher während der Ausführung einer Abfrage verwendet wird. Häufig ist QE-Arbeitsspeicher der größte Speicherverbraucher während der Lebensdauer einer Abfrage.
QE-Reservierungen (Query Execution, Abfrageausführung) oder Speicherreservierungen: Wenn eine Abfrage Arbeitsspeicher für Sortier- oder Hashvorgänge benötigt, stellt sie eine Reservierungsanforderung für den Arbeitsspeicher. Diese Reservierungsanforderung wird zur Kompilierzeit basierend auf der geschätzten Kardinalität berechnet. Später, wenn die Abfrage ausgeführt wird, gewährt SQL Server diese Anforderung je nach Arbeitsspeicherverfügbarkeit teilweise oder vollständig. Am Ende kann die Abfrage einen Prozentsatz des gewährten Arbeitsspeichers verwenden. Es gibt einen Speicheradministrator (Speicherbuchhalter) mit dem Namen "MEMORYCLERK_SQLQERESERVATIONS", der diese Speicherbelegungen nachverfolgt (siehe DBCC MEMORYSTATUS oder sys.dm_os_memory_clerks).
Speicherzuweisungen: Wenn SQL Server einer ausgeführten Abfrage den angeforderten Arbeitsspeicher gewährt, wird davon gesprochen, dass eine Speicherzuweisung erfolgt ist. Es gibt einige Leistungsindikatoren, die den Begriff "Gewährung" verwenden. Diese Leistungsindikatoren und
Memory Grants Pendingzeigen die Anzahl der erfülltenMemory Grants Outstandingoder wartend ausgeführten Speicherzuweisungen an. Sie berücksichtigen nicht die Größe der Speicherzuweisung. Eine Abfrage allein könnte beispielsweise 4 GB Arbeitsspeicher für eine Sortierung verbraucht haben, aber dies spiegelt sich in keinem dieser Leistungsindikatoren wider.Arbeitsbereichsspeicher ist ein weiterer Begriff, der denselben Speicher beschreibt. Häufig wird dieser Begriff im Perfmon-Zähler
Granted Workspace Memory (KB)angezeigt, der die Gesamtmenge des derzeit für Sortier-, Hash-, Massenkopier- und Indexerstellungsvorgängen verwendeten Arbeitsspeichers in KB widerspiegelt. DerMaximum Workspace Memory (KB), ein weiterer Leistungsindikator, berücksichtigt die maximale Menge an Arbeitsbereichsarbeitsspeicher, die für Alle Anforderungen verfügbar ist, die solche Hash-, Sortier-, Massenkopier- und Indexerstellungsvorgänge ausführen müssen. Der Begriff Arbeitsbereichsspeicher wird außerhalb dieser beiden Leistungsindikatoren selten gefunden.
Auswirkungen einer großen QE-Speicherauslastung auf die Leistung
Wenn ein Thread Arbeitsspeicher in SQL Server anfordert, um etwas zu erledigen, und der Arbeitsspeicher nicht verfügbar ist, schlägt die Anforderung in den meisten Fällen mit einem Fehler vom Typ "Nicht genügend Arbeitsspeicher" fehl. Es gibt jedoch einige Ausnahmeszenarien, in denen der Thread nicht fehlschlägt, sondern wartet, bis Arbeitsspeicher verfügbar ist. Eines dieser Szenarien ist die Speicherzuweisung, das andere ist der Arbeitsspeicher 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, führt dies dazu, dass die Abfrage nicht mit einem Fehler vom Typ "Nicht genügend Arbeitsspeicher" fehlschlägt, sondern die Abfrage wartet. Da der Arbeitsbereichsarbeitsspeicher 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 ausführungsspeicher angefordert, und zusammen haben sie den QE-Speicherpool erschöpft, oder einige gleichzeitige Abfragen haben jeweils sehr große Zuweisungen angefordert. In beiden Richtungen 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 Speicherzuweisungsanforderungen zu schaffen. Dies bedeutet, dass Seitenlesevorgänge, die von Abfrageanforderungen stammen, vom Datenträger erfüllt werden müssen (ein deutlich langsamerer Vorgang).
- Bei Anforderungen für andere Speicherbelegungen können Fehler aufgrund von nicht genügend Arbeitsspeicher auftreten, 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 wird, und es dauert lange, bis sie abgeschlossen sind. Anders ausgedrückt: Für den Endbenutzer sind diese Abfragen langsam.
Wenn Sie daher Wartezeiten für den Abfrageausführungsspeicher in Perfmon, dynamische Verwaltungssichten (Dynamic Management Views, DMVs) oder DBCC MEMORYSTATUSbeobachten, müssen Sie reagieren, um dieses Problem zu beheben, insbesondere, wenn das Problem häufig auftritt. Weitere Informationen finden Sie unter Was kann ein Entwickler bei Sortier- und Hashvorgängen tun?
Identifizieren von Wartevorgängen für den Abfrageausführungsspeicher
Es gibt mehrere Möglichkeiten, Wartezeiten für QE-Reservierungen zu bestimmen. 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 (z. B. Perfmon ist in Azure SQL-Datenbank nicht verfügbar). Nachdem Sie das Problem identifiziert haben, müssen Sie einen Drilldown auf einzelner Abfrageebene ausführen, um zu sehen, welche Abfragen optimierungs- oder neu geschrieben werden müssen.
Verwenden Sie auf Serverebene die folgenden Methoden:
- DMV-sys.dm_exec_query_resource_semaphores für Ressourcensemaphor Weitere Informationen finden Sie unter sys.dm_exec_query_resource_semaphores.
- Leistungsmonitor Leistungsindikatoren Weitere Informationen finden Sie unter SQL Server Speicher-Manager-Objekt.
- DBCC MEMORYSTATUS Weitere Informationen finden Sie unter DBCC MEMORYSTATUS.
- DMV-sys.dm_os_memory_clerks für Speicherbearbeiter Weitere Informationen finden Sie unter sys.dm_os_memory_clerks.
- Identifizieren von Speicherzuweisungen mithilfe von erweiterten Ereignissen (XEvents) Weitere Informationen finden Sie unter Erweiterte Ereignisse (XEvents).For more information, see Extended Events (XEvents).
Verwenden Sie auf einzelner Abfrageebene die folgenden Methoden:
- Identifizieren bestimmter Abfragen mit sys.dm_exec_query_memory_grants: Aktuell ausgeführte Abfragen. Weitere Informationen finden Sie unter sys.dm_exec_query_memory_grants.
- Identifizieren bestimmter 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: Verlaufsstatistiken für 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: Verlaufsstatistiken für Abfragen mit QDS. Weitere Informationen finden Sie unter sys.query_store_runtime_stats.
Aggregieren von Speicherauslastungsstatistiken
DMV-sys.dm_exec_query_resource_semaphores für Ressourcensemaphor
Diese DMV unterteilt den Speicher der Abfragereservierung nach Ressourcenpool (intern, Standard und benutzerseitig erstellt) und resource_semaphore (reguläre und kleine Abfrageanforderungen). 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 etwa 900 MB Arbeitsspeicher für die Abfrageausführung von 22 Anforderungen verwendet werden und 3 weitere warten. Dies erfolgt im Standardpool (pool_id = 2) und im 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)
Leistungsmonitor Indikatoren
Ähnliche Informationen sind über Leistungsmonitor Leistungsindikatoren verfügbar, in denen Sie die derzeit gewährten Anforderungen (Memory Grants Outstanding), die wartenden Gewährungsanforderungen (Memory Grants Pending) und die Menge des von Speicherzuweisungen verwendeten Arbeitsspeichers (Granted Workspace Memory (KB)) beobachten können. In der folgenden Abbildung sind die ausstehenden Zuweisungen 18, die ausstehenden Zuweisungen sind 2 und der gewährte Arbeitsbereichsarbeitsspeicher beträgt 828.288 KB. Der Memory Grants Pending Perfmon-Zähler mit einem Wert ungleich 0 (null) gibt an, dass der Arbeitsspeicher erschöpft ist.
Weitere Informationen finden Sie unter SQL Server Speicher-Manager-Objekt.
- SQLServer, Arbeitsspeicher-Manager: Maximaler Arbeitsbereichsarbeitsspeicher (KB)
- SQLServer, Arbeitsspeicher-Manager: Arbeitsspeicherzuweisungen ausstehend
- SQLServer, Speicher-Manager: Arbeitsspeicherzuweisungen stehen aus
- SQLServer, Speicher-Manager: Gewährter Arbeitsbereichsarbeitsspeicher (KB)
DBCC MEMORYSTATUS
Ein weiterer Ort, an dem Sie Details zum Abfragereservierungsspeicher anzeigen können, ist DBCC MEMORYSTATUS (Abschnitt Abfragespeicherobjekte). Sie können die Query Memory Objects (default) Ausgabe für Benutzerabfragen anzeigen. Wenn Sie z. B. Resource Governor mit einem Ressourcenpool namens PoolAdmin aktiviert haben, können Sie sich sowohl als Query Memory Objects (PoolAdmin)auch Query Memory Objects (default) ansehen.
Hier sehen Sie eine Beispielausgabe eines Systems, bei dem 18 Anforderungen Arbeitsspeicher für die Abfrageausführung und zwei Anforderungen auf Arbeitsspeicher gewährt wurden. Der verfügbare Leistungsindikator ist 0 (null), was darauf hinweist, dass kein Arbeitsbereichsspeicher mehr verfügbar ist. Diese Tatsache erklärt die beiden wartenden Anforderungen. Zeigt Wait Time die verstrichene Zeit in Millisekunden an, seit eine Anforderung in die Wartewarteschlange eingefügt wurde. Weitere Informationen zu diesen Leistungsindikatoren finden Sie unter Abfragen von Speicherobjekten.
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 auch Informationen zum Arbeitsspeicherbearbeiter an, der den Arbeitsspeicher für die Abfrageausführung nachverfolgt. Die folgende Ausgabe zeigt, dass die Seiten, die für QE-Reservierungen (Query Execution) zugeordnet sind, 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
DMV-sys.dm_os_memory_clerks für Speicherbearbeiter
Wenn Sie ein tabellarisches Resultset benötigen, das sich vom abschnittsbasierten DBCC MEMORYSTATUSunterscheidet, können Sie sys.dm_os_memory_clerks für ähnliche Informationen verwenden. Suchen Sie nach dem MEMORYCLERK_SQLQERESERVATIONS Memory Clerk. Die Abfragespeicherobjekte sind in dieser DMV jedoch nicht verfügbar.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Hier sehen Sie eine Beispielausgabe:
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Identifizieren von Speicherzuweisungen mithilfe von erweiterten Ereignissen (XEvents)
Es gibt mehrere erweiterte Ereignisse, die Informationen zur Speicherzuweisung 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 Arbeitsspeicher zu gewähren. Wenn Diese zusätzliche Speicherzuweisung nicht abgerufen wird, kann die Abfrageverlangsamung auftreten.
- sqlserver.query_memory_grant_blocking: Tritt auf, wenn eine Abfrage andere Abfragen blockiert, während auf eine Speicherzuweisung gewartet wird.
- sqlserver.query_memory_grant_info_sampling: Tritt am Ende der zufällig erfassten Abfragen auf, die Informationen zur Speicherzuweisung bereitstellen (sie können z. B. für Telemetriedaten verwendet werden).
- sqlserver.query_memory_grant_resource_semaphores: Tritt in Intervallen von fünf Minuten für jeden Ressourcenkontrolle-Ressourcenpool auf.
- sqlserver.query_memory_grant_usage: Tritt am Ende der Abfrageverarbeitung für Abfragen mit Speicherzuweisungen von mehr als 5 MB auf, um Benutzer über Ungenauigkeiten bei der Speicherzuweisung zu informieren.
- sqlserver.query_memory_grants: Tritt in Intervallen von fünf Minuten für jede Abfrage mit einer Speicherzuweisung auf.
Feedback zur Speicherzuweisung erweiterter Ereignisse
Informationen zu Feedbackfeatures zur Speicherzuweisung bei der Abfrageverarbeitung finden Sie unter Feedback zur Speicherzuweisung.
- sqlserver.memory_grant_feedback_loop_disabled: Tritt auf, wenn die Feedbackschleife zur Speicherzuweisung deaktiviert ist.
- sqlserver.memory_grant_updated_by_feedback: Tritt auf, wenn die Speicherzuweisung durch Feedback aktualisiert wird.
Abfrageausführungswarnungen, die sich auf Speicherzuweisungen beziehen
- sqlserver.execution_warning: Tritt auf, wenn eine T-SQL-Anweisung oder gespeicherte Prozedur mehr als eine Sekunde auf eine Speicherzuweisung wartet oder wenn der erste Versuch, Arbeitsspeicher abzurufen, fehlschlägt. Verwenden Sie dieses Ereignis in Kombination mit Ereignissen, die Wartezeiten zur Behandlung von Konfliktproblemen identifizieren, 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 eines Hashjoins zu verarbeiten. Verwenden Sie dieses Ereignis zusammen mit einem der
query_pre_execution_showplanEreignisse oderquery_post_execution_showplan, um zu bestimmen, welcher Vorgang im generierten Plan die Hashüberlauf verursacht. - sqlserver.hash_warning: Tritt auf, wenn nicht genügend Arbeitsspeicher vorhanden ist, um die Buildeingabe eines Hashjoins zu verarbeiten. Dies führt entweder zu einer Hashrekursion, wenn die Buildeingabe partitioniert wird, oder zu einem 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_showplan, 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: ein
Order Byin einerSelect-Anweisung.) Verwenden Sie dieses Ereignis, um Abfragen zu identifizieren, die aufgrund des Sortiervorgangs langsam ausgeführt werden, insbesondere wenn =warning_type2, was darauf hinweist, dass mehrere Durchläufe über die Daten zum Sortieren erforderlich waren.
Planen der Generierung von Ereignissen, die Informationen zur Speicherzuweisung 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
Erstellen 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: Die Speicher-Engine hat einen geringen Arbeitsspeicher erkannt, und die Zeilengruppengröße wurde reduziert. Hier gibt es mehrere interessante Spalten.
- sqlserver.column_store_index_build_memory_trace: Ablaufverfolgung der Speicherauslastung während der Indexerstellung.
- sqlserver.column_store_index_build_memory_usage_scale_down: Speicher-Engine herunterskaliert.
- 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 Arbeitsspeicher für die Abfrageausführung verbrauchen, und die Abfragen, die auf denselben Arbeitsspeicher warten. Die letztere Gruppe kann aus Anforderungen mit geringem Bedarf an Speicherzuweisungen bestehen, und wenn ja, können Sie Ihre Aufmerksamkeit an anderer Stelle konzentrieren. Sie könnten aber auch die Schuldigen sein, wenn sie große Speichergrößen anfordern. Konzentrieren Sie sich auf sie, wenn Sie dies für richtig erahen. Es kann üblich sein, dass eine bestimmte Abfrage der Täter ist, aber viele Instanzen davon werden erzeugt. Diese Instanzen, die die Speicherzuweisungen erhalten, führen dazu, dass andere Instanzen derselben Abfrage auf die Gewährung 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 Arbeitsspeichergröße, die angefordert und gewährt wurden, können Sie die sys.dm_exec_query_memory_grants dynamische Verwaltungssicht abfragen. Diese DMV zeigt Informationen zu derzeit ausgeführten Abfragen an, keine Verlaufsinformationen.
Die folgende Anweisung ruft Daten aus der DMV ab und ruft als Ergebnis auch den Abfragetext und den Abfrageplan 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 sehen Sie eine abgekürzte Beispielausgabe der Abfrage während des aktiven QE-Speicherverbrauchs. Bei den meisten Abfragen wird ihr Arbeitsspeicher gewährt, wie durch granted_memory_kb und used_memory_kb als numerische Werte ungleich NULL dargestellt wird. Die Abfragen, denen ihre Anforderung nicht gewährt wurde, warten auf den Ausführungsspeicher und den granted_memory_kb = NULL. Außerdem werden sie in einer Wartewarteschlange mit einem queue_id = 6 platziert. Ihre wait_time_ms gibt etwa 37 Sekunden Wartezeit an. Sitzung 72 ist die nächste Zeile, um eine Gewährung zu erhalten, wie durch wait_order = 1 angegeben, während Sitzung 74 folgt mit wait_order = 2.
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
Identifizieren bestimmter Abfragen mit sys.dm_exec_requests
Es gibt einen Wartetyp in SQL Server, der angibt, dass eine Abfrage auf die Speicherzuweisung RESOURCE_SEMAPHOREwartet. Sie können diesen Wartetyp für einzelne Anforderungen in sys.dm_exec_requests beobachten. Diese letztere DMV ist der beste Ausgangspunkt, um zu ermitteln, welche Abfragen Opfer von unzureichendem Arbeitsspeicher für die Gewährung 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 nicht gewährt werden kann, weil der Arbeitsspeicher von anderen gleichzeitigen Abfragen verbraucht wurde. Eine hohe Anzahl von wartenden Anforderungen und lange Wartezeiten deuten auf eine übermäßige Anzahl gleichzeitiger Abfragen hin, die den Ausführungsspeicher oder große Arbeitsspeicheranforderungsgrößen verwenden.
Hinweis
Die Wartezeit für Speicherzuweisungen ist begrenzt. Nach einer übermäßigen Wartezeit (z. B. mehr als 20 Minuten) SQL Server eine Zeitüberschreitung der Abfrage und löst den Fehler 8645 aus: "Beim Warten auf die Ausführung der Abfrage durch Speicherressourcen ist ein Timeout aufgetreten. Führen Sie die Abfrage erneut aus." Möglicherweise wird der Timeoutwert auf Serverebene festgelegt, indem Sie sich in sys.dm_exec_query_memory_grantsansehentimeout_sec. Der Timeoutwert kann zwischen SQL Server Versionen leicht variieren.
Mit der Verwendung von sys.dm_exec_requestskönnen Sie sehen, welche Abfragen Arbeitsspeicher und die Größe dieser Gewährung erhalten haben. Außerdem können Sie ermitteln, welche Abfragen derzeit auf eine Speicherzuweisung warten, indem Sie nach dem RESOURCE_SEMAPHORE Wartetyp suchen. Im Folgenden finden Sie eine Abfrage, die sowohl die gewährten als auch die wartende Anforderung 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 gewährt wurde, und zwei Dutzend weitere warten auf Zuweisungen. Die granted_query_memory Spalte gibt die Größe auf 8-KB-Seiten an. Ein Wert von 34.709 bedeutet beispielsweise, dass 34.709 * 8 KB = 277.672 KB Arbeitsspeicher gewährt werden.
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 Speicherzuweisung in diesem Moment nicht auftritt, Sie aber die beleidigenden Abfragen identifizieren möchten, können Sie verlaufsbezogene Abfragedaten über sys.dm_exec_query_statsanzeigen. Die Lebensdauer der Daten ist an den Abfrageplan der einzelnen Abfragen gebunden. Wenn ein Plan aus dem Plancache entfernt wird, werden die entsprechenden Zeilen aus dieser Ansicht entfernt. Anders ausgedrückt: Die DMV behält Statistiken im Arbeitsspeicher bei, die nach einem SQL Server Neustart nicht beibehalten werden oder wenn die Arbeitsspeicherauslastung zu einer Freigabe des Plancaches führt. Abgesehen davon können Sie die Informationen hier nützlich finden, insbesondere für aggregierte Abfragestatistiken. Möglicherweise hat jemand kürzlich berichtet, dass große Speicherzuweisungen von Abfragen aufgetreten sind, aber wenn Sie sich die Serverworkload ansehen, stellen Sie möglicherweise fest, dass das Problem nicht mehr vorhanden ist. In dieser Situation kann die Erkenntnisse liefern, sys.dm_exec_query_stats die andere DVMs nicht können. Hier sehen Sie eine Beispielabfrage, die Ihnen helfen kann, die 20 wichtigsten Anweisungen zu finden, die die größten Mengen an Ausführungsspeicher verbraucht haben. Diese Ausgabe zeigt einzelne Anweisungen an, auch 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 20 obersten Anweisungen mit einer maximalen Gewährungsgröße von mehr als 5 MB ab.
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 Erkenntnisse können sie gewinnen, indem Sie sich die von query_hashaggregierten Abfragen ansehen. In diesem Beispiel wird veranschaulicht, wie Sie die durchschnittlichen, maximalen und minimalen Gewährungsgrößen für eine Abfrageanweisung in allen instanzen seit dem ersten Zwischenspeichern des Abfrageplans ermitteln.
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 mit dem Abfragehash übereinstimmt, aber sie sollte ohne Berücksichtigung bestimmter Werte in der -Anweisung gelesen werden. Wenn eine -Anweisung z. B. enthält WHERE Id = 5, können Sie sie in ihrer generischeren Form lesen: WHERE Id = @any_value.
Hier sehen Sie eine abgekürzte Beispielausgabe der Abfrage, in der nur ausgewählte Spalten angezeigt werden:
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 gespeicherten Verlaufsstatistiken nutzen. Im Gegensatz zu Daten aus sys.dm_exec_query_statsüberstehen diese Statistiken einen SQL Server Neustart oder Arbeitsspeicherauslastung, da sie in einer Datenbank gespeichert sind. QDS verfügt auch über Größenbeschränkungen und eine Aufbewahrungsrichtlinie. Weitere Informationen finden Sie in den Abschnitten Festlegen des optimalen Abfragespeicher Erfassungsmodus und Beibehalten der relevantesten Daten in Abfragespeicher unter Bewährte Methoden für die Verwaltung der Abfragespeicher.
Ermitteln Sie mithilfe dieser Abfrage, ob für Ihre Datenbanken Abfragespeicher aktiviert sind:
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 -Anweisungen. Ein Unterschied besteht jedoch darin, dass Sie bei QDS nur Abfragen im Bereich dieser Datenbank betrachten, nicht die gesamte SQL Server. Daher müssen Sie möglicherweise die Datenbank kennen, in der eine bestimmte Speicherzuweisungsanforderung ausgeführt wurde. Führen Sie andernfalls diese Diagnoseabfrage in mehreren Datenbanken aus, bis Sie die beträchtlichen Speicherzuweisungen gefunden haben.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
Eine benutzerdefinierte Diagnoseabfrage
Hier sehen Sie eine Abfrage, die Daten aus mehreren Ansichten kombiniert, einschließlich der drei zuvor aufgeführten Ansichten. Zusätzlich zu den von bereitgestellten Statistiken auf Serverebene bietet sie eine ausführlichere Übersicht über die Sitzungen und deren Zuweisungen sys.dm_exec_query_resource_semaphoresüber sys.dm_exec_requests und sys.dm_exec_query_memory_grants.
Hinweis
Diese Abfrage würde aufgrund der Verwendung von sys.dm_exec_query_resource_semaphores zwei Zeilen pro Sitzung zurückgeben (eine Zeile für den regulären Ressourcensemaphor und eine andere für den Ressourcensemaphor mit kleinen Abfragen).
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 )
Hinweis
Der LOOP JOIN Hinweis wird in dieser Diagnoseabfrage verwendet, um eine Speicherzuweisung durch die Abfrage selbst zu vermeiden, und es wird keine ORDER BY -Klausel verwendet. Wenn die Diagnoseabfrage selbst auf eine Zuweisung wartet, würde ihr Zweck der Diagnose von Speicherzuweisungen nicht mehr bestehen. Der LOOP JOIN Hinweis kann dazu führen, dass die Diagnoseabfrage langsamer ist, aber in diesem Fall ist es wichtiger, die Diagnoseergebnisse abzurufen.
Hier sehen Sie eine abgekürzte Beispielausgabe 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 deutlich, wie eine von = 60 übermittelte Abfrage erfolgreich die angeforderte Speicherzuweisung von session_id 9 MB erhalten hat, aber nur 7 MB erforderlich waren, um die Abfrageausführung erfolgreich zu starten. Am Ende hat die Abfrage nur 1 MB der 9 MB verwendet, die sie vom Server empfangen hat. Die Ausgabe zeigt auch, dass die Sitzungen 75 und 86 auf Speicherzuweisungen warten, also .RESOURCE_SEMAPHOREwait_type Ihre Wartezeit beträgt mehr als 1.300 Sekunden (21 Minuten), und ist granted_memory_mbNULL.
Diese Diagnoseabfrage ist ein Beispiel, daher können Sie sie in jeder Weise ändern, die Ihren Anforderungen entspricht. Eine Version dieser Abfrage wird auch in Diagnosetools verwendet, die von Microsoft SQL Server Support verwendet werden.
Diagnosetools
Es gibt Diagnosetools, die microsoft SQL Server technischer Support verwendet, um Protokolle zu sammeln und Probleme effizienter zu beheben. SQL LogScout und Pssdiag Configuration Manager (zusammen mit SQLDiag) sammeln Ausgaben der zuvor beschriebenen DMVs und Leistungsmonitor Indikatoren, die Ihnen bei der Diagnose von Problemen mit der Speicherzuweisung helfen können.
Wenn Sie SQL LogScout 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 - und -- dm_exec_query_memory_grants -- -Ausgaben suchen-- dm_exec_query_resource_semaphores --. Alternativ können Sie die Ausgabe von SQL LogScout oder PSSDIAG mithilfe von SQL Nexus in eine SQL Server-Datenbank importieren. SQL Nexus erstellt die beiden Tabellen tbl_dm_exec_query_resource_semaphores und tbl_dm_exec_query_memory_grants, die die informationen enthalten, die zum Diagnostizieren von Speicherzuweisungen erforderlich sind. SQL LogScout 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 Speicherzuweisungen für entwickler oder DBA wichtig?
Basierend auf der Microsoft-Supporterfahrung gehören Speicherzuweisungsprobleme in der Regel zu den häufigsten Speicherproblemen. Anwendungen führen häufig scheinbar einfache Abfragen aus, die aufgrund großer Sortierungs- oder Hashvorgänge zu Leistungsproblemen auf der 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 Arbeitsspeicher verfügbar wird, was zu einem Leistungsengpass führt.
Mithilfe der hier beschriebenen Tools (DMVs, Perfmon-Leistungsindikatoren und tatsächliche Abfragepläne) können Sie ermitteln, welche Abfragen consumer mit großen Genehmigungen sind. Anschließend können Sie diese Abfragen optimieren oder umschreiben, um die Arbeitsspeicherauslastung des Arbeitsbereichs aufzulösen oder zu reduzieren.
Was kann ein Entwickler bei Sortier- und Hashvorgängen tun?
Nachdem Sie bestimmte Abfragen identifiziert haben, die eine große Menge an Abfragereservierungsspeicher verbrauchen, können Sie Maßnahmen ergreifen, um die Speicherzuweisungen zu reduzieren, indem Sie diese Abfragen neu entwerfen.
Ursache von Sortier- und Hashvorgängen in Abfragen
Der erste Schritt besteht darin, sich bewusst zu werden, welche Vorgänge in einer Abfrage zu Speicherzuweisungen 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 vor der Gruppierung einen Sortieroperator in einem Abfrageplan 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 werden Duplikate entfernt. Der Optimierer verwendet einenSortOperator vor diesem Operator, wenn die Daten aufgrund einer sortierten Indexsuche oder -überprüfung nicht bereits sortiert sind.Wenn der Merge Join-Operator vom Abfrageoptimierer ausgewählt wird, müssen beide verknüpften Eingaben sortiert werden. SQL Server kann eine Sortierung auslösen, wenn in der Joinspalte in einer der Tabellen kein gruppierter Index 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 gefundenen Gründe für Hashvorgänge. Analysieren Sie den Abfrageplan , um die Hash-Übereinstimmungsvorgänge zu identifizieren.
JOIN (T-SQL): Beim Verknüpfen von Tabellen hat SQL Server die Wahl zwischen drei physischen Operatoren:
Nested Loop,Merge JoinundHash Join. Wenn SQL Server sich schließlich für einen Hashjoin entscheidet, benötigt er QE-Arbeitsspeicher, damit Zwischenergebnisse gespeichert und verarbeitet werden. In der Regel kann ein Mangel an guten Indizes zu diesem ressourcenintensivsten Joinoperator führen,Hash Join. Informationen zum Untersuchen des Abfrageplans zur IdentifizierungHash Matchvon finden Sie unter Referenz zu logischen und physischen Operatoren.DISTINCT (T-SQL): Ein
Hash AggregateOperator könnte verwendet werden, um Duplikate in einem Rowset zu beseitigen. Informationen zum Suchen nach einemHash Match(Aggregate) im Abfrageplan finden Sie unter Referenz zu logischen und physischen Operatoren.UNION (T-SQL):Dies ähnelt
DISTINCT. EinHash Aggregatekönnte verwendet werden, um die Duplikate für diesen Operator zu entfernen.SUM/AVG/MAX/MIN (T-SQL): Jeder Aggregatvorgang kann potenziell als
Hash Aggregateausgeführt werden. Informationen zum Suchen nach einemHash Match(Aggregate) im Abfrageplan finden Sie unter Referenz zu logischen und physischen Operatoren.
Wenn Sie diese häufigen Gründe kennen, können Sie die großen Speicherzuweisungsanforderungen, die an SQL Server gesendet werden, so weit wie möglich beseitigen.
Möglichkeiten zum Reduzieren von Sortier- und Hashvorgängen oder der Gewährungsgröß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 bei der Auswahl von Abfrageplänen über die genauesten Informationen verfügt. SQL Server bestimmt basierend auf Statistiken, welche Größe für die Speicherzuweisung anzufordern ist. Veraltete Statistiken können zu einer Über- oder Unterschätzung der Gewährungsanforderung führen und somit zu einer unnötig hohen Gewährungsanforderung bzw. zu einem Überlauf der Ergebnisse auf den Datenträger führen. Stellen Sie sicher, dass die automatische Aktualisierung von Statistiken in Ihren Datenbanken aktiviert ist, und/oder halten Sie statische Daten mit UPDATE STATISTICS oder sp_updatestats auf dem neuesten Stand.
- Reduzieren 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 nach einem kleineren Resultset sortiert oder aggregiert. Für ein kleineres Zwischenresultset ist weniger Arbeitsspeicher erforderlich. Dies ist eine allgemeine Regel, die Entwickler nicht nur zum Speichern von Arbeitsspeicher für Arbeitssätze, sondern auch zum Reduzieren von CPU und E/A befolgen können (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 Joinspalten, um Mergejoins zu unterstützen. Die Zwischenvorgänge in einem Abfrageplan werden von den Indizes der zugrunde liegenden Tabelle beeinflusst. Wenn eine Tabelle beispielsweise keinen Index für eine Joinspalte aufweist und ein Mergejoin der kostengünstigste Joinoperator ist, müssen alle Zeilen aus dieser Tabelle sortiert werden, bevor der Join ausgeführt wird. Wenn stattdessen ein Index für die Spalte vorhanden ist, kann ein Sortiervorgang entfernt werden.
- Erstellen Sie Indizes, um Hashvorgänge zu vermeiden. In der Regel beginnt die grundlegende Abfrageoptimierung mit der Überprüfung, ob Ihre Abfragen über geeignete Indizes verfügen, um Lesevorgänge zu reduzieren und große Sortierungen oder Hashvorgänge nach Möglichkeit zu minimieren oder zu eliminieren. Hashjoins werden häufig ausgewählt, um große, unsortierte und nicht indizierte Eingaben zu verarbeiten. Das Erstellen von Indizes kann diese Optimiererstrategie ändern und den Datenabruf beschleunigen. Unterstützung beim Erstellen von Indizes finden Sie unter Datenbankoptimierungsratgeber und Optimieren nicht gruppierter Indizes mit Vorschlägen für fehlende Indizes.
- Verwenden Sie COLUMNSTORE-Indizes bei Bedarf für Aggregationsabfragen, die verwenden
GROUP BY. Analyseabfragen, die sich mit sehr großen Rowsets befassen 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 geordnete Ergebnisse liefert, wird automatisch eine Sortierung im Abfrageplan eingeführt. Eine Art sehr großes Ergebnis kann zu einer teuren Speicherzuweisung führen. - Entfernen Sie die ,
ORDER BYwenn Sie sie nicht benötigen. In Fällen, in denen Ergebnisse an eine Anwendung gestreamt werden, die die Ergebnisse auf eigene Weise sortiert oder dem Benutzer ermöglicht, die Reihenfolge der angezeigten Daten zu ändern, 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 erzeugt, an die Anwendung, und lassen Sie sie vom Endbenutzer selbst sortieren. Berichtsanwendungen wie Power BI oder Reporting Services sind Beispiele für solche Anwendungen, mit denen Endbenutzer ihre Daten sortieren können. - Erwägen Sie, wenn auch vorsichtig, die Verwendung eines LOOP JOIN-Hinweises , wenn Joins in einer T-SQL-Abfrage vorhanden sind. Diese Technik kann Hash- oder Mergejoins vermeiden, die Speicherzuweisungen verwenden. Diese Option wird jedoch nur als letztes Mittel empfohlen, da das Erzwingen eines Joins zu einer deutlich langsameren Abfrage führen kann. Testen Sie Ihre Workload, um sicherzustellen, dass dies eine Option ist. In einigen Fällen ist ein geschachtelter Schleifenjoin nicht einmal eine Option. In diesem Fall schlägt SQL Server möglicherweise mit dem Fehler MSSQLSERVER_8622 fehl: "Der Abfrageprozessor konnte aufgrund der in dieser Abfrage definierten Hinweise keinen Abfrageplan erstellen."
Abfragehinweis zur Speicherzuweisung
Seit SQL Server 2012 SP3 ist ein Abfragehinweis vorhanden, mit dem Sie die Größe der Speicherzuweisung pro Abfrage steuern können. Hier sehen Sie 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 Fällen, in denen Sie erwarten, dass viele Instanzen Ihrer Abfrage gleichzeitig ausgeführt werden. Stellen Sie sicher, dass Sie Ihre Workload so testen, dass sie mit Ihrer Produktionsumgebung übereinstimmt und welche Werte verwendet werden sollen.
Weitere Informationen finden Sie unter MAX_GRANT_PERCENT und MIN_GRANT_PERCENT.
Resource Governor
QE-Arbeitsspeicher ist der Arbeitsspeicher, der Resource Governor tatsächlich einschränkt, wenn die einstellungen MIN_MEMORY_PERCENT und MAX_MEMORY_PERCENT verwendet werden. Nachdem Sie Abfragen identifiziert haben, die zu großen Speicherzuweisungen führen, können Sie den von Sitzungen oder Anwendungen belegten Arbeitsspeicher einschränken. Es ist erwähnenswert, dass die default Arbeitsauslastungsgruppe zulässt, dass eine Abfrage bis zu 25 % des Arbeitsspeichers belegt, der für eine SQL Server instance gewährt werden kann. Weitere Informationen finden Sie unter Resource Governor Ressourcenpools und CREATE WORKLOAD GROUP.
Feedback zur adaptiven Abfrageverarbeitung und Speicherzuweisung
SQL Server 2017 wurde das Feedbackfeature zur Speicherzuweisung eingeführt. Es ermöglicht der Abfrageausführungs-Engine, die der Abfrage gewährte Gewährung basierend auf dem vorherigen Verlauf anzupassen. Das Ziel besteht darin, die Größe der Gewährung nach Möglichkeit zu reduzieren oder zu erhöhen, wenn mehr Arbeitsspeicher benötigt wird. Dieses Feature wurde in drei Wellen veröffentlicht:
- Feedback zur Speicherzuweisung im Batchmodus in SQL Server 2017
- Feedback zur Speicherzuweisung im Zeilenmodus in SQL Server 2019
- Feedback zur Speicherzuweisung auf Datenträgerpersistenz mithilfe der Abfragespeicher- und Perzentilzuweisung in SQL Server 2022
Weitere Informationen finden Sie unter Feedback zur Speicherzuweisung. Die Speicherzuweisungsfunktion kann die Größe der Speicherzuweisungen für Abfragen zur Ausführungszeit verringern und somit die Probleme reduzieren, die sich aus umfangreichen Genehmigungsanforderungen ergeben. Wenn dieses Feature vorhanden ist, insbesondere in SQL Server 2019 und höheren Versionen, in denen adaptive Verarbeitung im Zeilenmodus verfügbar ist, bemerken Sie möglicherweise nicht einmal Speicherprobleme, die durch die Abfrageausführung entstehen. Wenn Sie dieses Feature jedoch eingerichtet haben (standardmäßig aktiviert) und weiterhin einen hohen QE-Arbeitsspeicherverbrauch sehen, wenden Sie die zuvor beschriebenen Schritte zum Umschreiben von Abfragen an.
Erhöhen des SQL Server oder des Betriebssystemspeichers
Nachdem Sie die Schritte unternommen haben, um unnötige Speicherzuweisungen für Ihre Abfragen zu reduzieren, benötigt die Workload wahrscheinlich mehr Arbeitsspeicher, wenn weiterhin Probleme mit wenig Arbeitsspeicher auftreten. Erwägen Sie daher, den Arbeitsspeicher für SQL Server mithilfe der max server memory Einstellung zu erhöhen, wenn ausreichend physischer Arbeitsspeicher auf dem System vorhanden ist. Befolgen Sie die Empfehlungen, um etwa 25 % des Arbeitsspeichers für das Betriebssystem und andere Anforderungen zu belassen. Weitere Informationen finden Sie unter Konfigurationsoptionen für den Serverarbeitsspeicher. Wenn auf dem System kein ausreichender Arbeitsspeicher verfügbar ist, sollten Sie den physischen RAM hinzufügen oder den dedizierten RAM für Ihre VM erhöhen, wenn es sich um einen virtuellen Computer handelt.
Interne Speicherzuweisungen
Weitere Informationen zu einigen Internen im Abfrageausführungsspeicher finden Sie im Blogbeitrag Grundlegendes zur Sql Server-Speicherzuweisung .
Erstellen eines Leistungsszenarios mit hoher Speicherzuweisungsauslastung
Schließlich wird im folgenden Beispiel veranschaulicht, wie sie eine große Auslastung des Abfrageausführungsspeichers simulieren und Abfragen einführen, die auf RESOURCE_SEMAPHOREwarten. 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 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 Einstellung für den maximalen Serverarbeitsspeicher Ihres SQL Server auf 1.500 MB zu verringern:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREÖffnen Sie eine Eingabeaufforderung, und ändern Sie das Verzeichnis in den RmL-Hilfsprogrammordner:
cd C:\Program Files\Microsoft Corporation\RMLUtilsVerwenden Sie ostress.exe, um mehrere gleichzeitige Anforderungen für Ihre Test-SQL Server zu erzeugen. 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 probleme mit der Speicherzuweisung zu identifizieren.
Zusammenfassung der Möglichkeiten zum Umgang mit großen Speicherzuweisungen
- Erneutes Generieren von Abfragen.
- 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 reduzieren, wodurch die
JOINAlgorithmen geändert und die Größe der Zuweisungen reduziert oder vollständig entfernt wird. - Verwenden Sie den
OPTIONHinweis (min_grant_percent = XX, max_grant_percent = XX). - Verwenden Sie Resource Governor.
- SQL Server 2017 und 2019 verwenden adaptive Abfrageverarbeitung, sodass der Feedbackmechanismus zur Speicherzuweisung die Größe der Speicherzuweisung zur Laufzeit dynamisch anpassen kann. Dieses Feature kann Probleme mit der Speicherzuweisung überhaupt verhindern.
- Erhöhen Sie SQL Server oder Den Arbeitsspeicher des Betriebssystems.
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für