Monitorare le prestazioni tramite Query Store
Si applica a: 
SQL Server 2016 (13.x) e versioni successive database SQL di Azure Istanza gestita di SQL di Azure database SQL di Azure Synapse Analytics (solo pool SQL dedicato) in Microsoft Fabric
La funzionalità Query Store offre informazioni dettagliate sulla scelta e sulle prestazioni del piano di query per SQL Server, database SQL di Azure, database SQL dell'infrastruttura, Istanza gestita di SQL di Azure e Azure Synapse Analytics. Query Store 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.
È possibile configurare Query Store usando 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 utilizzabili e sull'ottimizzazione delle prestazioni con Query Store, vedere Ottimizzazione delle prestazioni con Query Store.
- Per informazioni sul modellamento dei piani di query senza modificare il codice dell'applicazione, vedere Hint di Query Store.
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.
Abilitare Query Store
- Query Store è abilitato per impostazione predefinita per le nuove versioni di database SQL di Azure e 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). È abilitato per impostazione predefinita nella modalità
READ_WRITEper 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), si consiglia di 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, 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
Usare l'istruzione ALTER DATABASE per abilitare Query Store per un determinato database. Ad esempio:
ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
Le opzioni per configurare Query Store nel database SQL di Infrastruttura con ALTER DATABASE sono attualmente limitate.
In Azure Synapse Analytics abilitare 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
Non è possibile abilitare Query Store per i database master 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 piani per istruzioni DML, ad esempio SELECT, INSERT, UPDATE, DELETE, MERGE e BULK INSERT.
Per impostazione predefinita, Query Store non raccoglie piani per istruzioni DDL, ad esempio CREATE INDEX e altro. Query Store acquisisce l'utilizzo cumulativo delle risorse raccogliendo piani per le istruzioni DML sottostanti. Ad esempio, Query Store può visualizzare le istruzioni SELECT e INSERT eseguite internamente per compilare un nuovo indice.
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 consente a 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 informazioni sulle query, sui relativi piani e sulle statistiche relative al tempo di compilazione e al tempo di esecuzione dal Query Store.
SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;
Query Store per le repliche secondarie
Si applica a: SQL Server, a partire da SQL Server 2022 (16.x)
La funzione Query Store per le repliche secondarie consente di usare le stesse funzionalità di Query Store nei carichi di lavoro di replica secondaria disponibili per le repliche primarie. Quando è abilitata la funzione Query Store per le repliche secondarie, le repliche inviano le informazioni sull'esecuzione della query che normalmente vengono archiviate in Query Store alla replica primaria. La replica primaria mantiene quindi i dati su disco all'interno del proprio Query Store. In sostanza, è presente un Query Store condiviso tra la replica primaria e tutte le repliche secondarie. Query Store esiste nella replica primaria e archivia insieme i dati per tutte le repliche.
Per informazioni complete su Query Store per le repliche secondarie, vedere Query Store per repliche secondarie del gruppo di disponibilità AlwaysOn.
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, le viste di Query Store 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. Il riquadro Query regredite mostra le query e i piani in Query Store. 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), Grado di parallelismo, 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.
Cercare query di attesa
A partire da SQL Server 2017 (14.x) e nel database SQL di Azure sono a disposizione degli utenti di Query Store statistiche di attesa per query.
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 18.0 o versioni successive. 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).
Scegliere una categoria di attesa selezionando la 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.
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. Per le varie categorie di attesa è necessario un'analisi di completamento diversa per risolvere il problema. Per i tipi di attesa della stessa categoria la risoluzione dei problemi è invece molto simile. Specificando la query interessata come prima nelle attese, si indica la parte mancante necessaria a completare le analisi in modo corretto.
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).
Per determinare le opzioni correnti di Query Store, eseguire una query sulla vista sys.database_query_store_options. 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
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
È possibile visualizzare e gestire Query Store 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 Query Store
Le informazioni su Query Store vengono presentate nelle viste del catalogo.
Stored procedure di 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), se si verifica un errore, è possibile recuperare Query Store eseguendo la stored procedure sp_query_store_consistency_check nel database interessato. Per altri dettagli della descrizione della colonna actual_state_desc, vedere sys.database_query_store_options.
Manutenzione di Query Store
Le procedure consigliate e le raccomandazioni per la manutenzione e la gestione di Query Store sono state approfondite in questo articolo: Procedure consigliate per la gestione di Query Store.
Controllo delle prestazioni e risoluzione dei problemi
Per altre informazioni sull'ottimizzazione delle prestazioni con Query Store, vedere Ottimizzare le prestazioni con Query Store.
Altri argomenti sulle prestazioni:
Contenuto correlato
- Stored procedure di Query Store (Transact-SQL)
- Viste del catalogo di 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 di Archivio query con OLTP in-memoria
- Procedure consigliate per Query Store
- Procedure consigliate per la gestione di Query Store
- Ottimizzare le prestazioni con Query Store
- Hint di Query Store
- Scenari di utilizzo dell'archivio query
- Aprire Monitoraggio attività (SQL Server Management Studio)