sys.dm_db_tuning_recommendations (Transact-SQL)

Gilt für: SQL Server 2017 (14.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance

Gibt detaillierte Informationen zu Empfehlungen zur automatischen Optimierung zurück. Weitere Informationen finden Sie unter "Automatische Optimierung"

Hinweise

Zurückgegebene sys.dm_db_tuning_recommendations Informationen werden aktualisiert, wenn das Datenbankmodul potenzielle Abfrageleistungsregression identifiziert und nicht beibehalten wird. Empfehlungen werden nur beibehalten, bis das Datenbankmodul neu gestartet wird. Verwenden Sie die sqlserver_start_time-Spalte in sys.dm_os_sys_info, um die aktuellste Startzeit der Datenbank-Engine zu suchen. Datenbankadministratoren sollten regelmäßig Sicherungskopien der Optimierungsempfehlung erstellen, wenn sie sie nach dem Recycling des Servers beibehalten möchten.

Das currentValue Feld in der state Spalte hat möglicherweise die folgenden Werte:

Das JSON-Dokument in state Spalte enthält den Grund, warum die Empfehlung im aktuellen Zustand beschrieben wird. Werte im Grundfeld können folgendes sein:

Statistiken in der details Spalte zeigen keine Laufzeitplanstatistiken an (z. B. aktuelle CPU-Zeit). Die Empfehlungsdetails werden zum Zeitpunkt der Regressionserkennung verwendet und beschreiben, warum Datenbank-Engine identifizierte Leistungsregression ermittelt wurde. Verwenden Und recommendedPlanId abfragen Sie regressedPlanId Abfragespeicher Katalogansichten, um genaue Laufzeitplanstatistiken zu finden.

Beispiele für die Verwendung von Optimierungsempfehlungen

Beispiel 1

Der folgende Beispielcode ruft das generierte Transact-SQL-Skript ab, das einen guten Plan für eine bestimmte Abfrage erzwingt:

SELECT name,
    reason,
    score,
    JSON_VALUE(details, '$.implementationDetails.script') AS script,
    details.*
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(details, '$.planForceDetails') WITH (
        [query_id] INT '$.queryId',
        regressed_plan_id INT '$.regressedPlanId',
        last_good_plan_id INT '$.recommendedPlanId'
        ) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';

Beispiel 2

Im Folgenden wird das generierte Transact-SQL-Skript aufgerufen, das einen guten Plan für eine bestimmte Abfrage und zusätzliche Informationen über den geschätzten Gewinn erzwingt:

SELECT reason,
    score,
    script = JSON_VALUE(details, '$.implementationDetails.script'),
    planForceDetails.*,
    estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount) * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000,
    error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(Details, '$.planForceDetails') WITH (
        [query_id] INT '$.queryId',
        regressedPlanId INT '$.regressedPlanId',
        recommendedPlanId INT '$.recommendedPlanId',
        regressedPlanErrorCount INT,
        recommendedPlanErrorCount INT,
        regressedPlanExecutionCount INT,
        regressedPlanCpuTimeAverage FLOAT,
        recommendedPlanExecutionCount INT,
        recommendedPlanCpuTimeAverage FLOAT
        ) AS planForceDetails;

Beispiel 3

Im Folgenden wird das generierte Transact-SQL-Skript aufgerufen, das einen guten Plan für jede bestimmte Abfrage und zusätzliche Informationen erzwingt, die den Abfragetext und die in Abfragespeicher gespeicherten Abfragepläne enthalten:

WITH cte_db_tuning_recommendations
AS (
    SELECT reason,
        score,
        query_id,
        regressedPlanId,
        recommendedPlanId,
        current_state = JSON_VALUE(STATE, '$.currentValue'),
        current_state_reason = JSON_VALUE(STATE, '$.reason'),
        script = JSON_VALUE(details, '$.implementationDetails.script'),
        estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount) *
                         (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000,
        error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO')
    FROM sys.dm_db_tuning_recommendations
    CROSS APPLY OPENJSON(Details, '$.planForceDetails') WITH (
            [query_id] INT '$.queryId',
            regressedPlanId INT '$.regressedPlanId',
            recommendedPlanId INT '$.recommendedPlanId',
            regressedPlanErrorCount INT,
            recommendedPlanErrorCount INT,
            regressedPlanExecutionCount INT,
            regressedPlanCpuTimeAverage FLOAT,
            recommendedPlanExecutionCount INT,
            recommendedPlanCpuTimeAverage FLOAT
            )
    )
SELECT qsq.query_id,
    qsqt.query_sql_text,
    dtr.*,
    CAST(rp.query_plan AS XML) AS RegressedPlan,
    CAST(sp.query_plan AS XML) AS SuggestedPlan
FROM cte_db_tuning_recommendations AS dtr
INNER JOIN sys.query_store_plan AS rp
    ON rp.query_id = dtr.query_id
        AND rp.plan_id = dtr.regressedPlanId
INNER JOIN sys.query_store_plan AS sp
    ON sp.query_id = dtr.query_id
        AND sp.plan_id = dtr.recommendedPlanId
INNER JOIN sys.query_store_query AS qsq
    ON qsq.query_id = rp.query_id
INNER JOIN sys.query_store_query_text AS qsqt
    ON qsqt.query_text_id = qsq.query_text_id;

Weitere Informationen zu JSON-Funktionen, die zum Abfragen von Werten in der Empfehlungsansicht verwendet werden können, finden Sie unter JSON-Unterstützung in Datenbank-Engine.

Berechtigungen

Erfordert VIEW SERVER STATE berechtigungen in SQL Server.

Erfordert die VIEW DATABASE STATE Berechtigung für die Datenbank in Azure SQL-Datenbank.

Berechtigungen für SQL Server 2022 und höher

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

Nächste Schritte

• Automatische Optimierung
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• JSON-Unterstützung
• sys.dm_os_sys_info (Transact-SQL)