Monitorare le prestazioni tramite Query Store
Si applica a:
SQL Server 2016 (13.x) e versioni successive Azure SQL DatabaseIstanza gestita di SQL di AzureAzure Synapse Analytics (solo pool SQL dedicato)
La funzionalità di Query Store offre informazioni dettagliate sulla scelta e sulle prestazioni del piano di query per SQL Server, database Azure SQL, Istanza gestita di SQL di Azure e Azure Synapse Analytics. Il Query Store semplifica la risoluzione dei problemi delle prestazioni consentendo di individuare rapidamente le differenze di prestazioni causate dalle modifiche del piano 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 dell'archivio query nel database SQL di Azure, vedere Uso dell'archivio query nel database SQL di Azure.
- Per informazioni sull'individuazione di informazioni interattive e sull'ottimizzazione delle prestazioni con il Query Store, vedere Ottimizzare le prestazioni con il Query Store.
- Per informazioni sulla modellazione dei piani di query senza modificare il codice dell'applicazione, vedere Query Store hint.
Importante
Se si usa Query Store per informazioni dettagliate sui carichi di lavoro JIT in SQL Server 2016 (13.x), pianificare l'installazione delle correzioni di scalabilità delle prestazioni in KB 4340759 il prima possibile.
Abilitare Query Store
- Query Store è abilitato per impostazione predefinita per i nuovi database di Azure SQL e di Istanza gestita di SQL di Azure.
- Query Store non è abilitato per impostazione predefinita per SQL Server 2016 (13,x), SQL Server 2017 (14,x), SQL Server 2019 (15,x). È abilitata per impostazione predefinita nella
READ_WRITEmodalità per i nuovi database a partire da SQL Server 2022 (16.x). Per consentire alle funzionalità di tenere traccia della cronologia delle prestazioni, risolvere i problemi correlati al piano di query e abilitare nuove funzionalità in SQL Server 2022 (16.x), è consigliabile abilitare Query Store in tutti i database. - Query Store non è abilitato per impostazione predefinita per i nuovi database di Azure Synapse Analytics.
Usare la pagina Query Store in SQL Server Management Studio
In Esplora oggetti fare clic con il pulsante destro del mouse su un database e quindi scegliere Proprietà.
Nota
Richiede almeno la versione 16 di Management Studio.
Nella finestra di dialogo Proprietà database selezionare la pagina Archivio query .
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 di sintassi correlate alla Query Store, vedere Opzioni ALTER DATABASE SET (Transact-SQL).For more syntax options related to the Query Store, see ALTER DATABASE SET Options (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 qualsiasi query specifica in SQL Server in genere si evolvono nel tempo a causa di diversi motivi, ad esempio modifiche alle statistiche, modifiche dello schema, creazione/eliminazione di indici e così via. La cache delle procedure (in cui sono archiviati i piani di query memorizzati nella cache) archivia 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 origine di informazioni che consentono di risolvere i problemi relativi alle 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 Azure SQL Database, Query Store include una dimensione che tiene traccia delle statistiche di attesa. L'esempio seguente consente al Query Store di raccogliere statistiche di attesa.
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;
Query Store per le repliche secondarie
Si applica a: SQL Server (a partire da SQL Server 2022 (16.x))
La funzionalità Query Store per le repliche secondarie consente la stessa funzionalità di Query Store nei carichi di lavoro di replica secondaria disponibili per le repliche primarie. Quando Query Store per le repliche secondarie è abilitata, le repliche inviano le informazioni di esecuzione della query che normalmente verrebbero archiviate nella Query Store alla replica primaria. La replica primaria mantiene quindi i dati su disco all'interno del proprio Query Store. In sostanza, esiste una Query Store condivisa tra le repliche primarie e tutte le repliche secondarie. Il Query Store esiste nella replica primaria e archivia i dati per tutte le repliche insieme.
Per informazioni complete sulle Query Store per le repliche secondarie, vedere Query Store per le repliche secondarie del gruppo di disponibilità di Always On.
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.
Nota
Per Azure Synapse Analytics, Query Store viste sono disponibili in Viste di sistema nella parte del database del riquadro Esplora oggetti.
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.
Per forzare un piano, selezionare una query e un piano, quindi selezionare Forza piano. È possibile forzare solo piani che sono stati salvati dalla funzionalità del piano di query e che sono ancora presenti nella relativa cache.
Trovare query in attesa
A partire da SQL Server 2017 (14.x) e Azure SQL Database, le statistiche di attesa per query nel tempo sono disponibili in Query Store.
In Query Store i tipi di attesa sono raggruppati in categorie di attesa. Il mapping delle categorie di attesa ai tipi di attesa è disponibile in sys.query_store_wait_stats (Transact-SQL) .
Selezionare Statistiche attesa query per aprire il riquadro Statistiche 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).
Selezionare una categoria di attesa selezionando sulla barra e visualizzando una visualizzazione dei dettagli nella categoria di attesa selezionata. Questo nuovo grafico a barre contiene le query che hanno contribuito a tale categoria di attesa.
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. Diverse categorie di attesa 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 in cima alle attese sarebbe la parte mancante per completare la maggior parte di tali indagini correttamente.
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 query sulla sys.database_query_store_options vista per determinare le opzioni correnti del Query Store. Per altre informazioni sui valori, vedere sys.database_query_store_options.
Per esempi sull'impostazione delle opzioni di configurazione tramite istruzioni Transact-SQL, vedere Gestione delle opzioni.
Nota
Per Azure Synapse Analytics, Query Store può essere abilitato come in altre piattaforme, ma non sono supportate opzioni di configurazione aggiuntive.
Viste, funzioni e procedure correlate
Visualizzare e gestire Query Store tramite Management Studio o usando le viste e le procedure seguenti.
funzioni Query Store
Le funzioni facilitano l'uso di Query Store.
viste del catalogo Query Store
Le informazioni su Query Store vengono presentate nelle viste del catalogo.
stored procedure Query Store
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, Query Store può essere ripristinata eseguendo la sp_query_store_consistency_check stored procedure nel database interessato. Vedere sys.database_query_store_options per altri dettagli descritti nella descrizione della actual_state_desc colonna.
manutenzione Query Store
Le procedure consigliate e le raccomandazioni per la manutenzione e la gestione dei Query Store sono state espanse in questo articolo: Procedure consigliate per la gestione delle Query Store.
Controllo delle prestazioni e risoluzione dei problemi
Per altre informazioni sull'ottimizzazione delle prestazioni con Query Store, vedere Ottimizzare le prestazioni con il Query Store.
Altri argomenti sulle prestazioni:
Vedi anche
- stored procedure Query Store (Transact-SQL)
- viste del catalogo Query Store (Transact-SQL)
- sys.database_query_store_options (Transact-SQL)
- Statistiche sulle query dinamiche
- Monitoraggio attività
- Modalità di raccolta dei dati dell'archivio query
- Monitoraggio e ottimizzazione delle prestazioni
- Strumenti per il monitoraggio e l'ottimizzazione delle prestazioni
- Uso dell'archivio query con OLTP in memoria