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

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

In 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
ragione 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 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:

Stato Descrizione
Active La raccomandazione è attiva e non è ancora applicata. L'utente può accettare script di raccomandazione ed eseguirlo manualmente.
Verifying La raccomandazione viene applicata da motore di database e il 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 identificato 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 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