Verwenden von dynamischen Verwaltungssichten zum Ermitteln der Abfrageleistung und für Troubleshooting

  • 8 Minuten

Dynamische Verwaltungssichten bieten eine programmgesteuerte Möglichkeit, die Aktivität des SQL-Pools in Azure Synapse Analytics mithilfe der Transact-SQL-Sprache zu überwachen. Die bereitgestellten Ansichten ermöglichen Ihnen nicht nur das Troubleshooting und Erkennen von Leistungsengpässen für Workloads, die in Ihrem System ausgeführt werden, sondern sie werden auch von anderen Diensten wie Azure Advisor verwendet, um Empfehlungen zu Azure Synapse Analytics auszusprechen.

Es gibt mehr als 90 dynamische Verwaltungssichten, die für dedizierte SQL-Pools abgefragt werden können, um Informationen zu den folgenden Dienstbereichen zu erhalten:

  • Verbindungsinformationen und Aktivitäten
  • SQL-Ausführungsanforderungen und -Abfragen
  • Index- und Statistikinformationen
  • Ressourcenblockierung und Sperraktivitäten
  • Aktivität des Diensts für Datenverschiebungen
  • Errors

Nachfolgend sehen Sie ein Beispiel, wie die Abfrageausführung von SQL-Pools in Azure Synapse Analytics überwacht wird. Im ersten Schritt werden zunächst die Verbindungen für den Server überprüft, anschließend wird die Aktivität der Abfrageausführung überprüft. 

Überwachen von Verbindungen

Alle Anmeldungen bei Ihrem Data Warehouse werden in sys.dm_pdw_exec_sessions protokolliert. Die Sitzungs-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. Die Anforderungs-ID identifiziert jede Abfrage eindeutig. Sie ist der Primärschlüssel für diese DMV. Die Anforderungs-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 Sitzungs-ID werden alle Abfragen für eine bestimmte Anmeldung angezeigt.

Schritt 1

Der erste Schritt besteht darin, zu ermitteln, welche Abfrage untersucht werden soll.

-- 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 der Abfrage „sys.dm_pdw_waits“ mit dem Typ UserConcurrencyResourceType angezeigt. 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 it a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Schritt 2

Rufen Sie mit der Anforderungs-ID den DSQL-Plan (Distributed SQL, verteiltes SQL) für Abfragen 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, beachten Sie die Spalte operation_type des Abfrageschritts mit langer Laufzeit, und beachten Sie den Schrittindex:

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

Schritt 3

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

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 zu überprüfen, ob eine bestimmte Verteilung mehr Zeit als andere für die Datenverschiebung benötigt.
  • Ü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, 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);

Dynamische Verwaltungssichten (DMVs) enthalten nur 10.000 Zeilen an Daten. In stark ausgelasteten Systemen bedeutet dies, dass die in dieser Tabelle enthaltenen Daten möglicherweise innerhalb von Stunden oder sogar Minuten verloren gehen, da die Daten als FIFO-System (First In, First Out) verwaltet werden. Dies führt dazu, dass Sie möglicherweise bedeutende Informationen verlieren, die Sie bei der Diagnose von Abfrageleistungsproblemen in Ihrem System unterstützen können. In dieser Situation sollten Sie den Abfragespeicher verwenden.

Sie können auch weitere Aspekte von SQL-Pools in Azure Synapse überwachen, darunter die folgenden:

  • Überwachung von Wartevorgängen
  • Überwachung für tempdb
  • Überwachung von Arbeitsspeicher
  • Überwachung des Transaktionsprotokolls
  • Überwachung von PolyBase

Hier können Sie sich die Informationen zur Überwachung dieser Bereiche ansehen.