Überwachen der Workload Ihres dedizierten SQL-Pools von Azure Synapse Analytics mit DMVs

Artikel
06/01/2023

In diesem Artikel wird beschrieben, wie Sie Ihre Workload mit dynamischen Verwaltungssichten (Dynamic Management Views, DMVs) überwachen können, einschließlich der Untersuchung von Abfrageausführungen in einem dedizierten SQL-Pool.

Berechtigungen

Zum Abfragen der DMVs in diesem Artikel benötigen Sie die Berechtigung VIEW DATABASE STATE oder CONTROL. Normalerweise ist VIEW DATABASE STATE die bevorzugte Berechtigung, da sie wesentlich restriktiver ist.

GRANT VIEW DATABASE STATE TO myuser;

Überwachen von Verbindungen

Alle Anmeldungen bei Ihrem Data Warehouse werden in sys.dm_pdw_exec_sessions protokolliert. Diese DMV enthält die letzten 10.000 Anmeldungen. Die session_id ist der Primärschlüssel und wird bei jeder neuen Anmeldung sequenziell zugewiesen.

-- Other Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions where status <> 'Closed' and session_id <> session_id();

Überwachen der Abfrageausführung

Alle im SQL-Pool ausgeführten Abfragen werden in sys.dm_pdw_exec_requests protokolliert. Diese DMV enthält die letzten 10.000 ausgeführten Abfragen. Die request_id identifiziert jede Abfrage eindeutig. Sie ist der Primärschlüssel für diese DMV. Die request_id wird für jede neue Abfrage sequenziell zugewiesen und erhält das Präfix QID für Abfrage-ID. Bei der Abfrage dieser DMV für eine bestimmte session_id werden alle Abfragen für eine bestimmte Anmeldung angezeigt.

Hinweis

Gespeicherte Prozeduren verwenden mehrere Anforderungs-IDs. Anforderungs-IDs werden in sequenzieller Reihenfolge zugewiesen.

Führen Sie folgende Schritte aus, um Abfrageausführungspläne und -zeiten für eine bestimmte Abfrage zu untersuchen.

Schritt 1: Identifizieren der Abfrage, die Sie untersuchen möchten

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

-- Find top 10 queries longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

Notieren Sie sich aus den oben stehenden Abfrageergebnissen die Anforderungs-ID der Abfrage, die Sie untersuchen möchten.

Abfragen im Zustand Angehalten können aufgrund einer großen Anzahl aktiv ausgeführter Abfragen in eine Warteschlange gestellt werden. Diese Abfragen werden auch in sys.dm_pdw_waits angezeigt. Suchen Sie in diesem Fall nach Wartezeiten wie „UserConcurrencyResourceType“. Informationen zu Parallelitätsgrenzwerten finden Sie unter Speicher- und Parallelitätsgrenzwerte oder Ressourcenklassen für die Workloadverwaltung. Abfragen können auch aus anderen Gründen warten, beispielsweise wegen Objektsperren. Wenn Ihre Abfrage auf eine Ressource wartet, finden Sie nähere Informationen unter Untersuchen von Anfragen, die auf Ressourcen warten weiter unten in diesem Artikel.

Vereinfachen Sie die Suche nach einer Abfrage in der Tabelle sys.dm_pdw_exec_requests mithilfe von LABEL, um Ihrer Abfrage einen Kommentar hinzuzufügen, der in der Ansicht sys.dm_pdw_exec_requests gesucht werden kann.

-- Query with Label
SELECT *
FROM sys.tables
OPTION (LABEL = 'My Query')
;

-- Find a query with the Label 'My Query'
-- Use brackets when querying the label column, as it is a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Schritt 2: Untersuchen des Abfrageplans

Rufen Sie mit der Anforderungs-ID den DSQL-Plan (Distributed SQL, verteiltes SQL) der Abfrage aus sys.dm_pdw_request_steps ab.

-- Find the distributed query plan steps for a specific query.
-- Replace request_id with value from Step 1.

SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;

Wenn ein DSQL-Plan mehr Zeit in Anspruch nimmt als erwartet, kann die Ursache ein komplexer Plan mit vielen DSQL-Schritten oder nur ein einziger Schritt sein, der einen langen Zeitraum benötigt. Wenn der Plan viele Schritte mit mehreren Verschiebungen aufweist, erwägen Sie die Optimierung Ihrer Tabellenverteilungen, um Datenverschiebungen zu reduzieren. Im Artikel Tabellenverteilung wird erläutert, warum Daten verschoben werden müssen, um eine Abfrage zu lösen. Außerdem werden in dem Artikel einige Verteilungsstrategien zum Minimieren der Datenverschiebung erläutert.

Um weitere Informationen zu einem Einzelschritt zu erhalten, sehen Sie sich die Spalte operation_type des Abfrageschritts mit langer Laufzeit an, und beachten Sie den Schrittindex:

Fahren Sie für SQL-Vorgänge mit SCHRITT 3 fort: OnOperation, RemoteOperation, ReturnOperation.
Fahren Sie für Datenverschiebungsvorgängemit SCHRITT 4 fort: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.

Schritt 3: Untersuchen von SQL auf den verteilten Datenbanken

Verwenden Sie die Anforderungs-ID und den Schrittindex, um Informationen aus sys.dm_pdw_sql_requests abzurufen. Das Ergebnis enthält Informationen zur Ausführung des Abfrageschritts in allen verteilten Datenbanken.

-- Find the distribution run times for a SQL step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID####' AND step_index = 2;

Wenn der Abfrageschritt ausgeführt wird, können Sie mit DBCC PDW_SHOWEXECUTIONPLAN aus dem Cache des SQL Server-Plans den berechneten SQL Server-Ausführungsplan für den in einer bestimmten Verteilung ausgeführten Schritt abrufen.

-- Find the SQL Server execution plan for a query running on a specific SQL pool or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

Schritt 4: Untersuchen der Datenverschiebung auf den verteilten Datenbanken

Verwenden Sie die Anforderungs-ID und den Schrittindex, um Informationen zu einem Datenverschiebungsschritt, der für jede Verteilung ausgeführt wird, aus sys.dm_pdw_dms_workers abzurufen.

-- Find information about all the workers completing a Data Movement Step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID####' AND step_index = 2;

Überprüfen Sie die Spalte total_elapsed_time, um festzustellen, ob das Verschieben von Daten in einer bestimmten Verteilung erheblich länger dauert als in anderen Verteilungen.
Überprüfen Sie für die Verteilung mit langer Laufzeit die Spalte rows_processed, um festzustellen, ob die Anzahl der Zeilen, die von dieser Verteilung verschoben werden, beträchtlich größer als bei den anderen ist. Falls ja, kann dies auf eine Ungleichmäßigkeit der zugrunde liegenden Daten hinweisen. Eine Ursache für Datenschiefe ist die Verteilung einer Spalte mit vielen NULL-Werten (deren Zeilen alle in dieselbe Verteilung eingefügt werden). Sie vermeiden langsame Abfragen, indem Sie die Verteilung dieser Spaltentypen vermeiden oder indem Sie die Abfrage nach Möglichkeit filtern, um NULL-Werte auszuschließen.

Wird die Abfrage gerade ausgeführt, können Sie mit DBCC PDW_SHOWEXECUTIONPLAN aus dem Cache des SQL Server-Plans den berechneten SQL Server-Ausführungsplan für den derzeit ausgeführten SQL-Schritt innerhalb einer bestimmten Verteilung abrufen.

-- Find the SQL Server estimated plan for a query running on a specific SQL pool Compute or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(55, 238);

Überwachen von wartenden Abfragen

Wenn Sie feststellen, dass Ihre Abfrage keine Fortschritte erzielt, weil sie auf eine Ressource wartet, können Sie mit folgender Abfrage alle Ressourcen anzeigen, auf die eine Abfrage wartet.

-- Find queries
-- Replace request_id with value from Step 1.

SELECT waits.session_id,
      waits.request_id,
      requests.command,
      requests.status,
      requests.start_time,
      waits.type,
      waits.state,
      waits.object_type,
      waits.object_name
FROM   sys.dm_pdw_waits waits
   JOIN  sys.dm_pdw_exec_requests requests
   ON waits.request_id=requests.request_id
WHERE waits.request_id = 'QID####'
ORDER BY waits.object_name, waits.object_type, waits.state;

Wenn die Abfrage aktiv auf Ressourcen einer anderen Abfrage wartet, lautet der Status AcquireResources. Wenn die Abfrage über alle erforderlichen Ressourcen verfügt, ist der Status Granted.

Überwachen von tempdb

Die Datenbank tempdb wird zum Speichern von Zwischenergebnissen während der Abfrageausführung verwendet. Eine hohe Auslastung der tempdb-Datenbank kann zu einer schwachen Abfrageleistung führen. Für jede konfigurierte DW100c werden 399 GB Speicherplatz für tempdb zugewiesen (DW1000c bietet insgesamt 3,99 TB Speicherplatz für tempdb). Nachstehend finden Sie Tipps zur Überwachung der tempdb-Auslastung und zur Verringerung der tempdb-Auslastung in Ihren Abfragen.

Überwachen von tempdb mit Ansichten

Wenn Sie die tempdb-Auslastung überwachen möchten, installieren Sie zuerst die Ansicht microsoft.vw_sql_requests aus dem Microsoft-Toolkit für SQL-Pool. Anschließend können Sie die folgende Abfrage ausführen, um die tempdb-Auslastung pro Knoten für alle ausgeführten Abfragen anzuzeigen:

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    exs.login_name AS 'LoginName',
    DB_NAME(ssu.database_id) AS 'DatabaseName',
    (es.memory_usage * 8) AS 'MemoryUsage (in KB)',
    (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
    (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
    (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
    (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)',
    CASE es.is_user_process
    WHEN 1 THEN 'User Session'
    WHEN 0 THEN 'System Session'
    END AS 'SessionType',
    es.row_count AS 'RowCount'
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
    INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
    INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
    INNER JOIN microsoft.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
    LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
    LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

Hinweis

Die Datenverschiebung verwendet die tempdb. Um die Nutzung von tempdb während der Datenverschiebung zu verringern, stellen Sie sicher, dass Ihre Tabelle eine Verteilungsstrategie verwendet, die Daten gleichmäßig verteilt. Verwenden Sie Azure Synapse SQL Distribution Advisor, um Empfehlungen für die für Ihre Workloads geeignete Verteilungsmethode zu erhalten. Verwenden Sie das Azure Synapse Toolkit, um tempdb mithilfe von T-SQL-Abfragen zu überwachen.

Wenn Sie eine Abfrage haben, die eine große Menge an Arbeitsspeicher verbraucht, oder wenn eine Fehlermeldung im Zusammenhang mit der Zuordnung von tempdb angezeigt wird, kann dies auf der Ausführung einer sehr umfangreichen CREATE TABLE AS SELECT (CTAS)- oder INSERT SELECT-Anweisung beruhen, bei der im letzten Datenverschiebungsvorgang ein Fehler aufgetreten ist. Dies kann im Plan für verteilte Abfragen normalerweise als „ShuffleMove“-Vorgang direkt vor der letzten INSERT SELECT-Anweisung identifiziert werden. Verwenden Sie sys.dm_pdw_request_steps zum Überwachen von ShuffleMove-Vorgängen.

Die häufigste Minderung besteht darin, Ihre CTAS- oder INSERT SELECT-Anweisung in mehrere Load-Anweisungen aufzuteilen, damit das Datenvolumen das tempdb-Limit von 399 GB pro 100DWUc nicht überschreitet. Sie können die Größe Ihres Clusters auch höherskalieren, um Ihren verfügbaren tempdb-Speicherplatz zu erhöhen.

Zusätzlich zu den CTAS- und INSERT SELECT-Anweisungen können große, komplexe Abfragen, die mit unzureichendem Speicher ausgeführt werden, in tempdb gelangen, wodurch Abfragen fehlschlagen. Erwägen Sie die Verwendung einer größeren Ressourcenklasse, um eine Auslagerung in tempdb zu vermeiden.

Überwachen des Arbeitsspeichers

Der Arbeitsspeicher kann die Hauptursache für Probleme in Verbindung mit geringer Leistung und unzureichendem Arbeitsspeicher sein. Ziehen Sie die Skalierung Ihres Data Warehouse in Betracht, wenn Sie feststellen, dass die Speicherauslastung von SQL Server beim Ausführen der Abfrage die Grenzwerte erreicht.

Die folgende Abfrage gibt die Speicherauslastung von SQL Server und die Speicherauslastung pro Knoten zurück:

-- Memory consumption
SELECT
  pc1.cntr_value as Curr_Mem_KB,
  pc1.cntr_value/1024.0 as Curr_Mem_MB,
  (pc1.cntr_value/1048576.0) as Curr_Mem_GB,
  pc2.cntr_value as Max_Mem_KB,
  pc2.cntr_value/1024.0 as Max_Mem_MB,
  (pc2.cntr_value/1048576.0) as Max_Mem_GB,
  pc1.cntr_value * 100.0/pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
-- pc1: current memory
sys.dm_pdw_nodes_os_performance_counters AS pc1
-- pc2: total memory allowed for this SQL instance
JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2
ON pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE
pc1.counter_name = 'Total Server Memory (KB)'
AND pc2.counter_name = 'Target Server Memory (KB)'

Überwachen der Größe von Transaktionsprotokollen

Die folgende Abfrage gibt die Größe von Transaktionsprotokollen für jede Verteilung zurück. Wenn eine der Protokolldateien 160 GB erreicht, sollten Sie Ihre Instanz eventuell zentral hochskalieren oder die Transaktionsgröße beschränken.

-- Transaction log size
SELECT
  instance_name as distribution_db,
  cntr_value*1.0/1048576 as log_file_size_used_GB,
  pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'

Überwachen des Rollbacks von Transaktionsprotokollen

Wenn bei Ihren Abfragen Fehler auftreten oder deren Verarbeitung sehr lange dauert, können Sie überprüfen und überwachen, ob Sie über Rollbacks von Transaktionen verfügen.

-- Monitor rollback
SELECT
    SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END),
    t.pdw_node_id,
    nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]

Überwachen der PolyBase-Last

Die folgende Abfrage stellt eine ungefähre Schätzung des Fortschritts Ihrer Last bereit. Die Abfrage zeigt nur Dateien an, die zurzeit verarbeitet werden.

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

Überwachen von Abfrageblockierungen

Die folgende Abfrage stellt die wichtigsten 500 blockierten Abfragen in der Umgebung bereit.

--Collect the top blocking
SELECT
    TOP 500 waiting.request_id AS WaitingRequestId,
    waiting.object_type AS LockRequestType,
    waiting.object_name AS ObjectLockRequestName,
    waiting.request_time AS ObjectLockRequestTime,
    blocking.session_id AS BlockingSessionId,
    blocking.request_id AS BlockingRequestId
FROM
    sys.dm_pdw_waits waiting
    INNER JOIN sys.dm_pdw_waits blocking
    ON waiting.object_type = blocking.object_type
    AND waiting.object_name = blocking.object_name
WHERE
    waiting.state = 'Queued'
    AND blocking.state = 'Granted'
ORDER BY
    ObjectLockRequestTime ASC;

Abrufen von Abfragetext von wartenden und blockierenden Abfragen

Die folgende Abfrage liefert den Abfragetext und den Bezeichner für die wartenden und blockierenden Abfragen zur einfachen Problembehandlung.

-- To retrieve query text from waiting and blocking queries

SELECT waiting.session_id AS WaitingSessionId,
       waiting.request_id AS WaitingRequestId,
       COALESCE(waiting_exec_request.command,waiting_exec_request.command2) AS WaitingExecRequestText,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       waiting.object_name AS Blocking_Object_Name,
       waiting.object_type AS Blocking_Object_Type,
       waiting.type AS Lock_Type,
       waiting.request_time AS Lock_Request_Time,
       datediff(ms, waiting.request_time, getdate())/1000.0 AS Blocking_Time_sec
FROM sys.dm_pdw_waits waiting
       INNER JOIN sys.dm_pdw_waits blocking
       ON waiting.object_type = blocking.object_type
       AND waiting.object_name = blocking.object_name
       INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request
       ON blocking.request_id = blocking_exec_request.request_id
       INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request
       ON waiting.request_id = waiting_exec_request.request_id
WHERE waiting.state = 'Queued'
       AND blocking.state = 'Granted'
ORDER BY Lock_Request_Time DESC;

Nächste Schritte

Weitere Informationen zu DMVs finden Sie unter Systemsichten.