In questa guida vedremo insieme come utilizzare Query Store in SQL Server. Query store è stato introdotto in Sql Server 2016 ed è stato, probabilmente, la funzionalità più attesa e discussa.
Questa sarà una panoramica abbastanza breve – avresti bisogno di un libro per coprirlo in dettaglio – ma si spera che questo ti dia un assaggio di quanto sarà utile e come iniziare.
Quello che fa, a livello di base, è in realtà abbastanza semplice. Memorizza solo le informazioni relative all’esecuzione delle query nel tempo.
Possiamo definirlo come la”scatola nera” di SQL Server, che cattura una cronologia delle query eseguite, statistiche di esecuzione del runtime di query, piani di esecuzione ecc. Rispetto a un database specifico.
Queste informazioni consentono di identificare i problemi di prestazioni causati dalle modifiche al piano di query e di risolvere i problemi individuando rapidamente le differenze di prestazioni, anche dopo il riavvio o l’aggiornamento di SQL Server. Tutti i dati acquisiti dall’Archivio query di SQL Server vengono archiviati su disco.
Gli scenari comuni in cui la funzionalità Archivio query di SQL Server può essere utile:
- Trova le query più costose per CPU, I / O, memoria ecc.
- Ottieni la cronologia completa delle esecuzioni di query
- Ottenere informazioni sulle regressioni delle query (un nuovo piano di esecuzione generato dal motore di query è peggiore di quello precedente).
- Determinare quante volte una query è stata eseguita nell’intervallo di tempo indicato
Sommario
Abilitare Query Store in Sql Server
Query Store in Sql Server è una configurazione a livello di database. È importante capirlo e che le informazioni archiviate sono effettivamente archiviate all’interno delle tabelle di sistema nel database.
Ciò significa che se si esegue il backup e il ripristino del database, le informazioni vengono mantenute. Inoltre, cosa molto importante, le informazioni vengono archiviate in modo asincrono, quindi non dovrebbe esserci alcun impatto sulle prestazioni delle query eseguite.
Ci sarà ovviamente un sovraccarico complessivo del server nel punto in cui i dati vengono salvati, ma nella maggior parte dei casi non dovrebbe essere troppo significativo.
- Per abilitare Query Store in SQL Server per un database in SQL Server on-promise, fare clic con il pulsante destro del mouse su un database in Esplora oggetti e scegliere l’opzione Proprietà dal menu di scelta rapida.
- Nella finestra di dialogo Proprietà database nella sezione Selezionare una pagina selezionare la pagina Query Store
- Dalla casella a discesa Operation Mode (Requested), scegliere l’elemento Lettura Scrittura.
- Non appena viene scelto l’elemento Lettura scrittura, gli altri campi nella casella a discesa Operation Mode (Requested) verranno precompilati con i valori predefiniti.
- Premendo il pulsante OK nella finestra di dialogo Proprietà database, l’archivio query di SQL Server viene abilitato per acquisire i piani di esecuzione delle query e le informazioni di runtime.
- Per verificare che l’archivio query di SQL Server sia abilitato nel database scelto, passare a Esplora oggetti, aggiornare ed espandere il database. La cartella Archivio query di SQL Server verrà visualizzata con l’elenco dei report predefiniti disponibili.
Query Store in Sql Server – Esplora Oggetti Query Store in Sql Server – Impostazione Operation Mode Query Store in Sql Server – Selezione Opzione Query Store Query Store in Sql Server – Selezione Modalità operativa Query Store in Sql Server – verifica attivazione Query Store
Per abilitare l’archivio query di SQL Server utilizzando T-SQL, eseguire l’istruzione seguente in una finestra di query:
ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = ON;
Nota: l’archivio query di SQL Server non può essere abilitato per il database master o tempdb.
L’archivio query di SQL Server avrà un impatto sulle prestazioni di SQL Server in media del 3-5% per indicazione di Microsoft.
Opzioni Query Store in Sql Server
La prima opzione nella pagina Archivio query di SQL Server della finestra di dialogo Proprietà database è l’opzione Operation Mode (Actual):
Questa opzione è disabilitata e non può essere modificata. Questa opzione indica lo stato dell’archivio query di SQL Server. Esistono tre modalità dell’archivio query di SQL Server e queste sono Disattivato, Sola lettura e Lettura scrittura.
Disattivato: l’archivio query di SQL Server disattivato
Sola lettura: questa modalità indica che le nuove statistiche di Query Runtime o i piani eseguiti non verranno monitorati (raccolti)
Lettura scrittura: consente di acquisire piani eseguiti da query e statistiche di runtime di query
Nella modalità operativa (richiesta), nella casella a discesa, è possibile impostare le stesse opzioni menzionate per l’opzione precedente. L’impostazione dei valori avrà un impatto diretto sullo stato dell’archivio query.
Ad esempio, se in modalità operativa (richiesta), dalla casella a discesa viene scelto il valore di sola lettura, il codice T-SQL equivalente per la stessa opzione è:
ALTER DATABASE AdventureWorks2014SET QUERY_STORE = ON ( OPERATION_MODE = READ_ONLY );Data Flush Interval (Minutes)
Nell’opzione Data Flush Interval (Minutes) è possibile impostare un intervallo in minuti che mostra la frequenza con cui le statistiche di runtime di query e i piani di esecuzione delle query verranno scaricati dalla memoria dell’istanza di SQL Server al disco. Per impostazione predefinita, questa opzione è impostata su 15 minuti:
Se questa opzione è impostata su un valore inferiore rispetto alla frequenza degli svuotamento avremo impatto negativo sulle prestazioni dell’istanza di SQL Server. Tuttavia, se il valore viene aumentato, più informazioni di Query Store verranno inserite nella memoria dell’istanza di SQL Server prima che venga scaricata su disco, il che aumenta il rischio di perdere tali dati in caso di riavvio/arresto anomalo di SQL Server.
Di seguito è riportato il codice T-SQL per l’impostazione dell’opzione Data Flush Interval (Minutes):
ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON
(
DATA_FLUSH_INTERVAL_SECONDS = 900
);Statistics Collection Interval
L’opzione Statistics Collection Interval determina la dimensione delle sezioni temporali in cui vengono aggregate le metriche delle prestazioni delle query.. Per impostazione predefinita, è impostato su 60 minuti.
Il codice T-SQL per l’impostazione dell’opzione Intervallo raccolta statistiche:
ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON
(
INTERVAL_LENGTH_MINUTES = 1440
);Nota: nel codice T-SQL per l’opzione Intervallo raccolta statistiche è possibile impostare i seguenti valori in minuti 1, 5, 10, 15, 30, 60, 1440.
Quando viene eseguita l’istruzione, verrà visualizzato il seguente messaggio:
La lunghezza dell'intervallo dell'archivio query di SQL Server
messaggi 12432, livello 16, stato 1, riga 1 non può essere modificata perché è stato fornito un valore non valido. Riprova con un valore valido (1, 5, 10, 15, 30 e 60).
Messaggio 5069, livello 16, stato
1, riga 1 istruzione ALTER DATABASE non riuscita.Max Size(MB)
L’opzione Max Size(MB) consente di configurare la dimensione massima dell’archivio query di SQL Server. Per impostazione predefinita, la dimensione massima dell’archivio query di SQL Server è impostata su 100 MB.
La dimensione del Query Store in SQL Server non cresce automaticamente e una volta che raggiunge la dimensione massima, la modalità operativa passerà automaticamente alla modalità di sola lettura e non verranno raccolte nuove statistiche di esecuzione delle query e runtime di query.
Il codice T-SQL per l’impostazione della dimensione massima dell’archivio query di SQL Server è:
ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON
(
MAX_STORAGE_SIZE_MB = 1024
);Query Store Capture Mode
L’opzione Query Store Capture Mode determina il tipo di query che verrà acquisito nell’archivio query. Per impostazione predefinita, l’opzione Query Store Capture Mode è impostata su All, il che significa che ogni query eseguita verrà archiviata nell’archivio query di SQL Server in esecuzione nel database.
Quando l’opzione Query Store Capture Mode è impostata su Auto, l’archivio query di SQL Server tenterà di valutare l’acquisizione delle query in base alla priorità e tenterà di ignorare le query eseguite di rado e altre query ad hoc.
Inoltre, c’è il terzo valore nella casella a discesa Query Store Capture Mode che è Nessuno. Quando viene scelto il valore Nessuno, l’archivio query di SQL Server non raccoglierà informazioni per le nuove query e continuerà a raccogliere informazioni solo sulle query registrate in precedenza. Il codice T-SQL per impostare questa opzione è:
ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON
(
QUERY_CAPTURE_MODE = ALL
);Size Based Cleanup Mode
L’opzione Size Based Cleanup Mode consente di pulire i dati dell’archivio query di SQL Server quando la dimensione massima nell’opzione Dimensione massima (MB) viene raggiunta al 90% della capacità.
Il processo di pulizia rimuoverà i dati di query meno vecchi e meno costosi. Il processo di pulizia si interrompe quando viene raggiunto l’80% della dimensione massima nell’opzione Dimensione massima (MB). Per impostazione predefinita, questa opzione è impostata su Auto.
Se nella casella di riepilogo a discesa il Size Based Cleanup Mode è impostato il valore Off, il processo di pulizia non verrà eseguito quando la dimensione dell’archivio query di SQL Server raggiunge il 90% della dimensione massima e l’archivio query di SQL Server passerà alla modalità di sola lettura quando viene raggiunta la dimensione massima. Il codice T-SQL per impostare questa opzione è:
ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON
(
SIZE_BASED_CLEANUP_MODE = AUTO
);Stale Query Threshold (Days)
L’opzione Stale Query Threshold (Days) è utilizzata per definire per quanto tempo i dati rimarranno nell’archivio query di SQL Server. Per impostazione predefinita, è impostato per 30 giorni.
Il codice T-SQL per impostare questa opzione è:
ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON
(
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
)Un’altra opzione che puoi abilitare via T-SQL e non presente in Query Store è il MAX_PLANS_PER_QUERY:
ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON
(
MAX_PLANS_PER_QUERY=200
);Con questa opzione è possibile impostare il numero massimo di piani di esecuzione che verranno archiviati nell’archivio query di SQL Server per query. Per impostazione predefinita, questo è impostato su 200 piani di esecuzione per query.
L’ultima opzione di Query Store in SQL Server è un’opzione che cancella/elimina tutti i dati nell’archivio query di SQL Server premendo il pulsante Purge Query Data.
Lo stesso può essere fatto eseguendo il seguente codice T-SQL:
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE CLEAR;Report Query Store in SQL Server
Come accennato ad inizio articolo, una volta abilitato, Query Store in SQL Server inizierà a raccogliere statistiche sulle query e piani di esecuzione di query. I report incorporati utilizzano i dati raccolti analizzandoli e li mostrano in formato griglia o grafico a seconda di ciò che è impostato nei report.
Attualmente, ci sono sei report incorporati:
- Query regresse è un report incorporato che mostra tutte le query che le matrici di esecuzione sono degradate in un intervallo di tempo specifico (ultima ora, giorno, settimana).
Per impostazione predefinita, vengono visualizzate le prime 25 query regresse nell’ultima ora.
Qui, è possibile impostare diverse opzioni per visualizzare le informazioni desiderate. Ad esempio, se si desidera visualizzare la data non elaborata anziché il grafico, premere il pulsante per visualizzare le query regresse in un formato griglia.
- Il report Overall Resource Consumption mostra il consumo di risorse di riepilogo durante il set di tempo specifico. Per impostazione predefinita, i risultati vengono visualizzati per l’ultimo mese e i risultati sono mostrati in quattro grafici: Durata, Tempo CPU, Letture logiche e Conteggio esecuzione.
Per impostare ulteriori report grafici, intervallo di tempo e intervallo di aggregazione, premere il pulsante Configura e verrà visualizzata la finestra di dialogo Configura consumo complessivo di risorse in cui è possibile impostare diverse opzioni per il rapporto Consumo complessivo di risorse.
- ll report integrato Top Resource Consuming Queries mostra, per impostazione predefinita, le prime 25 query su database specifici che consumano la maggior parte delle risorse come il tempo della CPU, il consumo di memoria, le letture fisiche ecc. in un set di tempo specifico
Con il report incorporato Tracked Query è possibile tenere traccia delle statistiche delle query e dei piani di esecuzione delle query specifica nel tempo. Nella casella di testo Query di monitoraggio immettere l’ID della query (ad esempio 205) e premere il pulsante di riproduzione verde accanto alla casella Query di monitoraggio.
Il report predefinito Query con piani forzati mostra tutti i piani di esecuzione forzata per query specifiche.
Per forzare SQL Server a utilizzare un piano di esecuzione specifico per la query specifica, nei report incorporati Query regresse, Query che consumano risorse principali, Query con variazione elevata o Query con traccia selezionare innanzitutto l’ID piano di esecuzione e fare clic sul pulsante Forza piano. Premere il pulsante Sì nella finestra di messaggio di conferma.
In questo modo, SQL Server impone di utilizzare questo piano di esecuzione per query specifiche d’ora in poi quando tale query viene eseguita.
Conclusione
Query Store di SQL Server è una funzionalità potente, per gli utenti di SQL Server 2016 e versioni successive, consente di tenere traccia dell’esecuzione di query e dei piani di esecuzione delle query, di monitorare e analizzare le prestazioni delle query e di mostrare i risultati nei report incorporati.
Ciò fornisce nuove potenti funzionalità rispetto ai metodi precedenti che gli amministratori di database sono stati relegati a utilizzare nelle versioni precedenti di SQL Server.
Scrivimi nei commenti se conosci questa funzionalità di SQL Server.
