sys.dm_db_tuning_recommendations (Transact-SQL)

Si applica a: SQL Server 2017 (14.x) e versioni successive database SQL di Azure Istanza gestita di SQL di Azure

Restituisce informazioni dettagliate sulle raccomandazioni per l'ottimizzazione automatica. Per altre informazioni, vedere Ottimizzazione automatica

Osservazioni:

Le informazioni restituite da sys.dm_db_tuning_recommendations vengono aggiornate quando il motore di database identifica la potenziale regressione delle prestazioni delle query e non è persistente. Le raccomandazioni vengono mantenute solo fino al riavvio del motore di database. Usare la colonna sqlserver_start_time in sys.dm_os_sys_info per trovare l'ora di avvio dell'ultimo motore di database. Gli amministratori del database devono eseguire periodicamente copie di backup della raccomandazione di ottimizzazione se vogliono mantenerle dopo il riciclo del server.

Il currentValue campo nella state colonna potrebbe avere i valori seguenti:

Il documento JSON nella state colonna contiene il motivo per cui viene descritta la raccomandazione nello stato corrente. I valori nel campo motivo potrebbero essere:

Le statistiche nella colonna non mostrano le statistiche del details piano di runtime, ad esempio l'ora corrente della CPU. I dettagli delle raccomandazioni vengono presi al momento del rilevamento della regressione e descrivono perché motore di database regressione delle prestazioni identificata. Usare regressedPlanId e recommendedPlanId per eseguire query sulle viste del catalogo di Query Store per trovare le statistiche esatte del piano di runtime.

Esempi di uso delle informazioni sulle raccomandazioni per l'ottimizzazione

Esempio 1

Il codice di esempio seguente ottiene lo script Transact-SQL generato che forza un buon piano per qualsiasi query specificata:

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

Esempio 2

Di seguito viene ottenuto lo script Transact-SQL generato che forza un buon piano per qualsiasi query specificata e informazioni aggiuntive sul guadagno stimato:

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;

Esempio 3

Di seguito viene restituito lo script Transact-SQL generato che forza un buon piano per qualsiasi query specificata e informazioni aggiuntive che includono il testo della query e i piani di query archiviati in Query Store:

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;

Per altre informazioni sulle funzioni JSON che possono essere usate per eseguire query sui valori nella visualizzazione delle raccomandazioni, vedere Supporto JSON in motore di database.

Autorizzazioni

È richiesta VIEW SERVER STATE l'autorizzazione in SQL Server.

Richiede l'autorizzazione VIEW DATABASE STATE per il database in database SQL di Azure.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.

Passaggi successivi

• Ottimizzazione automatica
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Supporto JSON
• sys.dm_os_sys_info (Transact-SQL)