sys.dm_db_tuning_recommendations (Transact-SQL)

Articolo
08/21/2023

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

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

Per altre informazioni, vedere Monitoraggio e ottimizzazione delle prestazioni nel database SQL di Azure e in Istanza gestita di SQL di Azure.

Nel database SQL di Azure le viste a gestione dinamica non possono esporre informazioni che potrebbero influire sul contenimento del database o esporre informazioni su altri database a cui l'utente ha accesso. Per evitare di esporre queste informazioni, ogni riga contenente dati che non appartengono al tenant connesso viene filtrata.

Nome colonna	Tipo di dati	Descrizione
name	nvarchar(4000)	Nome univoco della raccomandazione.
type	nvarchar(4000)	Nome dell'opzione di ottimizzazione automatica che ha prodotto la raccomandazione, ad esempio `FORCE_LAST_GOOD_PLAN`
Motivo	nvarchar(4000)	Motivo per cui è stata fornita questa raccomandazione.
valid_since	datetime2	La prima volta che è stata generata questa raccomandazione.
last_refresh	datetime2	Ora dell'ultima generazione di questa raccomandazione.
state	nvarchar(4000)	Documento JSON che descrive lo stato della raccomandazione. Sono disponibili i campi seguenti: - `currentValue` - stato corrente della raccomandazione. - `reason` - costante che descrive il motivo per cui la raccomandazione è nello stato corrente.
is_executable_action	bit	1 = La raccomandazione può essere eseguita sul database tramite script Transact-SQL. 0 = La raccomandazione non può essere eseguita sul database ( ad esempio: solo informazioni o raccomandazione ripristinata)
is_revertable_action	bit	1 = La raccomandazione può essere monitorata e ripristinata automaticamente dal motore di database. 0 = La raccomandazione non può essere monitorata e ripristinata automaticamente. La maggior parte delle azioni eseguibili sarà ripristinabile.
execute_action_start_time	datetime2	Data di applicazione della raccomandazione.
execute_action_duration	time	Durata dell'azione di esecuzione.
execute_action_initiated_by	nvarchar(4000)	`User` = Piano forzato manualmente nell'indicazione. `System` = Raccomandazione applicata automaticamente al sistema.
execute_action_initiated_time	datetime2	Data di applicazione della raccomandazione.
revert_action_start_time	datetime2	Data in cui la raccomandazione è stata ripristinata.
revert_action_duration	time	Durata dell'azione di ripristino.
revert_action_initiated_by	nvarchar(4000)	`User` = Piano consigliato rimosso manualmente dall'utente. `System` = Raccomandazione ripristinata automaticamente dal sistema.
revert_action_initiated_time	datetime2	Data in cui la raccomandazione è stata ripristinata.
Punteggio	int	Valore/effetto stimato per questa raccomandazione sulla scala da 0 a 100 (maggiore è il valore migliore)
details	nvarchar(max)	Documento JSON che contiene altri dettagli sulla raccomandazione. Sono disponibili i campi seguenti: `planForceDetails` - `queryId` - query_id della query regredita. - `regressedPlanId` - plan_id del piano regredito. - `regressedPlanExecutionCount` - Numero di esecuzioni della query con piano regredito prima che venga rilevata la regressione. - `regressedPlanAbortedCount` - Numero di errori rilevati durante l'esecuzione del piano regredito. - `regressedPlanCpuTimeAverage` - Tempo medio cpu (in micro secondi) utilizzato dalla query regredita prima che venga rilevata la regressione. - `regressedPlanCpuTimeStddev` - Deviazione standard del tempo cpu utilizzato dalla query regredita prima che venga rilevata la regressione. - `recommendedPlanId` - plan_id del piano che deve essere forzato. - `recommendedPlanExecutionCount`- Numero di esecuzioni della query con il piano che deve essere forzato prima che venga rilevata la regressione. - `recommendedPlanAbortedCount` - Numero di errori rilevati durante l'esecuzione del piano che deve essere forzato. - `recommendedPlanCpuTimeAverage` - Tempo medio cpu (in micro secondi) utilizzato dalla query eseguita con il piano che deve essere forzato (calcolato prima che venga rilevata la regressione). - `recommendedPlanCpuTimeStddev` Deviazione standard del tempo cpu utilizzato dalla query regredita prima che venga rilevata la regressione. `implementationDetails` - `method` - Metodo da usare per correggere la regressione. Il valore è sempre `TSql`. - `script` - Script Transact-SQL da eseguire per forzare il piano consigliato.

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 sqlserver_start_time colonna 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:

Status	Descrizione
`Active`	La raccomandazione è attiva e non è ancora applicata. L'utente può accettare script di raccomandazione ed eseguirlo manualmente.
`Verifying`	La raccomandazione viene applicata dal motore di database e dal processo di verifica interno confronta le prestazioni del piano forzato con il piano regredito.
`Success`	La raccomandazione viene applicata correttamente.
`Reverted`	La raccomandazione viene ripristinata perché non sono presenti miglioramenti significativi delle prestazioni.
`Expired`	La raccomandazione è scaduta e non può più essere applicata.

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:

Motivo	Descrizione
`SchemaChanged`	Raccomandazione scaduta perché lo schema di una tabella a cui viene fatto riferimento viene modificato. Se viene rilevata una nuova regressione del piano di query nel nuovo schema, verrà creata una nuova raccomandazione.
`StatisticsChanged`	Raccomandazione scaduta a causa della modifica delle statistiche in una tabella a cui si fa riferimento. Se viene rilevata una nuova regressione del piano di query in base alle nuove statistiche, verrà creata una nuova raccomandazione.
`ForcingFailed`	Non è possibile forzare il piano consigliato in una query. Trovare nella `last_force_failure_reason`visualizzazione sys.query_store_plan per trovare il motivo dell'errore.
`AutomaticTuningOptionDisabled`	`FORCE_LAST_GOOD_PLAN` l'opzione è disabilitata dall'utente durante il processo di verifica. Abilitare `FORCE_LAST_GOOD_PLAN` l'opzione usando l'istruzione ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) o forzare manualmente il piano usando lo script nella `details` colonna.
`UnsupportedStatementType`	Non è possibile forzare il piano sulla query. Esempi di query non supportate sono cursori e `INSERT BULK` istruzioni.
`LastGoodPlanForced`	La raccomandazione viene applicata correttamente.
`AutomaticTuningOptionNotEnabled`	Motore di database ha identificato la potenziale regressione delle prestazioni, ma l'opzione `FORCE_LAST_GOOD_PLAN` non è abilitata. Vedere ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Applicare la raccomandazione manualmente o abilitare l'opzione `FORCE_LAST_GOOD_PLAN` .
`VerificationAborted`	Il processo di verifica viene interrotto a causa del riavvio o della pulizia di Query Store.
`VerificationForcedQueryRecompile`	La query viene ricompilata perché non esiste un miglioramento significativo delle prestazioni.
`PlanForcedByUser`	L'utente ha forzato manualmente il piano usando sp_query_store_force_plan (Transact-SQL). Il motore di database non applicherà la raccomandazione se l'utente ha deciso esplicitamente di forzare un piano.
`PlanUnforcedByUser`	L'utente ha rimosso manualmente il piano usando sp_query_store_unforce_plan (Transact-SQL). Poiché l'utente ha ripristinato in modo esplicito il piano consigliato, il motore di database continuerà a usare il piano corrente e genererà una nuova raccomandazione se in futuro si verifica una regressione del piano.
`UserForcedDifferentPlan`	L'utente ha forzato manualmente un piano diverso usando sp_query_store_force_plan (Transact-SQL). Il motore di database non applicherà la raccomandazione se l'utente ha deciso esplicitamente di forzare un piano.
`TempTableChanged`	Viene modificata una tabella temporanea utilizzata nel piano.

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 il motivo per cui il motore di database ha identificato la regressione delle prestazioni. 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 nel motore di database.

Autorizzazioni

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

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

Autorizzazioni per SQL Server 2022 e versioni successive

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