Freigeben über

sys.dm_exec_query_memory_grants (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Gibt Informationen zu allen abfragen zurück, die angefordert wurden und auf eine Speichererteilung warten oder eine Speichererteilung erhalten haben. Abfragen, die keine Speichererteilung erfordern, werden in dieser Ansicht nicht angezeigt. Beispielsweise verfügen Sortier- und Hashbeitrittsvorgänge über Speichererteilungen für die Abfrageausführung, während Abfragen ohne Eine ORDER BY Klausel keine Speichererteilung aufweisen.

In Azure SQL-Datenbank können dynamische Verwaltungssichten keine Informationen verfügbar machen, die sich auf den Datenbankeinschluss auswirken würden oder die sich auf andere Datenbanken beziehen, auf die der Benutzer Zugriff hat. Um diese Informationen nicht verfügbar zu machen, wird jede Zeile mit Daten, die nicht zum verbundenen Mandanten gehören, herausgefiltert. Darüber hinaus werden die Werte in den Spalten scheduler_id, wait_order, , group_id pool_idgefiltert; der Spaltenwert wird auf NULL festgelegt.

Hinweis

Um dies von Azure Synapse Analytics oder Analytics Platform System (PDW) aufzurufen, verwenden Sie den Namen sys.dm_pdw_nodes_exec_query_memory_grants. Diese Syntax wird vom serverlosen SQL-Pool in Azure Synapse Analytics nicht unterstützt.

Spaltenname Datentyp Beschreibung
session_id smallint ID (SPID) der Sitzung, in der die Abfrage ausgeführt wird.
request_id int ID der Anforderung. Ist im Kontext der Sitzung eindeutig.
scheduler_id int ID des Zeitplanungsmoduls, der diese Abfrage plant.
Dop smallint Grad an Parallelität für diese Abfrage.
request_time datetime Datum und Uhrzeit, zu der die Abfrage die Arbeitsspeicherzuweisung angefordert hat.
grant_time datetime Datum und Uhrzeit, zu der die Arbeitsspeicherzuweisung für die Abfrage erfolgt ist. NULL, wenn noch kein Arbeitsspeicher zugewiesen wurde.
requested_memory_kb bigint Insgesamt angeforderter Arbeitsspeicher in Kilobytes.
granted_memory_kb bigint Insgesamt tatsächlich zugewiesener Arbeitsspeicher in Kilobytes. Kann NULL sein, wenn noch kein Arbeitsspeicher zugewiesen wurde. Bei einer typischen Situation sollte dieser Wert mit dem wert übereinstimmen requested_memory_kb. Für die Indexerstellung wird möglicherweise vom Server bei Bedarf weiterer Arbeitsspeicher über den ursprünglich zugewiesenen hinaus zugelassen.
required_memory_kb bigint Minimaler Arbeitsspeicher in Kilobyte, der erforderlich ist, um diese Abfrage auszuführen. requested_memory_kb ist identisch oder größer als dieser Betrag.
used_memory_kb bigint Der zu diesem Zeitpunkt verwendete physische Arbeitsspeicher in Kilobytes.
max_used_memory_kb bigint Der bis zu diesem Zeitpunkt verwendete maximale physische Arbeitsspeicher in Kilobytes.
query_cost float Die geschätzten Abfragekosten.
timeout_sec int Timeout in Sekunden, nach dem die Abfrage die Anforderung der Arbeitsspeicherzuweisung aufgibt.
resource_semaphore_id smallint Nicht eindeutige ID des Ressourcensemaphors, auf das die Abfrage wartet.

Hinweis: Diese ID ist in Versionen von SQL Server eindeutig, die älter als SQL Server 2008 (10.0.x) sind. Diese Änderung kann die Abfrageausführung bei der Problembehandlung beeinflussen. Weitere Informationen finden Sie im Abschnitt "Hinweise" weiter unten in diesem Artikel.
queue_id smallint ID der Warteschlange, in der die Abfrage auf Arbeitsspeicherzuweisungen wartet. NULL, wenn der Arbeitsspeicher bereits zugewiesen wurde.
wait_order int Sequenzielle Reihenfolge des Wartens von Abfragen innerhalb der angegebenen queue_id. Dieser Wert kann sich für eine bestimmte Abfrage ändern, wenn andere Abfragen Speichererteilungen oder Timeouts erhalten. NULL, wenn der Arbeitsspeicher bereits gewährt wird.
is_next_candidate bit Kandidat für die nächste Arbeitsspeicherzuweisung.

1 = Ja

0 = Nein

NULL = Arbeitsspeicher wurde bereits zugewiesen
wait_time_ms bigint Wartezeit in Millisekunden. NULL, wenn der Arbeitsspeicher bereits zugewiesen wurde.
plan_handle varbinary(64) Bezeichner für diesen Abfrageplan. Wird sys.dm_exec_query_plan verwendet, um den tatsächlichen XML-Plan zu extrahieren.
sql_handle varbinary(64) Bezeichner für Transact-SQL-Text für diese Abfrage. Dient sys.dm_exec_sql_text zum Abrufen des tatsächlichen Transact-SQL-Texts.
group_id int ID für die Arbeitsauslastungsgruppe, in der diese Abfrage ausgeführt wird.
pool_id int ID des Ressourcenpools, zu dem die Arbeitsauslastungsgruppe gehört.
is_small tinyint Der Wert 1 gibt an, dass diese Zuweisung das kleine Ressourcensemaphor verwendet. Der Wert 0 gibt an, dass ein normales Semaphor verwendet wird.
ideal_memory_kb bigint Größe der Arbeitsspeicherzuweisung in Kilobyte (KB), um alles in den physischen Speicher aufzunehmen. Dieser Wert basiert auf der Kardinalitätsschätzung.
pdw_node_id int Der Bezeichner für den Knoten, auf dem sich diese Verteilung befindet.

Gilt für: Azure Synapse Analytics, Analytics Platform System (PDW)
reserved_worker_count bigint Anzahl reservierter Arbeitsthreads.

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank
used_worker_count bigint Die Anzahl der zu diesem Zeitpunkt verwendeten Arbeitsthreads .

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank
max_used_worker_count bigint Maximale Anzahl von Arbeitsthreads, die bis zu diesem Moment verwendet wurden.

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank
reserved_node_bitmap bigint Bitmap von NUMA-Knoten, in denen Arbeitsthreads reserviert sind.

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank

Berechtigungen

Auf SQL Server ist die VIEW SERVER STATE-Berechtigung erforderlich.
In der Azure SQL-Datenbank ist die VIEW DATABASE STATE-Berechtigung für die Datenbank erforderlich.

Berechtigungen für SQL Server 2022 und höher

Erfordert die VIEW SERVER PERFORMANCE STATE-Berechtigung auf dem Server.

Hinweise

Abfragen, die dynamische Verwaltungsansichten verwenden, die enthalten ORDER BY oder Aggregate verwenden, erhöhen möglicherweise den Arbeitsspeicherverbrauch und tragen somit zu dem Problem bei, das sie beheben.

Mit der Ressourcenkontrollen-Funktion kann ein Datenbankadministrator Serverressourcen auf Ressourcenpools verteilen, bis zu maximal 64 Pools. Ab SQL Server 2008 (10.0.x) verhält sich jeder Pool wie eine kleine unabhängige Serverinstanz und erfordert zwei Semaphore. Die Anzahl der Zeilen, die zurückgegeben sys.dm_exec_query_resource_semaphores werden, kann bis zu 20 Mal größer sein als die Zeilen, die in SQL Server 2005 (9,x) zurückgegeben werden.

Beispiele

Ein typisches Debugszenario für das Abfragetimeout kann Folgendes untersuchen:

  • Überprüfen Sie den gesamten Systemspeicherstatus mithilfe von sys.dm_os_memory_clerks, sys.dm_os_sys_info und verschiedenen Leistungsindikatoren.

  • Überprüfen Sie, wo type = 'MEMORYCLERK_SQLQERESERVATIONS'die Speicherreservierungen für sys.dm_os_memory_clerks die Abfrageausführung ausgeführt werden.

  • Überprüfen Sie, ob Abfragen 1 auf Finanzhilfen warten, indem Sie Folgendes verwendensys.dm_exec_query_memory_grants:

    --Find all queries waiting in the memory queue  
SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;

    1 In diesem Szenario ist der Wartetyp in der Regel RESOURCE_SEMAPHORE. Weitere Informationen finden Sie unter sys.dm_os_wait_stats (Transact-SQL).

  • Suchcache für Abfragen mit Speichererteilungen mithilfe von sys.dm_exec_cached_plans (Transact-SQL) und sys.dm_exec_query_plan (Transact-SQL)

    -- retrieve every query plan from the plan cache  
USE master;  
GO  
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO

  • Wenn eine Auslaufabfrage vermutet wird, überprüfen Sie den Showplan in der query_plan Spalte aus sys.dm_exec_query_plan und abfragebatch text aus sys.dm_exec_sql_text. Untersuchen Sie arbeitsspeicherintensive Abfragen, die zurzeit ausgeführt werden, und verwenden Sie sys.dm_exec_requests.

    --Active requests with memory grants
SELECT
--Session data 
  s.[session_id], s.open_transaction_count
--Memory usage
, r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb     
--Query 
, query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan, request_row_count = r.row_count, session_row_count = s.row_count
--Session history and status
, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads, session_status = s.[status], request_status = r.status
--Session connection information
, s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests AS r 
    ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg 
    ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib 
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp 
WHERE mg.granted_memory_kb > 0
ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;
GO

Siehe auch