Optimieren der Leistung mit dem Abfragespeicher
Gilt für: 
SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics
Mit dem SQL Server-Feature „Abfragespeicher“ können Sie Abfragen in Ihrer Workload über die visuelle Oberfläche von SQL Server Management Studio wie auch über T-SQL-Abfragen ermitteln und optimieren. In diesem Artikel erfahren Sie, wie Sie mit Hilfe von Informationen die Abfrageleistung Ihrer Datenbank verbessern können. Dazu gehört auch, wie Sie Abfragen anhand ihrer Nutzungsstatistiken und Zwangspläne identifizieren können. Sie können auch das Feature Abfragespeicherhinweise verwenden, um Abfragen zu bestimmen und deren Abfragepläne zu verbessern, ohne Anwendungscode zu ändern.
- Weitere Informationen dazu, wie diese Daten gesammelt werden, finden Sie unter So sammelt der Abfragespeicher Daten.
- Weitere Informationen zum Konfigurieren und Verwalten mit dem Abfragespeicher finden Sie unter Überwachen der Leistung mit dem Abfragespeicher.
- Informationen zum Betrieb des Abfragespeichers in Azure SQL-Datenbank finden Sie unter Betrieb des Abfragespeichers in Azure SQL-Datenbank.
Beispielabfragen zur Leistungsoptimierung
Der Abfragespeicher protokolliert den Verlauf der Kompilierungs- und Laufzeitmetriken während der Abfrageausführungen, sodass Sie Fragen zu Ihrer Arbeitsauslastung stellen können.
Die folgenden Beispielabfragen können bei Ihrer Untersuchung der Leistungsbaseline und der Abfrageleistung hilfreich sein:
Die letzten für die Datenbank ausgeführten Abfragen
Die letzten n Abfragen, die für die Datenbank in der letzten Stunde ausgeführt wurden:
SELECT TOP 10 qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id,
rs.last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.last_execution_time DESC;
Ausführungsanzahl
Anzahl der Ausführungen für jede Abfrage innerhalb der letzten Stunde:
SELECT q.query_id,
qt.query_text_id,
qt.query_sql_text,
SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY q.query_id,
qt.query_text_id,
qt.query_sql_text
ORDER BY total_execution_count DESC;
Längste durchschnittliche Ausführungszeit
Die Anzahl der Abfragen mit der höchsten durchschnittliche Dauer innerhalb der letzten Stunde:
SELECT TOP 10 ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) /
NULLIF(SUM(rs.count_executions), 0), 2) avg_duration,
SUM(rs.count_executions) AS total_execution_count,
qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id,
GETUTCDATE() AS CurrentUTCTime,
MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id
ORDER BY avg_duration DESC;
Höchste durchschnittliche physische E/A-Leseleistung
Die Anzahl der Abfragen, die die umfangreichsten durchschnittlichen physischen E/A-Lesevorgänge in den letzten 24 Stunden aufwiesen, mit der entsprechenden durchschnittlichen Zeilen- und Ausführungsanzahl:
SELECT TOP 10 rs.avg_physical_io_reads,
qt.query_sql_text,
q.query_id,
qt.query_text_id,
p.plan_id,
rs.runtime_stats_id,
rsi.start_time,
rsi.end_time,
rs.avg_rowcount,
rs.count_executions
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;
Abfragen mit mehreren Plänen
Abfragen mit mehr als einem Plan sind besonders interessant, da sie aufgrund einer Änderung der Planauswahl Kandidaten für eine Regression in der Leistung sein können.
Die folgende Abfrage identifiziert die Abfragen mit der höchsten Anzahl von Plänen innerhalb der letzten Stunde:
SELECT q.query_id,
object_name(object_id) AS ContainingObject,
COUNT(*) AS QueryPlanCount,
STRING_AGG(p.plan_id, ',') plan_ids,
qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY OBJECT_NAME(object_id),
q.query_id,
qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY QueryPlanCount DESC;
Die folgende Abfrage erkennt diese Abfragen sowie alle Pläne innerhalb der letzten Stunde:
WITH Query_MultPlans
AS (
SELECT COUNT(*) AS QueryPlanCount,
q.query_id
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
GROUP BY q.query_id
HAVING COUNT(DISTINCT plan_id) > 1
)
SELECT q.query_id,
object_name(object_id) AS ContainingObject,
query_sql_text,
p.plan_id,
p.query_plan AS plan_xml,
p.last_compile_start_time,
p.last_execution_time
FROM Query_MultPlans AS qm
INNER JOIN sys.query_store_query AS q
ON qm.query_id = q.query_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY q.query_id,
p.plan_id;
Längste Wartezeit
Diese Abfrage gibt die ersten 10 Abfragen mit den höchsten Wartezeiten innerhalb der letzten Stunde zurück:
SELECT TOP 10 qt.query_text_id,
q.query_id,
p.plan_id,
sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
INNER JOIN sys.query_store_plan p
ON ws.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_text_id,
q.query_id,
p.plan_id
ORDER BY sum_total_wait_ms DESC;
Hinweis
In Azure Synapse Analytics werden die Beispielabfragen für den Abfragespeicher in diesem Abschnitt unterstützt. Eine Ausnahme sind Wartestatistiken, die in den dynamischen Verwaltungssicht des Abfragespeicher von Azure Synapse Analytics nicht verfügbar sind.
Abfragen mit kürzlichen Leistungsregressionen
Das folgende Abfragebeispiel gibt alle Abfragen zurück, für die sich die Ausführungszeit in den letzten 48 Stunden aufgrund einer Änderung der Planauswahl verdoppelt hat. Diese Abfrage vergleicht alle Intervalle der Laufzeitstatistiken miteinander:
SELECT qt.query_sql_text,
q.query_id,
qt.query_text_id,
rs1.runtime_stats_id AS runtime_stats_id_1,
rsi1.start_time AS interval_1,
p1.plan_id AS plan_1,
rs1.avg_duration AS avg_duration_1,
rs2.avg_duration AS avg_duration_2,
p2.plan_id AS plan_2,
rsi2.start_time AS interval_2,
rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
ON p2.plan_id = rs2.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi2
ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
AND rsi2.start_time > rsi1.start_time
AND p1.plan_id <> p2.plan_id
AND rs2.avg_duration > 2 * rs1.avg_duration
ORDER BY q.query_id,
rsi1.start_time,
rsi2.start_time;
Wenn Sie alle Leistungsrückgänge sehen möchten (nicht nur die Rückgänge, die mit der Änderung der Planauswahl zusammenhängen), entfernen Sie die Bedingung AND p1.plan_id <> p2.plan_id aus der vorherigen Abfrage.
Abfragen mit historischen Leistungsregressionen
Wenn Sie die letzte Ausführung mit vergangenen Ausführungen vergleichen möchten, können Sie mit der folgenden Abfrage die Ausführung der Abfrage anhand des Ausführungszeitraums vergleichen. In diesem speziellen Beispiel vergleicht die Abfrage die Ausführung in jüngster Zeit (1 Stunde) mit einem älteren Zeitraum (letzter Tag) und identifiziert Ausführungen, die zu additional_duration_workload geführt haben. Diese Metrik wird als Differenz zwischen aktueller durchschnittlicher Ausführung und älterer durchschnittlicher Ausführung multipliziert mit der Anzahl der letzten Ausführungen berechnet. Sie gibt an, wie viel zusätzliche Zeit diese jüngsten Hinrichtungen im Vergleich zur Vergangenheit in Anspruch genommen haben:
--- "Recent" workload - last 1 hour
DECLARE @recent_start_time DATETIMEOFFSET;
DECLARE @recent_end_time DATETIMEOFFSET;
SET @recent_start_time = DATEADD(hour, - 1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();
--- "History" workload
DECLARE @history_start_time DATETIMEOFFSET;
DECLARE @history_end_time DATETIMEOFFSET;
SET @history_start_time = DATEADD(hour, - 24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();
WITH hist AS (
SELECT p.query_id query_id,
ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
SUM(rs.count_executions) AS count_executions,
COUNT(DISTINCT p.plan_id) AS num_plans
FROM sys.query_store_runtime_stats AS rs
INNER JOIN sys.query_store_plan AS p
ON p.plan_id = rs.plan_id
WHERE (
rs.first_execution_time >= @history_start_time
AND rs.last_execution_time < @history_end_time
)
OR (
rs.first_execution_time <= @history_start_time
AND rs.last_execution_time > @history_start_time
)
OR (
rs.first_execution_time <= @history_end_time
AND rs.last_execution_time > @history_end_time
)
GROUP BY p.query_id
),
recent AS (
SELECT p.query_id query_id,
ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
SUM(rs.count_executions) AS count_executions,
COUNT(DISTINCT p.plan_id) AS num_plans
FROM sys.query_store_runtime_stats AS rs
INNER JOIN sys.query_store_plan AS p
ON p.plan_id = rs.plan_id
WHERE (
rs.first_execution_time >= @recent_start_time
AND rs.last_execution_time < @recent_end_time
)
OR (
rs.first_execution_time <= @recent_start_time
AND rs.last_execution_time > @recent_start_time
)
OR (
rs.first_execution_time <= @recent_end_time
AND rs.last_execution_time > @recent_end_time
)
GROUP BY p.query_id
)
SELECT results.query_id AS query_id,
results.query_text AS query_text,
results.additional_duration_workload AS additional_duration_workload,
results.total_duration_recent AS total_duration_recent,
results.total_duration_hist AS total_duration_hist,
ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM (
SELECT hist.query_id AS query_id,
qt.query_sql_text AS query_text,
ROUND(CONVERT(FLOAT, recent.total_duration / recent.count_executions - hist.total_duration / hist.count_executions) * (recent.count_executions), 2) AS additional_duration_workload,
ROUND(recent.total_duration, 2) AS total_duration_recent,
ROUND(hist.total_duration, 2) AS total_duration_hist,
recent.count_executions AS count_executions_recent,
hist.count_executions AS count_executions_hist
FROM hist
INNER JOIN recent
ON hist.query_id = recent.query_id
INNER JOIN sys.query_store_query AS q
ON q.query_id = hist.query_id
INNER JOIN sys.query_store_query_text AS qt
ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);
Erhalten einer stabilen Abfrageleistung
Möglicherweise ist Ihnen bereits aufgefallen, dass SQL Server verschiedene Pläne für mehrmals ausgeführte Abfragen verwendet, was zu unterschiedlicher Ressourcenverwendung und Dauer führt. Mit dem Abfragespeicher können Sie erkennen, wenn die Abfrageleistung abfällt, und den optimalen Plan innerhalb des gewünschten Zeitraums bestimmen. Anschließend können Sie diesen optimalen Plan für zukünftige Abfrageausführungen erzwingen.
Sie können auch abweichende Abfrageleistungen für eine Abfrage mit Parametern ermitteln (entweder automatisch oder manuell parametrisiert). Sie können unter den verschiedenen Plänen den Plan identifizieren, der schnell und ausreichend geeignet für alle oder die meisten Parameterwerte ist, und diesen anschließend erzwingen. Dies sorgt für eine vorhersehbare Leistung für die breitere Gruppe von Benutzerszenarien.
Erzwingen eines Plans für eine Abfrage (Anwenden einer Durchsetzungsrichtlinie)
Wenn ein Plan für eine bestimmte Abfrage erzwungen wird, versucht SQL Server den Plan im Optimierer zu erzwingen. Wenn der Plan nicht erzwungen werden kann, wird ein erweitertes Ereignis ausgelöst, und der Optimierer wird angewiesen, die Optimierung auf die übliche Weise durchzuführen.
EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;
Durch die Verwendung von sp_query_store_force_plan können Sie ausschließlich solche Pläne erzwingen, die vom Abfragespeicher als Plan für diese Abfrage aufgezeichnet wurden. Es stehen für eine Abfrage also nur Pläne zur Verfügung, die bereits zum Ausführen dieser Abfrage verwendet wurden, während der Abfragespeicher aktiv war.
Hinweis
Das Erzwingen von Plänen im Abfragespeicher wird nicht in Azure Synapse Analytics unterstützt.
Erzwingen eines Plans für schnelle Vorwärts- und statische Cursor
Ab SQL Server 2019 (15.x) und Azure SQL-Datenbank (alle Bereitstellungsmodelle) unterstützt der Abfragespeicher die Möglichkeit, Abfrageausführungspläne für schnelle Vorwärtscursor und statische Transact-SQL (und API)-Cursor zu erzwingen. Das Erzwingen kann über sp_query_store_force_plan oder über SQL Server Management Studio-Abfragespeicherberichte erfolgen.
Aufheben der Erzwingung eines Plans für eine Abfrage
Wenn Sie wieder den SQL Server-Abfrageoptimierer verwenden möchten, um den optimalen Abfrageplan zu berechnen, heben Sie mit sp_query_store_unforce_plan das Erzwingen des für die Abfrage ausgewählten Plans auf.
EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;
Zugehöriger Inhalt
- Überwachen der Leistung mit dem Abfragespeicher
- Bewährte Methoden für den Abfragespeicher
- Verwenden des Abfragespeichers mit In-Memory-OLTP
- Query Store Usage Scenarios (Verwendungsszenarien für den Abfragespeicher)
- So werden Daten im Abfragespeicher gesammelt
- Gespeicherte Prozeduren für den Abfragespeicher (Transact-SQL)
- Katalogsichten des Abfragespeichers (Transact-SQL)
- Öffnen des Aktivitätsmonitors (SQL Server Management Studio)
- Live-Abfragestatistik
- Aktivitätsmonitor
- sys.database_query_store_options (Transact-SQL)
- Überwachen und Optimieren der Leistung
- Tools für die Leistungsüberwachung und -optimierung
- Abfragespeicherhinweise
- Optimieren der Datenbank mithilfe der Workload aus deem Abfragespeicher mit dem Datenbankoptimierungsratgeber
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