sys.dm_db_tuning_recommendations (Transact-SQL)

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje bazy danychSQL Sql Database azure SQLDatabasew usłudze Microsoft Fabric

Zwraca szczegółowe informacje o rekomendacjach dotyczących automatycznego dostrajania. Aby uzyskać więcej informacji, zobacz Automatyczne dostrajanie

Aby uzyskać więcej informacji, zobacz Monitorowanie i dostrajanie wydajności w usługach Azure SQL Database i Azure SQL Managed Instance.

W usłudze Azure SQL Database dynamiczne widoki zarządzania nie mogą uwidaczniać informacji, które miałyby wpływ na zawieranie bazy danych lub uwidaczniać informacje o innych bazach danych, do których użytkownik ma dostęp. Aby uniknąć uwidaczniania tych informacji, każdy wiersz zawierający dane, które nie należą do połączonej dzierżawy, jest filtrowany.

Nazwa kolumny	Typ danych	Opis
name	nvarchar(4000)	Unikatowa nazwa rekomendacji.
type	nvarchar(4000)	Nazwa opcji automatycznego dostrajania, która wyprodukowała zalecenie, na przykład FORCE_LAST_GOOD_PLAN
przyczyna	nvarchar(4000)	Przyczyna podania tego zalecenia.
valid_since	datetime2	Przy pierwszym wygenerowaniu tej rekomendacji.
last_refresh	datetime2	Ostatni raz ta rekomendacja została wygenerowana.
state	nvarchar(4000)	Dokument JSON opisujący stan zalecenia. Dostępne są następujące pola: - currentValue — bieżący stan zalecenia. - reason - stała, która opisuje, dlaczego zalecenie jest w bieżącym stanie.
is_executable_action	bit	1 = Zalecenie można wykonać względem bazy danych za pomocą skryptu Transact-SQL. 0 = Rekomendacja nie może być wykonywana względem bazy danych (na przykład: tylko informacje lub rekomendacja przywrócona)
is_revertable_action	bit	1 = Rekomendacja może być automatycznie monitorowana i przywracana przez aparat bazy danych. 0 = Rekomendacja nie może być automatycznie monitorowana i przywracana. Większość akcji wykonywalnych będzie można przywrócić.
execute_action_start_time	datetime2	Data zastosowania zalecenia.
execute_action_duration	time	Czas trwania akcji wykonania.
execute_action_initiated_by	nvarchar(4000)	User = Ręcznie wymuszony plan użytkownika w rekomendacji. System = Rekomendacja zastosowana automatycznie przez system.
execute_action_initiated_time	datetime2	Data zastosowania zalecenia.
revert_action_start_time	datetime2	Data, w których zalecenie zostało przywrócone.
revert_action_duration	time	Czas trwania akcji przywracania.
revert_action_initiated_by	nvarchar(4000)	User = Użytkownik ręcznie niewymuszonego zalecanego planu. System = Rekomendacja automatycznie przywrócona przez system.
revert_action_initiated_time	datetime2	Data, w których zalecenie zostało przywrócone.
wynik	int	Szacowana wartość/efekt dla tego zalecenia w skali od 0 do 100 (tym większa)
Szczegóły	nvarchar(max)	Dokument JSON zawierający więcej szczegółów na temat zalecenia. Dostępne są następujące pola: planForceDetails - queryId — query_id zapytania, których regresja jest regresja. - regressedPlanId - plan_id planu regresji. - regressedPlanExecutionCount — Liczba wykonań zapytania z planem regresji przed wykryciem regresji. - regressedPlanAbortedCount - Liczba wykrytych błędów podczas wykonywania planu regresji. - regressedPlanCpuTimeAverage — Średni czas procesora CPU (w mikrosekundach) używany przez zapytanie regresji przed wykryciem regresji. - regressedPlanCpuTimeStddev — Odchylenie standardowe czasu procesora CPU zużywane przez zapytanie regresji przed wykryciem regresji. - recommendedPlanId - plan_id planu, który należy wymusić. - recommendedPlanExecutionCount— Liczba wykonań zapytania z planem, które należy wymusić przed wykryciem regresji. - recommendedPlanAbortedCount - Liczba wykrytych błędów podczas wykonywania planu, które należy wymusić. - recommendedPlanCpuTimeAverage — Średni czas procesora CPU (w mikrosekundach) używany przez zapytanie wykonane z planem, który powinien zostać wymuszony (obliczony przed wykryciem regresji). - recommendedPlanCpuTimeStddev Odchylenie standardowe czasu procesora CPU zużywane przez zapytanie o regresji przed wykryciem regresji. implementationDetails - method - Metoda, która powinna służyć do poprawiania regresji. Wartość to zawsze TSql. - script - Transact-SQL skrypt, który należy wykonać, aby wymusić zalecany plan.

Uwagi

Informacje zwracane przez sys.dm_db_tuning_recommendations program są aktualizowane, gdy aparat bazy danych identyfikuje potencjalną regresję wydajności zapytań i nie jest utrwalany. Zalecenia są przechowywane tylko do momentu ponownego uruchomienia aparatu bazy danych. Użyj kolumny sqlserver_start_time w sys.dm_os_sys_info , aby znaleźć ostatni czas uruchamiania aparatu bazy danych. Administratorzy baz danych powinni okresowo tworzyć kopie zapasowe zalecenia dostrajania, jeśli chcą zachować je po recyklingu serwera.

Pole currentValue w kolumnie state może mieć następujące wartości:

Status	Description
Active	Rekomendacja jest aktywna i nie jest jeszcze stosowana. Użytkownik może wykonać skrypt rekomendacji i wykonać go ręcznie.
Verifying	Zalecenie jest stosowane przez aparat bazy danych i wewnętrzny proces weryfikacji porównuje wydajność wymuszonego planu z planem regresji.
Success	Zalecenie zostało pomyślnie zastosowane.
Reverted	Zalecenie zostało przywrócone, ponieważ nie ma znaczących wzrostów wydajności.
Expired	Zalecenie wygasło i nie można go już stosować.

Dokument JSON w state kolumnie zawiera przyczynę, dla których opisano, dlaczego zalecenie jest w bieżącym stanie. Wartości w polu przyczyny mogą być następujące:

Przyczyna	Description
SchemaChanged	Zalecenie wygasło, ponieważ schemat tabeli, do których odwołuje się odwołanie, został zmieniony. Nowe zalecenie zostanie utworzone, jeśli zostanie wykryta nowa regresja planu zapytania w nowym schemacie.
StatisticsChanged	Zalecenie wygasło z powodu zmiany statystyki w tabeli, do których odwołuje się odwołanie. Nowe zalecenie zostanie utworzone, jeśli zostanie wykryta nowa regresja planu zapytania na podstawie nowych statystyk.
ForcingFailed	Zalecanego planu nie można wymusić na zapytaniu. Znajdź element last_force_failure_reason w widoku sys.query_store_plan , aby znaleźć przyczynę błędu.
AutomaticTuningOptionDisabled	FORCE_LAST_GOOD_PLAN opcja jest wyłączona przez użytkownika podczas procesu weryfikacji. Włącz FORCE_LAST_GOOD_PLAN opcję przy użyciu instrukcji ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) lub wymuś ręczne użycie skryptu w kolumnie details .
UnsupportedStatementType	W zapytaniu nie można wymusić wymuszenia planu. Przykłady nieobsługiwanych zapytań to kursory i INSERT BULK instrukcja.
LastGoodPlanForced	Zalecenie zostało pomyślnie zastosowane.
AutomaticTuningOptionNotEnabled	Aparat bazy danych zidentyfikował potencjalną regresję wydajności, ale FORCE_LAST_GOOD_PLAN opcja nie jest włączona — zobacz ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Zastosuj zalecenie ręcznie lub włącz FORCE_LAST_GOOD_PLAN opcję.
VerificationAborted	Proces weryfikacji został przerwany z powodu ponownego uruchomienia lub oczyszczania magazynu zapytań.
VerificationForcedQueryRecompile	Zapytanie jest ponownie skompilowane, ponieważ nie ma znaczącej poprawy wydajności.
PlanForcedByUser	Użytkownik ręcznie wymusił plan przy użyciu procedury sp_query_store_force_plan (Transact-SQL). Aparat bazy danych nie zastosuje rekomendacji, jeśli użytkownik jawnie zdecyduje się wymusić jakiś plan.
PlanUnforcedByUser	Użytkownik ręcznie nie wymuś planu przy użyciu procedury sp_query_store_unforce_plan (Transact-SQL). Ponieważ użytkownik jawnie przywrócił zalecany plan, aparat bazy danych będzie nadal korzystać z bieżącego planu i generować nowe zalecenie, jeśli w przyszłości wystąpi regresja planu.
UserForcedDifferentPlan	Użytkownik ręcznie wymusił inny plan przy użyciu procedury sp_query_store_force_plan (Transact-SQL). Aparat bazy danych nie zastosuje rekomendacji, jeśli użytkownik jawnie zdecyduje się wymusić jakiś plan.
TempTableChanged	Zmieniono tymczasową tabelę, która została użyta w planie.

Statystyki w kolumnie details nie pokazują statystyk planu środowiska uruchomieniowego (na przykład bieżącego czasu procesora CPU). Szczegóły rekomendacji są wykonywane w czasie wykrywania regresji i opisują, dlaczego aparat bazy danych zidentyfikował regresję wydajności. Użyj regressedPlanId funkcji i recommendedPlanId , aby wysyłać zapytania do widoków wykazu magazynu zapytań , aby znaleźć dokładne statystyki planu środowiska uruchomieniowego.

Przykłady używania informacji o zaleceniach dotyczących dostrajania

Przykład 1

Poniższy przykładowy kod pobiera wygenerowany skrypt Transact-SQL, który wymusza dobry plan dla danego zapytania:

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';

Przykład 2

Poniższy kod pobiera wygenerowany skrypt Transact-SQL, który wymusza dobry plan dla danego zapytania i dodatkowe informacje o szacowanym zysku:

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;

Przykład 3

Poniższy kod pobiera wygenerowany skrypt Transact-SQL, który wymusza dobry plan dla danego zapytania oraz dodatkowe informacje zawierające tekst zapytania i plany zapytań przechowywane w magazynie zapytań:

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;

Aby uzyskać więcej informacji na temat funkcji JSON, które mogą służyć do wykonywania zapytań o wartości w widoku rekomendacji, zobacz Obsługa formatu JSON w a także aparatu bazy danych.

Permissions

Wymaga VIEW SERVER STATE uprawnień w programie SQL Server.

VIEW DATABASE STATE Wymaga uprawnień dla bazy danych w usłudze Azure SQL Database.

Uprawnienia dla programu SQL Server 2022 i nowszych

Wymaga VIEW SERVER PERFORMANCE STATE uprawnień na serwerze.

Dalsze kroki

• automatyczne dostrajanie
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Obsługa formatu JSON
• sys.dm_os_sys_info (Transact-SQL)