sys.dm di _ _ ottimizzazione _ dei database (Transact-SQL)

Si applica a: sìSQL Server 2017 (14.x) e versioni successive Sìdatabase SQL di Azure

Restituisce informazioni dettagliate sulle indicazioni di ottimizzazione.

In database SQL di Azure, le viste a gestione dinamica non possono esporre le informazioni che influenzerebbero l'indipendenza del database o le informazioni sugli altri database a cui l'utente dispone di accesso. Per evitare di esporre queste informazioni, viene filtrata ogni riga che contiene dati che non appartengono al tenant connesso.

Nome colonna Tipo di dati Descrizione
nome nvarchar(4000) Nome univoco della raccomandazione.
type nvarchar(4000) Nome dell'opzione di ottimizzazione automatica che ha generato la raccomandazione, ad esempio FORCE_LAST_GOOD_PLAN
reason nvarchar(4000) Motivo per cui è stata fornita questa raccomandazione.
valid _ since datetime2 La prima volta che è stata generata questa raccomandazione.
ultimo _ aggiornamento 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 si trova nello stato corrente.
è _ un'azione _ eseguibile bit 1 = La raccomandazione può essere eseguita sul database tramite Transact-SQL script.
0 = La raccomandazione non può essere eseguita sul database (ad esempio, solo informazioni o raccomandazione ripristinata)
è _ un'azione _ ripristinabile 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.
ora di _ inizio _ _ dell'azione execute datetime2 Data di applicazione della raccomandazione.
durata _ dell'azione _ di esecuzione time Durata dell'azione di esecuzione.
azione _ execute _ avviata _ da nvarchar(4000) User = Piano forzato dall'utente nella raccomandazione.
System = Raccomandazione applicata automaticamente dal sistema.
ora _ di avvio _ dell'azione _ di esecuzione datetime2 Data di applicazione della raccomandazione.
ripristinare _ _ l'ora di inizio _ dell'azione datetime2 Data di ripristino della raccomandazione.
ripristinare la _ durata _ dell'azione time Durata dell'azione di ripristino.
azione _ di ripristino avviata _ _ da nvarchar(4000) User = Piano consigliato non forzato manualmente dall'utente.
System = Raccomandazione ripristinata automaticamente dal sistema.
tempo _ di avvio _ _ dell'azione di ripristino datetime2 Data di ripristino della raccomandazione.
Punteggio int Valore stimato/impatto per questa raccomandazione sulla scala 0-100 (maggiore è il valore migliore)
Dettagli nvarchar(max) Documento JSON che contiene altri dettagli sulla raccomandazione. Sono disponibili i campi seguenti:

planForceDetails
- queryId : _ ID query 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 della 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 devono essere forzati.
- recommendedPlanCpuTimeAverage - Tempo medio della 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 - Transact-SQL script da eseguire per forzare il piano consigliato.

Commenti

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

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

Stato Descrizione
Active La raccomandazione è attiva e non ancora applicata. L'utente può usare lo script di raccomandazione ed eseguirlo manualmente.
Verifying La raccomandazione viene applicata da e il processo di verifica interno confronta le prestazioni del piano Motore di database forzato con il piano regredito.
Success La raccomandazione è stata 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 state nella colonna contiene il motivo che descrive perché la raccomandazione è nello stato corrente. I valori nel campo reason potrebbero essere:

Motivo Descrizione
SchemaChanged La raccomandazione è scaduta perché lo schema di una tabella a cui si fa riferimento è stato modificato. Se viene rilevata una nuova regressione del piano di query nel nuovo schema, verrà creata una nuova raccomandazione.
StatisticsChanged La raccomandazione è scaduta a causa della modifica delle statistiche in una tabella di 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 last_force_failure_reason nella 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 ALTER DATABASE SET AUTOMATIC_TUNING ('istruzione Transact-SQL) o forzare manualmente il piano usando lo script nella details colonna .
UnsupportedStatementType Non è possibile forzare il piano nella query. Esempi di query non supportate sono cursori e INSERT BULK istruzioni .
LastGoodPlanForced La raccomandazione è stata applicata correttamente.
AutomaticTuningOptionNotEnabled Motore di databaseha identificato la potenziale regressione delle prestazioni, ma FORCE_LAST_GOOD_PLAN l'opzione non è abilitata. Vedere ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Opzione Applica raccomandazione manualmente FORCE_LAST_GOOD_PLAN o Abilita.
VerificationAborted Il processo di verifica viene interrotto a causa del riavvio o Query Store pulizia.
VerificationForcedQueryRecompile La query viene ricompilata perché non sono presenti miglioramenti significativi delle prestazioni.
PlanForcedByUser L'utente ha forzato manualmente il piano sp_query_store_force_plan (la procedura Transact-SQL). Il motore di database non applica la raccomandazione se l'utente ha deciso in modo esplicito di forzare un piano.
PlanUnforcedByUser L'utente ha forzato manualmente il piano sp_query_store_unforce_plan (procedura 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 si verifica una regressione del piano in futuro.
UserForcedDifferentPlan L'utente ha forzato manualmente un piano diverso sp_query_store_force_plan (la procedura Transact-SQL). Il motore di database non applica la raccomandazione se l'utente ha deciso in modo esplicito di forzare un piano.
TempTableChanged Viene modificata una tabella temporanea utilizzata nel piano.

Le statistiche nella colonna details non mostrano le statistiche del piano di runtime (ad esempio, il tempo CPU corrente). I dettagli delle raccomandazioni vengono presi al momento del rilevamento della regressione e descrivono il motivo per cui Motore di database è stata identificata la regressione delle prestazioni. Usare regressedPlanId e per eseguire query Query Store del catalogo recommendedPlanId per trovare statistiche esatte dei piani di runtime.

Esempi di utilizzo delle informazioni sulle indicazioni di ottimizzazione

Esempio 1

Di seguito viene fornito lo Transact-SQL script generato che forza un piano di esecuzione per una determinata query:

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 fornito lo script generato che impone un piano di buon piano per una Transact-SQL determinata query 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 fornito lo script generato che forza un piano di esecuzione per qualsiasi query specificata e informazioni aggiuntive che includono il testo della query e i piani di query archiviati Transact-SQL 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 raccomandazione, vedere Supporto JSON in Motore di database .

Autorizzazioni

È richiesta VIEW SERVER STATE l'autorizzazione in SQL Server .
Richiede VIEW DATABASE STATE l'autorizzazione per il database in database SQL di Azure .

Vedi anche

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)