Monitoraggio delle prestazioni con Query Store

Si applica a: sìSQL Server 2016 (13.x) e versioni successive Sìdatabase SQL di Azure SìIstanza gestita di SQL di Azure sìAzure Synapse Analytics

La funzionalità Archivio query di SQL Server mostra informazioni dettagliate sulle prestazioni e sulla scelta del piano di query. Semplifica la risoluzione dei problemi di prestazioni in quanto consente di individuare rapidamente le variazioni delle prestazioni causate da modifiche nei piani di query. Archivio query acquisisce automaticamente una cronologia delle query, dei piani e delle statistiche di runtime e li conserva per la consultazione. I dati vengono separati in base a intervalli di tempo, consentendo di visualizzare i modelli di utilizzo del database e capire quando sono state apportate modifiche al piano di query nel server. Per configurare l'archivio query, è possibile usare l'opzione ALTER DATABASE SET .

Per informazioni sul funzionamento di Query Store nel Database SQL di Azure, vedere Uso di Query Store nel database SQL di Azure.

Importante

Se si usa Query Store per informazioni dettagliate sui carichi di lavoro Just-In-Time in SQL Server 2016 (13.x), prevedere l'installazione delle correzioni di scalabilità delle prestazioni in KB 4340759 appena possibile.

Abilitazione di Archivio query

Per impostazione predefinita, Query Store non è abilitato per i nuovi database di SQL Server e Azure Synapse Analytics ed è abilitato per impostazione predefinita per i nuovi database di database SQL di Azure.

Usare la pagina Archivio query in SQL Server Management Studio

  1. In Esplora oggetti fare clic con il pulsante destro del mouse su un database e quindi scegliere Proprietà.

    Nota

    È necessaria almeno la versione 16 di Management Studio.

  2. Nella finestra di dialogo Proprietà database selezionare la pagina Archivio query .

  3. Nella casella Modalità operativa (richiesta) selezionare Lettura/Scrittura.

Usare istruzioni Transact-SQL

Per abilitare Query Store per un determinato database, usare l'istruzione ALTER DATABASE. Ad esempio:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

In Azure Synapse Analytics abilitare il Query Store senza opzioni aggiuntive, ad esempio:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Per altre opzioni della sintassi correlate a Query Store, vedere Opzioni ALTER DATABASE SET (Transact-SQL).

Nota

Query Store non può essere abilitato per i master database o tempdb .

Importante

Per informazioni sull'abilitazione di Query Store e su come mantenerlo allineato al carico di lavoro, vedere Procedure consigliate per l'archivio query.

Informazioni presenti in Archivio query

I piani di esecuzione per query specifiche in SQL Server in genere cambiano nel tempo per motivi diversi, quali modifiche delle statistiche, modifiche dello schema, creazione/eliminazione di indici e così via. Nella cache delle procedure, dove sono archiviati i piani di query memorizzati nella cache, viene archiviato solo il piano di esecuzione più recente. La rimozione dei piani dalla cache dei piani può dipendere anche da problemi di memoria. Di conseguenza, le regressioni delle prestazioni di esecuzione delle query causate da modifiche del piano di esecuzione possono essere rilevanti e richiedere tempo per la risoluzione.

Dal momento che in Query Store vengono mantenuti più piani di esecuzione per ogni query, è possibile applicare i criteri in modo che Query Processor usi un piano di esecuzione specifico per una query. Questo processo viene chiamato utilizzo forzato del piano. Per applicare l'utilizzo forzato del piano in Archivio query, viene usato un meccanismo simile all'hint per la query USE PLAN , che però non richiede modifiche nelle applicazioni utente. Grazie all'utilizzo forzato del piano è possibile risolvere molto rapidamente una regressione delle prestazioni di esecuzione delle query causata da una modifica del piano.

Nota

Query Store raccoglie i piani per le istruzioni DML come SELECT, INSERT, UPDATE, DELETE, MERGE e BULK INSERT.

Per impostazione predefinita, Query Store non raccoglie i dati per le stored procedure compilate in modo nativo. Usare sys.sp_xtp_control_query_exec_stats per abilitare la raccolta di dati per le stored procedure compilate in modo nativo.

Le statistiche di attesa sono un'altra fonte di informazioni utili per risolvere i problemi di prestazioni nel Motore di database. Per molto tempo le statistiche di attesa sono state disponibili solo a livello di istanza, il che rendeva difficile far risalire le attese a una query specifica. A partire da SQL Server 2017 (14.x) e database SQL di Azure, Query Store include una dimensione che tiene traccia delle statistiche di attesa. L'esempio seguente abilita la raccolta delle statistiche di attesa in Query Store.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

La funzionalità Archivio query viene usata in genere negli scenari seguenti:

  • Individuare e correggere rapidamente una regressione delle prestazioni di esecuzione delle query forzando un piano di query precedente. Correggere le query in cui si è verificata di recente una regressione delle prestazioni a causa di modifiche del piano di esecuzione.
  • Determinare il numero di volte in cui una query è stata eseguita in un determinato intervallo di tempo, in modo da assistere un amministratore di database nella risoluzione dei problemi relativi alle prestazioni delle risorse.
  • Identificare le prime n query (in base al tempo di esecuzione, al consumo della memoria e così via) nelle ultime x ore.
  • Controllare la cronologia dei piani di query per una determinata query.
  • Analizzare i modelli di utilizzo delle risorse (CPU, I/O e memoria) per un determinato database.
  • Identificare le prime query n in attesa su risorse.
  • Comprendere la natura di attesa per una query o un piano in particolare.

Query Store contiene tre archivi:

  • Un archivio piani per il salvataggio in modo permanente delle informazioni sul piano di esecuzione.
  • a archivio statistiche runtime: per il salvataggio in modo permanente delle informazioni sulle statistiche di esecuzione.
  • a archivio statistiche di attesa: per il salvataggio in modo permanente delle informazioni sulle statistiche di attesa.

Il numero di piani univoci che è possibile archiviare per una query nell'archivio piani è limitato dall'opzione di configurazione max_plans_per_query . Per migliorare le prestazioni, le informazioni vengono scritte negli archivi in modo asincrono. Per ridurre al minimo l'utilizzo dello spazio, le statistiche di esecuzione di runtime nell'archivio delle statistiche di runtime vengono aggregate in un intervallo di tempo fisso. Per visualizzare le informazioni contenute in questi archivi, è possibile eseguire una query sulle viste del catalogo di Query Store.

La query seguente restituisce le informazioni sulle query e sui piani inclusi in Query Store.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id;

Usare la funzionalità Query regredite

Dopo aver abilitato Query Store, aggiornare la parte del database del riquadro Esplora oggetti per aggiungere la sezione Query Store.

Albero di Query Store di SQL Server 2016 in Esplora oggetti di SSMS Albero di Query Store di SQL Server 2017 in Esplora oggetti di SSMS

Nota

Ad Azure Synapse Analytics, Query Store sono disponibili in Viste di sistema nella parte del database del riquadro Esplora oggetti dati.

Selezionare Query regredite per aprire il riquadro Query regredite in SQL Server Management Studio. Nel riquadro Query regredite sono visualizzati i piani e le query presenti nell'archivio query. Usare le caselle a discesa nella parte superiore per filtrare le query in base a criteri diversi: Durata (ms) (predefinito), Tempo CPU (ms), Letture logiche (KB), Scritture logiche (KB), Letture fisiche (KB), Tempo CLR (ms), DOP, Utilizzo memoria (KB), Conteggio righe, Memoria log usata (KB), Memoria database temporaneo usata (KB) e Tempo di attesa (ms).

Selezionare un piano per visualizzare il piano di query con interfaccia grafica. Sono disponibili pulsanti che consentono di visualizzare la query di origine, forzare e annullare la forzatura di un piano di query, passare dai formati griglia ai grafici e viceversa, confrontare piani selezionati (se è selezionato più di un piano) e aggiornare la visualizzazione.

Query regredite di SQL Server 2016 in Esplora oggetti di SSMS

Per forzare un piano, selezionare una query e un piano, quindi selezionare Force Plan (Forza piano). È possibile forzare solo piani che sono stati salvati dalla funzionalità del piano di query e che sono ancora presenti nella relativa cache.

Ricerca di query in attesa

A partire da SQL Server 2017 (14.x) e database SQL di Azure, in Query Store sono disponibili le statistiche di attesa per ogni query nel corso del tempo.

In Query Store i tipi di attesa sono raggruppati in categorie di attesa. In sys.query_store_wait_stats (Transact-SQL) è disponibile il mapping delle categorie di attesa ai tipi di attesa.

Selezionare Statistiche di attesa query per aprire il riquadro Statistiche di attesa query in SQL Server Management Studio v18 o versione successiva. Il riquadro Statistiche di attesa query visualizza un grafico a barre contenente le categorie di attesa principali in Query Store. Usare l'elenco a discesa nella parte superiore per selezionare un criterio di aggregazione per il tempo di attesa: medio, max, min, deviazione standard e totale (impostazione predefinita).

Statistiche di attesa query di SQL Server 2017 in Esplora oggetti di SSMS

Selezionare una categoria di attesa facendo clic sulla barra. Verrà visualizzata una vista dettagliata sulla categoria di attesa selezionata. Questo nuovo grafico a barre contiene le query che hanno contribuito a tale categoria di attesa.

Visualizzazione dettagliata di Statistiche di attesa query di SQL Server 2017 in Esplora oggetti di SSMS

Usare la casella nella parte superiore per filtrare le query in base a diversi criteri di tempo di attesa per la categoria di attesa selezionata: medio, max, min, deviazione standard e totale (impostazione predefinita). Selezionare un piano per visualizzare il piano di query con interfaccia grafica. Sono disponibili pulsanti per visualizzare la query di origine, forzare e annullar e la forzatura di un piano di query e aggiornare la visualizzazione.

Le categorie di attesa raggruppano tipi di attesa diversi in bucket simili per natura. Categorie di attesa diverse richiedono un'analisi di completamento diversa per risolvere il problema, ma i tipi di attesa della stessa categoria portano a esperienze di risoluzione dei problemi molto simili e fornire la query interessata sulle attese sarebbe il componente mancante per completare correttamente la maggior parte di queste indagini.

Di seguito sono descritti alcuni esempi su come ottenere informazioni dettagliate riguardanti il carico di lavoro prima e dopo aver introdotto le categorie di attesa in Query Store:

Esperienza precedente Esperienza successiva Azione
Attese di RESOURCE_SEMAPHORE elevate per database Attese di memoria elevate in Query Store per query specifiche Trovare le query con il maggiore utilizzo di memoria in Query Store. Queste query probabilmente ritardano l'avanzamento delle query interessate. È consigliabile usare l'hint per la query MAX_GRANT_PERCENT per queste query o per le query interessate.
Attese di LCK_M_X elevate per database Attese di blocco elevate in Query Store per query specifiche Controllare il testo delle query interessate e identificare le entità di destinazione. In Query Store cercare altre query che modificano la stessa entità, che vengono eseguite frequentemente e/o hanno una durata elevata. Dopo aver identificato tali query, valutare la possibilità di modificare la logica dell'applicazione per migliorare la concorrenza o usare un livello di isolamento meno restrittivo.
Attese di PAGEIOLATCH_SH elevate per database Attese di I/O del buffer elevate in Query Store per query specifiche Trovare le query con un numero elevato di letture fisiche in Query Store. Se corrispondono alle query con attese di I/O elevate, provare a introdurre un indice nell'entità sottostante, in modo da eseguire ricerche anziché analisi e ridurre così al minimo il sovraccarico di I/O delle query.
Attese di SOS_SCHEDULER_YIELD elevate per database Attese di CPU elevate in Query Store per query specifiche Individuare la prime query per utilizzo CPU in Query Store. Tra queste query identificare quelle in cui la tendenza di utilizzo CPU elevato è correlata ad attese di CPU elevate per le query interessate. Concentrarsi sull'ottimizzazione di queste query: considerare la possibilità di una regressione del piano o la mancanza di un indice.

Opzioni di configurazione

Per le opzioni disponibili per la configurazione dei parametri di Query Store, vedere Opzioni ALTER DATABASE SET (Transact-SQL).

Eseguire una sys.database_query_store_options query sulla vista per determinare le opzioni correnti del Query Store. Per altre informazioni sui valori, vedere sys.database_query_store_options.

Per esempi di impostazione delle opzioni di configurazione tramite le istruzioni Transact-SQL, vedere Gestione delle opzioni.

Nota

Ad Azure Synapse Analytics, il Query Store può essere abilitato come in altre piattaforme, ma non sono supportate opzioni di configurazione aggiuntive.

È possibile visualizzare e gestire Archivio query con Management Studio oppure usando le viste e le procedure seguenti.

Funzioni di Query Store

Le funzioni facilitano l'uso di Query Store.

Viste del catalogo di Archivio query

Le informazioni su Query Store vengono presentate nelle viste del catalogo.

Query Store stored procedure

Per configurare Query Store vengono usate le stored procedure.

sp_query_store_consistency_check (Transact-SQL)1

1 In scenari estremi Query Store può avere uno stato di errore a causa di errori interni. A partire da SQL Server 2017 (14.x), in questo caso è sp_query_store_consistency_check possibile ripristinare Query Store eseguendo il stored procedure nel database interessato. Vedere sys.database_query_store_options per altri dettagli descritti nella descrizione actual_state_desc della colonna.

Principali scenari di utilizzo

Gestione delle opzioni

Questa sezione fornisce alcune linee guida per la gestione della funzionalità Archivio query.

Query Store stato

I dati della funzionalità Query Store vengono archiviati nel database utente ed è quindi previsto un limite per le dimensioni, che viene configurato con MAX_STORAGE_SIZE_MB. Se i dati in Archivio query raggiungono tale limite, lo stato cambia automaticamente da lettura/scrittura a sola lettura e la raccolta di nuovi dati viene interrotta.

Eseguire una query su sys.database_query_store_options per determinare se la funzionalità Query Store è attualmente attiva e se è in corso la raccolta delle statistiche di runtime.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Query Store stato del controllo è determinato dalla actual_state colonna . Se è diverso dallo stato previsto, la colonna readonly_reason contiene maggiori informazioni. Quando Query Store supera la quota, la funzionalità passa alla modalità read_only e fornisce un motivo. Per informazioni sui motivi, vedere sys.database_query_store_options (Transact-SQL).

Ottenere le opzioni di Archivio query

Per informazioni dettagliate sullo stato di Archivio query, eseguire l'istruzione seguente in un database utente.

SELECT * FROM sys.database_query_store_options;

Impostazione dell'intervallo di Archivio query

È possibile ignorare l'intervallo per l'aggregazione delle statistiche di runtime delle query (impostazione predefinita: 60 minuti). Il nuovo valore per l'intervallo viene esposto tramite la vista sys.database_query_store_options.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

I valori arbitrari non sono consentiti per INTERVAL_LENGTH_MINUTES. Usare uno dei seguenti: 1, 5, 10, 15, 30, 60 o 1440 minuti.

Nota

Ad Azure Synapse Analytics, la personalizzazione Query Store di configurazione, come illustrato in questa sezione, non è supportata.

Utilizzo dello spazio di Archivio query

Per controllare le dimensioni e i limiti correnti di Archivio query, eseguire l'istruzione seguente nel database utente.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Se lo spazio di archiviazione di Archivio query è esaurito, usare l'istruzione seguente per estenderlo.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Impostare le opzioni di Query Store

È possibile impostare più opzioni di Archivio query contemporaneamente con un'unica istruzione ALTER DATABASE.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Per un elenco completo delle opzioni di configurazione, vedere Opzioni ALTER DATABASE SET (Transact-SQL).

Pulizia dello spazio

Le tabelle interne di Archivio query vengono create nel filegroup PRIMARY durante la creazione del database e tale configurazione non è modificabile in un secondo momento. Se si esaurisce lo spazio, è possibile cancellare dati di Archivio query meno recenti usando l'istruzione seguente.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

In alternativa, è possibile cancellare solo dati di query ad hoc perché sono meno rilevanti per le ottimizzazioni query e l'analisi del piano, ma occupano molto spazio.

In Azure Synapse Analytics, la cancellazione dell'archivio query non è disponibile. I dati vengono conservati automaticamente negli ultimi 30 giorni.

Eliminare query ad hoc

Questa operazione elimina le query adhoc e interne dal Query Store in modo che il Query Store non esersi e rimuovono le query che è effettivamente necessario rilevare.

SET NOCOUNT ON
-- This purges adhoc and internal queries from 
-- the Query Store in the current database 
-- so that the Query Store does not run out of space 
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Per la cancellazione dei dati non più necessari, è possibile definire procedure personalizzate con logiche diverse.

Per rimuovere i dati non necessari, nell'esempio precedente viene usata la stored procedure estesa sp_query_store_remove_query. È anche possibile:

  • Usare sp_query_store_reset_exec_stats per cancellare le statistiche di runtime per un piano specificato.
  • Usare sp_query_store_remove_plan per rimuovere un singolo piano.

Controllo delle prestazioni e risoluzione dei problemi

Archivio query conserva la cronologia delle metriche relative a compilazione e runtime per tutte le esecuzioni delle query e questo consente di ottenere facilmente informazioni sul carico di lavoro. Le query di esempio seguenti possono essere utili nella baseline delle prestazioni e nell'analisi delle prestazioni delle query:

Ultime query eseguite nel database

Ultime n query eseguite nel database:

SELECT TOP 10 qt.query_sql_text, q.query_id,
    qt.query_text_id, p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;

Conteggi di esecuzione

Numero di esecuzioni per ogni query:

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,
    SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC;

Tempo medio di esecuzione più lungo

Numero di query con il tempo medio di esecuzione più lungo nell'ultima ora:

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

Numero massimo di operazioni di I/O fisiche medie

Numero di query con le operazioni di I/O fisiche medie più grandi nelle ultime 24 ore, con il numero medio di righe e il numero di esecuzioni corrispondenti:

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,
    q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,
    rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;

Query con più piani

Queste query sono particolarmente interessanti perché sono candidati a regressioni in seguito alla modifica del piano selezionato. La query seguente consente di identificare queste query unitamente a tutti i piani:

WITH Query_MultPlans
AS
(
SELECT COUNT(*) AS cnt, q.query_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
GROUP BY q.query_id
HAVING COUNT(distinct plan_id) > 1
)

SELECT q.query_id, object_name(object_id) AS ContainingObject,
    query_sql_text, plan_id, p.query_plan AS plan_xml,
    p.last_compile_start_time, p.last_execution_time
FROM Query_MultPlans AS qm
JOIN sys.query_store_query AS q
    ON qm.query_id = q.query_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt
    ON qt.query_text_id = q.query_text_id
ORDER BY query_id, plan_id;

Durate di attesa più alte

Questa query restituirà le prime 10 query con le durate di attesa più alte:

SELECT TOP 10
    qt.query_text_id,
    q.query_id,
    p.plan_id,
    sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC;

Nota

In Azure Synapse Analytics, le Query Store di esempio in questa sezione sono supportate ad eccezione delle statistiche di attesa, che non sono disponibili nelle DMV Azure Synapse Analytics Query Store.

Query regredite di recente nelle prestazioni

L'esempio di query seguente restituisce tutte le query in cui il tempo di esecuzione è raddoppiato nelle ultime 48 ore in seguito alla modifica del piano selezionato. Questa query confronta tutti gli intervalli di statistiche di runtime affiancati:

SELECT
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    rs1.runtime_stats_id AS runtime_stats_id_1,
    rsi1.start_time AS interval_1,
    p1.plan_id AS plan_1,
    rs1.avg_duration AS avg_duration_1,
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2,
    rsi2.start_time AS interval_2,
    rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi2
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
    AND rsi2.start_time > rsi1.start_time
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > 2*rs1.avg_duration
ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;

Per visualizzare le prestazioni di tutte le regressioni (non solo quelle correlate alla modifica della scelta del piano), AND p1.plan_id <> p2.plan_id rimuovere la condizione dalla query precedente.

Query con regressione cronologica nelle prestazioni

Confrontando l'esecuzione recente con l'esecuzione cronologica, la query successiva confronta l'esecuzione della query in base al periodo di esecuzione. In questo particolare esempio, la query confronta l'esecuzione nel periodo recente (1 ora) con il periodo della cronologia (ultimo giorno) e identifica quelle che hanno introdotto additional_duration_workload. Questa metrica viene calcolata come differenza tra l'esecuzione media recente e l'esecuzione media della cronologia moltiplicata per il numero di esecuzioni recenti. Rappresenta in effetti la quantità di esecuzioni recenti con durata aggiuntiva introdotte rispetto alla cronologia:

--- "Recent" workload - last 1 hour
DECLARE @recent_start_time datetimeoffset;
DECLARE @recent_end_time datetimeoffset;
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload
DECLARE @history_start_time datetimeoffset;
DECLARE @history_end_time datetimeoffset;
SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();

WITH
hist AS
(
    SELECT
        p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(distinct p.plan_id) AS num_plans
     FROM sys.query_store_runtime_stats AS rs
        JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
    WHERE (rs.first_execution_time >= @history_start_time
               AND rs.last_execution_time < @history_end_time)
        OR (rs.first_execution_time <= @history_start_time
               AND rs.last_execution_time > @history_start_time)
        OR (rs.first_execution_time <= @history_end_time
               AND rs.last_execution_time > @history_end_time)
    GROUP BY p.query_id
),
recent AS
(
    SELECT
        p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(distinct p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
        JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
    WHERE  (rs.first_execution_time >= @recent_start_time
               AND rs.last_execution_time < @recent_end_time)
        OR (rs.first_execution_time <= @recent_start_time
               AND rs.last_execution_time > @recent_start_time)
        OR (rs.first_execution_time <= @recent_end_time
               AND rs.last_execution_time > @recent_end_time)
    GROUP BY p.query_id
)
SELECT
    results.query_id AS query_id,
    results.query_text AS query_text,
    results.additional_duration_workload AS additional_duration_workload,
    results.total_duration_recent AS total_duration_recent,
    results.total_duration_hist AS total_duration_hist,
    ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
    ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM
(
    SELECT
        hist.query_id AS query_id,
        qt.query_sql_text AS query_text,
        ROUND(CONVERT(float, recent.total_duration/
                   recent.count_executions-hist.total_duration/hist.count_executions)
               *(recent.count_executions), 2) AS additional_duration_workload,
        ROUND(recent.total_duration, 2) AS total_duration_recent,
        ROUND(hist.total_duration, 2) AS total_duration_hist,
        recent.count_executions AS count_executions_recent,
        hist.count_executions AS count_executions_hist
    FROM hist
        JOIN recent
            ON hist.query_id = recent.query_id
        JOIN sys.query_store_query AS q
            ON q.query_id = hist.query_id
        JOIN sys.query_store_query_text AS qt
            ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);

Misure per garantire la stabilità delle prestazioni di query

Per le query eseguite più volte è possibile notare che SQL Server usa piani diversi che comportano durate e utilizzi diversi delle risorse. Con Query Store, è possibile rilevare quando le prestazioni delle query sono regredite e determinare il piano ottimale entro un periodo di interesse. È quindi possibile forzare il piano ottimale per le future esecuzioni delle query.

È anche possibile identificare incoerenze nelle prestazioni di una query con parametri (impostati sia automaticamente che manualmente). Tra i diversi piani, è possibile identificare il piano sufficientemente veloce e ottimale per tutti i valori dei parametri o per la maggior parte e forzare tale piano, mantenendo prestazioni prevedibili per il set più ampio di scenari utente.

Forzare un piano per una query (applicando criteri di utilizzo forzato)

Quando si forza un piano per una determinata query, SQL Server prova a forzare il piano in Query Optimizer. Se l'uso forzato del piano ha esito negativo, viene generato un XEvent e a query optimizer viene richiesto di ottimizzare in modo normale.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

Se si usa sp_query_store_force_plan, è possibile forzare solo piani che sono stati registrati da Archivio query come piani per tale query. In altre parole, gli unici piani disponibili per una query sono quelli già usati per eseguire tale query mentre Archivio query era attivo.

Nota

La forzatura dei piani Query Store non è supportata in Azure Synapse Analytics.

Supporto dell'uso forzato del piano per cursori fast forward e statici

A partire da SQL Server 2019 (15.x) e database SQL di Azure (tutti i modelli di distribuzione), Query Store consente di forzare i piani di esecuzione query per i cursori API e Transact-SQL Fast Forward e statici. A tale scopo, è possibile usare sp_query_store_force_plan o i report di Query Store di SQL Server Management Studio.

Rimuovere l'utilizzo forzato del piano per una query

Per fare di nuovo affidamento su SQL Server Query Optimizer per calcolare il piano di query ottimale, sp_query_store_unforce_plan usare per annullare l'applicazione del piano selezionato per la query.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

Vedere anche