sys.dm_db_tuning_recommendations (Transact-SQL)

Artikel
07/05/2024

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

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 verwaltete Instanz.

In Azure SQL-Datenbank können dynamische Verwaltungsansichten keine Informationen verfügbar machen, die sich auf das Eindämmen von Datenbanken auswirken oder Informationen zu anderen Datenbanken verfügbar machen, auf die der Benutzer zugreifen kann. Um diese Informationen nicht verfügbar zu machen, wird jede Zeile mit Daten, die nicht zum verbundenen Mandanten gehören, herausgefiltert.

Spaltenname	Datentyp	Beschreibung
name	nvarchar(4000)	Eindeutiger Name der Empfehlung.
type	nvarchar(4000)	Der Name der Option für die automatische Optimierung, die die Empfehlung erzeugt hat, z. B. `FORCE_LAST_GOOD_PLAN`
Grund	nvarchar(4000)	Grund dafür, warum diese Empfehlung vorgelegt wurde.
valid_since	datetime2	Das erste Mal, wenn diese Empfehlung generiert wurde.
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 Stand der Empfehlung. - `reason` - Konstante, die beschreibt, warum sich die Empfehlung im aktuellen Zustand befindet.
is_executable_action	bit	1 = Die Empfehlung kann über Transact-SQL-Skript für die Datenbank ausgeführt werden. 0 = Die Empfehlung kann nicht für die Datenbank ausgeführt werden (z. B. Informationen nur oder empfehlungsverwendet)
is_revertable_action	bit	1 = Die Empfehlung kann automatisch überwacht und vom Datenbankmodul zurückgesetzt werden. 0 = Die Empfehlung kann nicht automatisch überwacht und wiederhergestellt werden. Die meisten ausführbaren Aktionen können wiederhergestellt werden.
execute_action_start_time	datetime2	Das Datum, an dem die Empfehlung angewendet wird.
execute_action_duration	time	Dauer der Ausführungsaktion.
execute_action_initiated_by	nvarchar(4000)	`User` = Manuell erzwungener Plan in der Empfehlung. `System` = Automatisch angewendete Systemempfehlung.
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 Wiederherstellen-Aktion.
revert_action_initiated_by	nvarchar(4000)	`User` = Manuell nicht erzwungener empfohlener Plan. `System` = Die Empfehlung des Systems wird automatisch wiederhergestellt.
revert_action_initiated_time	datetime2	Datum, an dem die Empfehlung wiederhergestellt wurde.
score	int	Geschätzter Wert/Effekt für diese Empfehlung im Maßstab 0-100 (je größer)
details	nvarchar(max)	JSON-Dokument, das weitere Details zur Empfehlung enthält. Folgende Felder sind verfügbar: `planForceDetails` - `queryId` - query_id der zurückgeschrittenen Abfrage. - `regressedPlanId` - plan_id des zurückgeschrittenen Plans. - `regressedPlanExecutionCount` – Anzahl der Ausführungen der Abfrage mit zurückgeschrittenen Plan, bevor die Regression erkannt wird. - `regressedPlanAbortedCount` - Anzahl der erkannten Fehler während der Ausführung des zurückgeschrittenen Plans. - `regressedPlanCpuTimeAverage` - Durchschnittliche CPU-Zeit (in Mikro sekunden), die von der regressierten Abfrage verbraucht wird, bevor die Regression erkannt wird. - `regressedPlanCpuTimeStddev` – Die Standardabweichung der CPU-Zeit, die von der regressierten Abfrage verbraucht wird, bevor die Regression erkannt wird. - `recommendedPlanId` - plan_id des Plans, der erzwungen werden sollte. - `recommendedPlanExecutionCount`– Anzahl der Ausführungen der Abfrage mit dem Plan, der 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 Mikro sekunden), 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 regressierten Abfrage verbraucht wird, bevor die Regression erkannt wird. `implementationDetails` - `method` - Die Methode, die verwendet werden soll, um die Regression zu korrigieren. Der Wert lautet stets `TSql`. - `script` – Transact-SQL-Skript, das ausgeführt werden soll, um den empfohlenen Plan zu erzwingen.

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:

Status	Beschreibung
`Active`	Empfehlung ist aktiv und wird noch nicht angewendet. Der Benutzer kann das Empfehlungsskript übernehmen und manuell ausführen.
`Verifying`	Empfehlung wird durch Datenbank-Engine und interner Überprüfungsprozess angewendet, wobei die Leistung des erzwungenen Plans mit dem zurückgesetzten Plan verglichen wird.
`Success`	Empfehlung wird erfolgreich angewendet.
`Reverted`	Empfehlung wird wiederhergestellt, da keine signifikanten Leistungsgewinne erzielt werden.
`Expired`	Empfehlung ist abgelaufen und kann nicht mehr angewendet werden.

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:

`Reason`	Beschreibung
`SchemaChanged`	Empfehlung ist abgelaufen, da das Schema einer referenzierten Tabelle geändert wird. Neue Empfehlung wird erstellt, wenn eine neue Abfrageplanregression im neuen Schema erkannt wird.
`StatisticsChanged`	Die Empfehlung ist aufgrund der Statistikänderung in einer referenzierten Tabelle abgelaufen. Neue Empfehlung wird erstellt, wenn eine neue Abfrageplanregression basierend auf neuen Statistiken erkannt wird.
`ForcingFailed`	Empfohlener Plan kann nicht für eine Abfrage erzwungen werden. Suchen Sie die `last_force_failure_reason` in der sys.query_store_plan Ansicht, um den Grund des Fehlers zu finden.
`AutomaticTuningOptionDisabled`	`FORCE_LAST_GOOD_PLAN` option is disabled by the user during verification process. Aktivieren Sie `FORCE_LAST_GOOD_PLAN` option using ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) statement, or force the plan manually using the script in the `details` column.
`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`	Empfehlung wird erfolgreich angewendet.
`AutomaticTuningOptionNotEnabled`	Datenbank-Engine identifizierte potenzielle Leistungsregression, aber die `FORCE_LAST_GOOD_PLAN` Option ist nicht aktiviert – siehe 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 Abfragespeicher Bereinigung abgebrochen.
`VerificationForcedQueryRecompile`	Die Abfrage wird neu kompiliert, da es keine erhebliche Leistungsverbesserung gibt.
`PlanForcedByUser`	Der Benutzer hat den Plan manuell mit sp_query_store_force_plan (Transact-SQL) -Prozedur erzwungen. Das Datenbankmodul wird die Empfehlung nicht anwenden, wenn der Benutzer explizit beschlossen hat, einen Plan zu erzwingen.
`PlanUnforcedByUser`	Der Benutzer hat den Plan manuell mit sp_query_store_unforce_plan (Transact-SQL) -Verfahren entforcet. Da der Benutzer den empfohlenen Plan explizit zurückgesetzt hat, verwendet das Datenbankmodul weiterhin den aktuellen Plan und generiert eine neue Empfehlung, wenn eine Planregression in Zukunft auftritt.
`UserForcedDifferentPlan`	Der Benutzer hat manuell einen anderen Plan mit sp_query_store_force_plan (Transact-SQL) -Verfahren erzwungen. Das Datenbankmodul wird die Empfehlung nicht anwenden, wenn der Benutzer explizit beschlossen 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. 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.

Freigeben über