Sdílet prostřednictvím

sys.dm_db_tuning_recommendations (Transact-SQL)

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

Vrátí podrobné informace o doporučeních automatického ladění. Další informace najdete 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é by ovlivnily uzavření databáze nebo zpřístupnily 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
název nvarchar(4000) Jedinečný název doporučení
type nvarchar(4000) Název možnosti automatického ladění, která doporučení vytvořila, například FORCE_LAST_GOOD_PLAN
důvod 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 následující 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í bude vrácena zpět.
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.
partitura int Odhadovaná hodnota/účinek tohoto doporučení na měřítko 0–100 (čím větší je lepší)
podrobnosti 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é databázovým strojem sys.dm_db_tuning_recommendations se aktualizují, když databázový stroj identifikuje potenciální regresi výkonu dotazů a neuchovává se. Doporučení se uchovávají pouze do restartování databázového stroje. 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:

Stav 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:

Důvod Description
SchemaChanged Doporučení vypršelo, protože došlo ke změně schématu 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í příkazu ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) 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á – viz ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). 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í postupu sp_query_store_force_plan (Transact-SQL). Databázový stroj doporučení nepoužije, pokud se uživatel explicitně rozhodl vynutit nějaký plán.
PlanUnforcedByUser Uživatel ručně nevynucoval plán pomocí postupu sp_query_store_unforce_plan (Transact-SQL). Vzhledem k tomu, že se uživatel výslovně vrátil k doporučenému plánu, bude databázový stroj dál používat aktuální plán a vygenerovat nové doporučení, pokud v budoucnu dojde k nějaké regresi plánu.
UserForcedDifferentPlan Uživatel ručně vynutil jiný plán pomocí postupu sp_query_store_force_plan (Transact-SQL). 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] INT '$.queryId',
        regressed_plan_id INT '$.regressedPlanId',
        last_good_plan_id INT '$.recommendedPlanId'
        ) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';

Příklad 2

Následující kód 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,
    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;

Příklad 3

Následující kód získá vygenerovaný Transact-SQL skript, který vynutí dobrý plán pro každý daný dotaz a další informace, které obsahují 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,
        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;

Další informace o funkcích JSON, které lze použít k dotazování hodnot v zobrazení doporučení, najdete v tématu Podpora JSON v databázovém stroji.

Povolení

Vyžaduje VIEW SERVER STATE oprávnění v SQL Serveru.

VIEW DATABASE STATE Vyžaduje oprávnění pro databázi ve službě Azure SQL Database.

Oprávnění pro SQL Server 2022 a novější

Vyžaduje VIEW SERVER PERFORMANCE STATE oprávnění na serveru.

Další kroky

  • Last updated on