Optimieren der Leistung mit dem Abfragespeicher

Artikel
06/11/2024

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics

Das FEATURE "SQL Server Abfragespeicher" bietet Ihnen die Möglichkeit, Abfragen in Ihrer Workload über die visuelle Sql Server Management Studio-Schnittstelle und über T-SQL-Abfragen zu ermitteln und zu optimieren. In diesem Artikel wird erläutert, wie Sie umsetzbare Informationen ergreifen können, um die Abfrageleistung in Ihrer Datenbank zu verbessern, einschließlich der Identifizierung von Abfragen basierend auf ihren Nutzungsstatistiken und erzwingen von Plänen. Sie können auch das Feature Abfragespeicher Hinweise verwenden, um Abfragen zu identifizieren und ihre Abfragepläne zu gestalten, ohne den Anwendungscode zu ändern.

Weitere Informationen dazu, wie diese Daten gesammelt werden, finden Sie unter How Abfragespeicher collection data.
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 der Leistungsbasisplan- und Abfrageleistungsuntersuchung hilfreich sein:

Die letzten für die Datenbank ausgeführten Abfragen

Die letzten n Abfragen, die innerhalb der letzten Stunde in der Datenbank ausgeführt werden:

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-Lesevorgänge

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 identifiziert diese Abfragen zusammen mit allen Plänen 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 höchsten 10 Abfragen mit der höchsten Wartezeit für die letzte 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 Abfragespeicher Beispielabfragen in diesem Abschnitt mit Ausnahme von Wartezeitstatistiken unterstützt, die in azure Synapse Analytics Abfragespeicher DMVs nicht verfügbar sind.

Abfragen mit kürzlichen Leistungsregressionen

Im folgenden Abfragebeispiel werden alle Abfragen zurückgegeben, für die die Ausführungszeit in den letzten 48 Stunden aufgrund einer Planauswahländerung verdoppelt wurde. 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 Leistungsregressionen anzeigen möchten (nicht nur Regressionen im Zusammenhang mit der Planauswahländerung), 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 der verlaufsbezogenen Ausführung vergleichen möchten, vergleicht die folgende Abfrage die Abfrageausführung basierend auf dem Ausführungszeitraum. 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. Es stellt dar, wie viel zusätzliche Dauer diese letzten Ausführungen eingeführt wurden, verglichen mit dem Verlauf:

--- "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);

Verwalten der Abfrageleistungsstabilität

Bei Abfragen, die mehrmals ausgeführt werden, stellen Sie möglicherweise fest, dass SQL Server unterschiedliche Pläne verwendet, was zu unterschiedlicher Ressourcenauslastung 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 die inkonsistente Abfrageleistung für eine Abfrage mit Parametern (entweder autoparameterisiert oder manuell parametrisiert) identifizieren. Unter verschiedenen Plänen können Sie den Plan identifizieren, der schnell und optimal genug für alle oder die meisten Parameterwerte ist, und diesen Plan 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 das Erzwingen eines Plans fehlschlägt, wird ein erweitertes Ereignis ausgelöst, und der Optimierer wird angewiesen, auf normale Weise zu optimieren.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

Bei Verwendung sp_query_store_force_plankönnen Sie nur pläne erzwingen, die von Abfragespeicher als Plan für diese Abfrage aufgezeichnet werden. Mit anderen Worten, die einzigen Pläne, die für eine Abfrage verfügbar sind, sind Pläne, die bereits zum Ausführen dieser Abfrage verwendet wurden, während Abfragespeicher aktiv war.

Hinweis

Das Erzwingen von Plänen in Abfragespeicher wird in Azure Synapse Analytics nicht unterstützt.

Erzwingen eines Plans für schnelle Vorwärts- und statische Cursor

In SQL Server 2019 (15.x) und höheren Versionen und Azure SQL-Datenbank (alle Bereitstellungsmodelle) unterstützt Abfragespeicher die Möglichkeit, Abfrageausführungspläne für schnelle und statische Transact-SQL- und API-Cursor zu erzwingen. Das Erzwingen wird über sp_query_store_force_plan oder über SQL Server Management Studio Abfragespeicher Berichte unterstützt.

Aufheben der Erzwingung eines Plans für eine Abfrage

Wenn Sie sich erneut auf den SQL Server-Abfrageoptimierer verlassen möchten, um den optimalen Abfrageplan zu berechnen, verwenden Sie diese Option sp_query_store_unforce_plan , um den für die Abfrage ausgewählten Plan aufzuheben.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

Teilen über

Optimieren der Leistung mit dem Abfragespeicher

Beispielabfragen zur Leistungsoptimierung

Die letzten für die Datenbank ausgeführten Abfragen

Ausführungsanzahl

Längste durchschnittliche Ausführungszeit

Höchste durchschnittliche physische E/A-Lesevorgänge

Abfragen mit mehreren Plänen

Längste Wartezeit

Abfragen mit kürzlichen Leistungsregressionen

Abfragen mit historischen Leistungsregressionen

Verwalten der Abfrageleistungsstabilität

Erzwingen eines Plans für eine Abfrage (Anwenden einer Durchsetzungsrichtlinie)

Erzwingen eines Plans für schnelle Vorwärts- und statische Cursor

Aufheben der Erzwingung eines Plans für eine Abfrage

Feedback

Feedback

Zusätzliche Ressourcen

Teilen über

Optimieren der Leistung mit dem Abfragespeicher

Beispielabfragen zur Leistungsoptimierung

Die letzten für die Datenbank ausgeführten Abfragen

Ausführungsanzahl

Längste durchschnittliche Ausführungszeit

Höchste durchschnittliche physische E/A-Lesevorgänge

Abfragen mit mehreren Plänen

Längste Wartezeit

Abfragen mit kürzlichen Leistungsregressionen

Abfragen mit historischen Leistungsregressionen

Verwalten der Abfrageleistungsstabilität

Erzwingen eines Plans für eine Abfrage (Anwenden einer Durchsetzungsrichtlinie)

Erzwingen eines Plans für schnelle Vorwärts- und statische Cursor

Aufheben der Erzwingung eines Plans für eine Abfrage

Zugehöriger Inhalt

Feedback

Feedback

Zusätzliche Ressourcen