sys.dm_db_tuning_recommendations (Transact-SQL)

Platí pro: SQL Server 2017 (14.x) a novější verze Azure SQL Database Azure SQL Managed Instance SQL databáze v Microsoft Fabric

Vrátí podrobné informace o doporučeních automatického ladění. Další informace naleznete v tématu Automatické ladění.

Další informace najdete v tématu Monitorování a ladění výkonu ve službě Azure SQL Database a azure SQL Managed Instance.

V Azure SQL Database nemůžou zobrazení dynamické správy zveřejnit informace, které mají vliv na zahrnutí databáze nebo zpřístupňují informace o jiných databázích, ke kterým má uživatel přístup. Aby se zabránilo zveřejnění těchto informací, vyfiltruje se každý řádek obsahující data, která nepatří do připojeného tenanta.

Název sloupce	Datový typ	Description
`name`	nvarchar(4000)	Jedinečný název doporučení
`type`	nvarchar(4000)	Název možnosti automatického ladění, která doporučení vytvořila, `FORCE_LAST_GOOD_PLAN`například .
`reason`	nvarchar(4000)	Důvod, proč bylo toto doporučení poskytnuto
`valid_since`	datetime2	Při prvním vygenerování tohoto doporučení.
`last_refresh`	datetime2	Čas posledního vygenerování tohoto doporučení
`state`	nvarchar(4000)	Dokument JSON, který popisuje stav doporučení. K dispozici jsou tato pole: - `currentValue` - aktuální stav doporučení. - `reason` – konstanta, která popisuje, proč je doporučení v aktuálním stavu.
`is_executable_action`	bit	1 = Doporučení lze provést pro databázi prostřednictvím Transact-SQL skriptu. 0 = Doporučení nelze spouštět v databázi (například pouze informace nebo doporučení vrácená zpět).
`is_revertable_action`	bit	1 = Doporučení lze automaticky monitorovat a vrátit zpět databázovým strojem. 0 = Doporučení nelze automaticky monitorovat a vrátit zpět. Většina spustitelných akcí je obnovitelná.
`execute_action_start_time`	datetime2	Datum, kdy se doporučení použije
`execute_action_duration`	time	Doba trvání akce provedení.
`execute_action_initiated_by`	nvarchar(4000)	`User` = Plán vynuceného vynucení uživatele v doporučení. `System` = Systém automaticky použil doporučení.
`execute_action_initiated_time`	datetime2	Datum, kdy se doporučení použilo
`revert_action_start_time`	datetime2	Datum, kdy bylo doporučení vráceno.
`revert_action_duration`	time	Doba trvání akce vrácení.
`revert_action_initiated_by`	nvarchar(4000)	`User` = Uživatel ručně nevynucený doporučený plán. `System` = Systém automaticky vrátil doporučení.
`revert_action_initiated_time`	datetime2	Datum, kdy bylo doporučení vráceno.
`score`	int	Odhadovaná hodnota nebo účinek tohoto doporučení na měřítko 0–100 (čím větší je lepší).
`details`	nvarchar(max)	Dokument JSON, který obsahuje další podrobnosti o doporučení. K dispozici jsou následující pole. `planForceDetails`: - `queryId` - `query_id` regresního dotazu. - `regressedPlanId` - `plan_id` regresního plánu. - `regressedPlanExecutionCount` – Počet spuštění dotazu s regresí plánu před zjištěním regrese. - `regressedPlanAbortedCount` – Počet zjištěných chyb během provádění regresního plánu. - `regressedPlanCpuTimeAverage` – Průměrná doba procesoru (v mikrosekundách) spotřebovaná regresním dotazem před zjištěním regrese. - `regressedPlanCpuTimeStddev` – Směrodatná odchylka času procesoru spotřebovaného regresním dotazem před zjištěním regrese. - `recommendedPlanId` - `plan_id` plánu, který by měl být vynucen. - `recommendedPlanExecutionCount`– Počet spuštění dotazu s plánem, který by měl být vynucen před zjištěním regrese. - `recommendedPlanAbortedCount` – Počet zjištěných chyb při provádění plánu, které by měly být vynuceny. - `recommendedPlanCpuTimeAverage` – Průměrná doba procesoru (v mikrosekundách) spotřebovaná dotazem spuštěným s plánem, který by měl být vynucený (vypočítán před zjištěním regrese). - `recommendedPlanCpuTimeStddev` Směrodatná odchylka času procesoru spotřebovaného regresním dotazem před zjištěním regrese `implementationDetails`: - `method` - Metoda, která by měla být použita k opravě regrese. Hodnota je vždy `TSql`. - `script` – Transact-SQL skript, který by se měl provést, aby se vynutil doporučený plán.

Poznámky

Informace vrácené sys.dm_db_tuning_recommendations databázovým strojem se aktualizují, když databázový stroj identifikuje potenciální regresi výkonu dotazu a neudrží se. Databázový stroj uchovává doporučení jenom do té doby, než se restartuje. Pomocí sloupce sqlserver_start_time v sys.dm_os_sys_info vyhledejte čas posledního spuštění databázového stroje. Správci databází by měli pravidelně vytvářet záložní kopie doporučení pro ladění, pokud je chtějí zachovat po recyklaci serveru.

Pole currentValue ve sloupci state může mít následující hodnoty:

Status	Description
`Active`	Doporučení je aktivní a ještě není použito. Uživatel může přijmout skript doporučení a spustit ho ručně.
`Verifying`	Doporučení se používá databázovým strojem a procesem interního ověření porovnává výkon vynuceného plánu s regresním plánem.
`Success`	Doporučení se úspěšně použije.
`Reverted`	Doporučení se vrátí, protože nedošlo k významným nárůstu výkonu.
`Expired`	Vypršela platnost doporučení a už se nedá použít.

Dokument JSON ve state sloupci obsahuje důvod, proč je doporučení v aktuálním stavu. Hodnoty v poli důvodu můžou být:

Reason	Description
`SchemaChanged`	Doporučení vypršelo, protože se změnilo schéma odkazované tabulky. Pokud se v novém schématu zjistí regrese nového plánu dotazů, vytvoří se nové doporučení.
`StatisticsChanged`	Doporučení vypršelo kvůli změně statistiky v odkazované tabulce. Nové doporučení se vytvoří, pokud se na základě nových statistik zjistí regrese nového plánu dotazů.
`ForcingFailed`	Doporučený plán nelze v dotazu vynutit. `last_force_failure_reason` Vyhledejte v zobrazení sys.query_store_plan důvod selhání.
`AutomaticTuningOptionDisabled`	`FORCE_LAST_GOOD_PLAN` uživatel během procesu ověření tuto možnost zakázal. Povolte `FORCE_LAST_GOOD_PLAN` možnost pomocí ALTER DATABASE SET AUTOMATIC_TUNING příkazu nebo vynuťte plán ručně pomocí skriptu ve sloupci `details` .
`UnsupportedStatementType`	Plán nelze v dotazu vynutit. Příklady nepodporovaných dotazů jsou kurzory a `INSERT BULK` příkazy.
`LastGoodPlanForced`	Doporučení se úspěšně použije.
`AutomaticTuningOptionNotEnabled`	Databázový stroj identifikoval potenciální regresi výkonu `FORCE_LAST_GOOD_PLAN` , ale tato možnost není povolená. Pro více informací viz ALTER DATABASE SET AUTOMATIC_TUNING. Použijte doporučení ručně nebo povolte `FORCE_LAST_GOOD_PLAN` možnost.
`VerificationAborted`	Proces ověření se přeruší kvůli restartování nebo vyčištění úložiště dotazů.
`VerificationForcedQueryRecompile`	Dotaz je rekompilován, protože nedošlo k významnému zlepšení výkonu.
`PlanForcedByUser`	Uživatel ručně vynutil plán pomocí sp_query_store_force_plan postupu. Databázový stroj doporučení nepoužije, pokud se uživatel explicitně rozhodl vynutit nějaký plán.
`PlanUnforcedByUser`	Uživatel ručně nevynutil plán pomocí sp_query_store_unforce_plan postupu. Vzhledem k tomu, že uživatel výslovně vrátil doporučený plán, databázový stroj bude dál používat aktuální plán a vygeneruje nové doporučení, pokud v budoucnu dojde k nějaké regresi plánu.
`UserForcedDifferentPlan`	Uživatel ručně vynutil jiný plán pomocí sp_query_store_force_plan postupu. Databázový stroj doporučení nepoužije, pokud se uživatel explicitně rozhodl vynutit nějaký plán.
`TempTableChanged`	Změní se dočasná tabulka použitá v plánu.

Statistiky ve details sloupci nezobrazují statistiky plánu modulu runtime (například aktuální čas procesoru). Podrobnosti o doporučení se provádějí v době detekce regrese a popisují, proč databázový stroj identifikoval regresi výkonu. Pomocí regressedPlanId a recommendedPlanId dotazem na zobrazení katalogu úložiště dotazů vyhledejte přesné statistiky plánu modulu runtime.

Příklady použití informací o doporučeních pro ladění

Příklad 1

Následující ukázkový kód získá vygenerovaný Transact-SQL skript, který vynutí dobrý plán pro každý daný dotaz:

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] BIGINT '$.queryId',
    regressed_plan_id BIGINT '$.regressedPlanId',
    last_good_plan_id BIGINT '$.recommendedPlanId'
) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';

Příklad 2

Následující dotaz získá vygenerovaný Transact-SQL skript, který vynutí dobrý plán pro každý daný dotaz a další informace o odhadovaném získání:

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

Příklad 3

Následující dotaz získá vygenerovaný Transact-SQL skript, který vynutí dobrý plán pro každý daný dotaz a další informace, které zahrnují text dotazu a plány dotazů uložené v úložišti dotazů:

WITH cte_db_tuning_recommendations
AS (
SELECT reason,
       score,
       query_id,
       regressedPlanId,
       recommendedPlanId,
       JSON_VALUE(STATE, '$.currentValue') AS current_state,
       JSON_VALUE(STATE, '$.reason') AS current_state_reason,
       JSON_VALUE(details, '$.implementationDetails.script') AS script,
       (regressedPlanExecutionCount + recommendedPlanExecutionCount) * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000 AS estimated_gain,
       IIF (regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO') AS error_prone
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (details, '$.planForceDetails') WITH (
    [query_id] BIGINT '$.queryId',
    regressedPlanId BIGINT '$.regressedPlanId',
    recommendedPlanId BIGINT '$.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;

Další informace o funkcích JSON, které můžete použít k dotazování hodnot v zobrazení doporučení, najdete v tématu Data JSON na SQL Serveru.

Povolení

SQL Server 2022 (16.x) a starší verze vyžadují VIEW SERVER STATE oprávnění v SQL Serveru.

SQL Server 2022 (16.x) a Azure SQL Database vyžadují VIEW SERVER PERFORMANCE STATE na serveru oprávnění.

automatické ladění
sys.database_automatic_tuning_options (Transact-SQL)
sys.database_query_store_options (Transact-SQL)
Data JSON na SQL Serveru
sys.dm_os_sys_info (Transact-SQL)

Váš názor

Byla tato stránka užitečná?

Last updated on 2026-07-08