sys.dm_db_tuning_recommendations (Transact-SQL)

Artikel
06/06/2023

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.

Weitere Informationen finden Sie unter Überwachung und Leistungsoptimierung in Azure SQL Datenbank und Azure SQL Managed Instance.

In Azure SQL Datenbank können dynamische Verwaltungsansichten keine Informationen verfügbar machen, die sich auf die Datenbankeindämmung auswirken würden, oder Informationen zu anderen Datenbanken verfügbar machen, auf die der Benutzer Zugriff hat. Um diese Informationen nicht verfügbar zu machen, wird jede Zeile herausgefiltert, die Daten enthält, die nicht zum verbundenen Mandanten gehören.

Spaltenname	Datentyp	Beschreibung
name	nvarchar(4000)	Eindeutiger Name der Empfehlung.
type	nvarchar(4000)	Der Name der Automatischen Optimierungsoption, die die Empfehlung erzeugt hat, z. B. `FORCE_LAST_GOOD_PLAN`
reason	nvarchar(4000)	Grund, warum diese Empfehlung gegeben wurde.
valid_since	datetime2	Diese Empfehlung wurde zum ersten Mal generiert.
last_refresh	datetime2	Das letzte Mal, wenn diese Empfehlung generiert wurde.
state	nvarchar(4000)	JSON-Dokument, das den Status der Empfehlung beschreibt. Folgende Felder sind verfügbar: - `currentValue` - aktueller Status der Empfehlung. - `reason` - Konstante, die beschreibt, warum sich die Empfehlung im aktuellen Zustand befindet.
is_executable_action	bit	1 = Die Empfehlung kann für die Datenbank über ein Transact-SQL-Skript ausgeführt werden. 0 = Die Empfehlung kann nicht für die Datenbank ausgeführt werden (z. B. nur Informationen oder revertierte Empfehlung)
is_revertable_action	bit	1 = Die Empfehlung kann automatisch von der Datenbank-Engine überwacht und revertiert werden. 0 = Die Empfehlung kann nicht automatisch überwacht und wiederhergestellt werden. Die meisten ausführbaren Aktionen sind revertierbar.
execute_action_start_time	datetime2	Datum der Anwendung der Empfehlung.
execute_action_duration	time	Dauer der Ausführungsaktion.
execute_action_initiated_by	nvarchar(4000)	`User` = Der Vom Benutzer manuell erzwungene Plan in der Empfehlung. `System` = Automatisch angewendete Empfehlung des Systems.
execute_action_initiated_time	datetime2	Datum der Anwendung der Empfehlung.
revert_action_start_time	datetime2	Datum, an dem die Empfehlung wiederhergestellt wurde.
revert_action_duration	time	Dauer der rückgängig machen Aktion.
revert_action_initiated_by	nvarchar(4000)	`User` = Vom Benutzer manuell erzwungener empfohlener Plan. `System` = Empfehlung vom System automatisch zurückgesetzt.
revert_action_initiated_time	datetime2	Datum, an dem die Empfehlung wiederhergestellt wurde.
score	int	Geschätzter Wert/Effekt für diese Empfehlung auf der Skala 0-100 (je größer, desto besser)
details	nvarchar(max)	JSON-Dokument, das weitere Details zur Empfehlung enthält. Folgende Felder sind verfügbar: `planForceDetails` - `queryId` – query_id der zurückgefragten Abfrage. - `regressedPlanId` - plan_id des zurückgeschrittenen Plans. - `regressedPlanExecutionCount` – Anzahl der Ausführungen der Abfrage mit einem zurückgestellten Plan, bevor die Regression erkannt wird. - `regressedPlanAbortedCount` - Anzahl der erkannten Fehler während der Ausführung des zurückgezahlten Plans. - `regressedPlanCpuTimeAverage` – Durchschnittliche CPU-Zeit (in Mikrosekunden), die von der zurückgeschrittenen Abfrage verbraucht wird, bevor die Regression erkannt wird. - `regressedPlanCpuTimeStddev` - Standardabweichung der CPU-Zeit, die von der zurückgeschrittenen Abfrage verbraucht wird, bevor die Regression erkannt wird. - `recommendedPlanId` - plan_id des Plans, der erzwungen werden soll. - `recommendedPlanExecutionCount`– Anzahl der Ausführungen der Abfrage mit dem Plan, die erzwungen werden soll, bevor die Regression erkannt wird. - `recommendedPlanAbortedCount` - Anzahl der erkannten Fehler während der Ausführung des Plans, die erzwungen werden sollen. - `recommendedPlanCpuTimeAverage` – Durchschnittliche CPU-Zeit (in Mikrosekunden), die von der Abfrage verbraucht wird, die mit dem Plan ausgeführt wird, der erzwungen werden soll (berechnet, bevor die Regression erkannt wird). - `recommendedPlanCpuTimeStddev` Standardabweichung der CPU-Zeit, die von der zurückgestellten Abfrage verbraucht wird, bevor die Regression erkannt wird. `implementationDetails` - `method` - Die Methode, die verwendet werden soll, um die Regression zu korrigieren. Der Wert ist immer `TSql`. - `script` – Transact-SQL-Skript, das ausgeführt werden soll, um den empfohlenen Plan zu erzwingen.

Bemerkungen

Von zurückgegebene sys.dm_db_tuning_recommendations Informationen werden aktualisiert, wenn die Datenbank-Engine eine potenzielle Abfrageleistungsregression identifiziert und nicht beibehalten wird. Empfehlungen werden nur beibehalten, bis die Datenbank-Engine 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 Serverrecycling beibehalten möchten.

Das currentValue Feld in der state Spalte kann die folgenden Werte aufweisen:

Status	BESCHREIBUNG
`Active`	Die Empfehlung ist aktiv und noch nicht angewendet. Der Benutzer kann empfehlungsskripts verwenden und es manuell ausführen.
`Verifying`	Die Empfehlung wird von Datenbank-Engine angewendet, und der interne Überprüfungsprozess vergleicht die Leistung des erzwungenen Plans mit dem zurückgesetzten Plan.
`Success`	Die Empfehlung wurde erfolgreich angewendet.
`Reverted`	Die Empfehlung wird zurückgesetzt, da es keine signifikanten Leistungssteigerungen gibt.
`Expired`	Die Empfehlung ist abgelaufen und kann nicht mehr angewendet werden.

Das JSON-Dokument in state der Spalte enthält den Grund, der beschreibt, warum die Empfehlung im aktuellen Zustand ist. Die Werte im Feld "Reason" können wie folgt sein:

`Reason`	BESCHREIBUNG
`SchemaChanged`	Die Empfehlung ist abgelaufen, weil das Schema einer Tabelle, auf die verwiesen wird, geändert wird. Eine neue Empfehlung wird erstellt, wenn eine neue Abfrageplanregression für das neue Schema erkannt wird.
`StatisticsChanged`	Die Empfehlung ist aufgrund der Statistikänderung für eine Tabelle, auf die verwiesen wird, abgelaufen. Eine neue Empfehlung wird erstellt, wenn basierend auf neuen Statistiken eine neue Abfrageplanregression erkannt wird.
`ForcingFailed`	Der empfohlene Plan kann für eine Abfrage nicht erzwungen werden. Suchen Sie in `last_force_failure_reason` der ansicht sys.query_store_plan , um die Ursache des Fehlers zu ermitteln.
`AutomaticTuningOptionDisabled`	`FORCE_LAST_GOOD_PLAN` -Option wird vom Benutzer während des Überprüfungsprozesses deaktiviert. Aktivieren Sie `FORCE_LAST_GOOD_PLAN` die Option mithilfe von ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL)-Anweisung, oder erzwingen Sie den Plan manuell mithilfe des Skripts in der `details` Spalte.
`UnsupportedStatementType`	Der Plan kann für die Abfrage nicht erzwungen werden. Beispiele für nicht unterstützte Abfragen sind Cursor und `INSERT BULK` Anweisungen.
`LastGoodPlanForced`	Die Empfehlung wurde erfolgreich angewendet.
`AutomaticTuningOptionNotEnabled`	Die Datenbank-Engine hat eine potenzielle Leistungsregression identifiziert, aber die `FORCE_LAST_GOOD_PLAN` Option ist nicht aktiviert. Weitere Informationen finden Sie unter ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Empfehlung manuell anwenden oder Option aktivieren `FORCE_LAST_GOOD_PLAN` .
`VerificationAborted`	Der Überprüfungsprozess wird aufgrund des Neustarts oder der bereinigung Abfragespeicher abgebrochen.
`VerificationForcedQueryRecompile`	Die Abfrage wird neu kompiliert, da es keine signifikanten Leistungsverbesserungen gibt.
`PlanForcedByUser`	Der Benutzer hat den Plan mithilfe sp_query_store_force_plan (Transact-SQL)- Prozedur manuell erzwungen. Die Datenbank-Engine wendet die Empfehlung nicht an, wenn der Benutzer explizit entschieden hat, einen Plan zu erzwingen.
`PlanUnforcedByUser`	Der Benutzer hat den Plan mithilfe sp_query_store_unforce_plan (Transact-SQL)- Prozedur manuell entzwingt. Da der Benutzer den empfohlenen Plan explizit zurückgesetzt hat, verwendet die Datenbank-Engine weiterhin den aktuellen Plan und generiert eine neue Empfehlung, wenn in Zukunft eine Planregression auftritt.
`UserForcedDifferentPlan`	Der Benutzer hat einen anderen Plan mithilfe sp_query_store_force_plan (Transact-SQL)- Prozedur manuell erzwungen. Die Datenbank-Engine wendet die Empfehlung nicht an, wenn der Benutzer explizit entschieden hat, einen Plan zu erzwingen.
`TempTableChanged`	Eine temporäre Tabelle, die im Plan verwendet wurde, wird geändert.

Statistiken in der details Spalte zeigen keine Laufzeitplanstatistiken an (z. B. die aktuelle CPU-Zeit). Die Empfehlungsdetails werden zum Zeitpunkt der Regressionserkennung erstellt und beschreiben, warum die Datenbank-Engine leistungsregression identifiziert hat. Verwenden Sie regressedPlanId undrecommendedPlanId, um Abfragespeicher Katalogsichten abzufragen, um genaue Laufzeitplanstatistiken zu finden.

Beispiele für die Verwendung von Informationen zu 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 abgerufen, das einen guten Plan für eine bestimmte Abfrage erzwingt, sowie zusätzliche Informationen zum geschätzten Gewinn:

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 abgerufen, das einen guten Plan für jede bestimmte Abfrage erzwingt, sowie zusätzliche Informationen, 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 der Datenbank-Engine.

Berechtigungen

Erfordert VIEW SERVER STATE die Berechtigung 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.