Monitorare le prestazioni tramite Query Store
Si applica a:
SQL Server 2016 (13.x) e versioni successiveDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse Analytics (solo pool SQL dedicato)Database SQL 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. Query Store acquisisce automaticamente una cronologia delle query, dei piani e delle statistiche di esecuzione, conservandoli 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 il Query Store
- Query Store è abilitato per impostazione predefinita per i nuovi database di Azure SQL Database e Azure SQL Managed Instance.
- 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, consultare Best Practice con il Query Store.
Informazioni dell'Archivio delle 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. I piani vengono anche rimossi dalla cache dei piani a causa della pressione della 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. Questa è chiamata forzatura del piano. L'applicazione del forcing del piano in Query Store viene fornita mediante un meccanismo simile al suggerimento di query USE PLAN, ma non richiede alcuna modifica 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 di tipo 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:
- Individua e correggi rapidamente una regressione delle prestazioni di un piano forzando l'utilizzo del 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.
- Verificare 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 il motivo dell'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 distinti che è possibile archiviare per una query nello store dei 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 nelle 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 le repliche secondarie nel gruppo di disponibilità Always On.
Utilizzare la funzione Query regresse
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 retrocesse mostra le query e i piani nel 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 applicare un piano, selezionare una query e il relativo 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.
Trova query in attesa
A partire da SQL Server 2017 (14.x) e nel database SQL di Azure, sono disponibili nel Query Store statistiche di attesa per query nel 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 18.0 o versioni successive. Il riquadro Statistiche di attesa query visualizza un grafico a barre contenente le categorie di attesa principali nel 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.
Usa l'elenco a discesa nella parte superiore per filtrare le query in base a diversi criteri di tempo di attesa per la categoria di attesa selezionata: media, massimo, minimo, deviazione standard e totale (impostazione di default). Selezionare un piano per visualizzare il piano di query con interfaccia grafica. Sono disponibili pulsanti per visualizzare la query originale, forzare e annullare un piano di query e aggiornare la visualizzazione.
Le categorie di attesa raggruppano tipi di attesa diversi in bucket simili per natura. Per le diverse categorie di attesa è necessaria un'analisi di follow-up differente per risolvere il problema. Tuttavia, i tipi di attesa della stessa categoria portano a esperienze di risoluzione dei problemi molto simili. Fornire la query interessata in cima alle attese rappresenterebbe l'elemento mancante per completare con successo la maggior parte di tali 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 | Nuova esperienza | 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. Si consiglia di utilizzare l'hint di query MAX_GRANT_PERCENT per queste query o per quelle 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 | Trova 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 elevate della CPU nel Query Store per specifiche query | Individuare le prime query con maggiore utilizzo della CPU nel 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. Focalizzarsi sull'ottimizzazione di queste query: potrebbe esserci 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.
Procedure memorizzate del 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 , vedere actual_state_desc.
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
- Procedure memorizzate del Query Store (Transact-SQL)
- Viste del catalogo di Query Store (Transact-SQL)
- sys.database_query_store_options (Transact-SQL)
- Statistiche delle query in tempo reale
- Monitoraggio attività
- Come l'archivio delle query raccoglie dati
- Monitoraggio e ottimizzazione delle prestazioni
- Strumenti per il monitoraggio e l'ottimizzazione delle prestazioni
- Uso del Query Store 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)