Introduzione a SQL Server Query Store

05.09.24 04:24 PM Comment(s)

Esistono diversi strumenti nativi di SQL Server per poter monitorare il carico di lavoro di un’istanza: profiler, trace, extended events. Tuttavia, alcuni sono deprecati, altri richiedono invece particolare attenzione, conoscenze e configurazione.

A partire da SQL Server 2016 è stato introdotto un nuovo strumento: SQL Server Query Store.

Query Store è uno strumento che agisce come un “registratore”, fornendo una preziosa cronologia delle esecuzioni delle query e delle prestazioni nel tempo. Questo strumento è progettato per aiutare gli amministratori di database a identificare rapidamente le query problematiche e a risolvere i problemi di prestazioni.

Configurazione di SQL Server Query Store

Per iniziare a utilizzare Query Store, è necessario abilitarlo a livello di database. Questo può essere fatto tramite SQL Server Management Studio (SSMS) o con una query T-SQL.

Ecco i passaggi per l’abilitazione e la configurazione:

1) Abilitazione di Query Store: Nelle proprietà del database, sotto “Query Store”, impostare “Operation Mode (Requested)” da “Off” a “Read Write”.

Esiste un terzo stato “Read only” dove non vengono catturate nuove queries. Solitamente Query Store entra in questo stato quando viene raggiunta la dimensione massima impostata per lo strumento (Max Size).

2) Configurazione delle Opzioni:

Data Flush Interval (Minutes): ogni quanti minuti le statistiche delle query e i piani di esecuzione vengono salvati dalla memoria al disco. Lasciare il valore di default di 15

Statistics Collection Interval: definisce l’intervallo di aggregazione delle statistiche delle query all’interno dello strumento Query Store. Intervalli più piccoli aumentano l’overhead di registrazione. Valutare in base alle esigenze ma generalmente si può lasciare di 1 ora.

Max Plans Per Query: indica quanti piani di esecuzione possono essere registrati per singola query. Lasciare il valore 200.

Max Size (MB): i dati registrati da Query Store vengono salvati su delle tabelle di sistema all’interno del database. Tale parametro indica la quantità di MB che si vuole dedicare a questi dati. Dipende chiaramente dal carico di lavoro che vi è nell’istanza. Diciamo che dai 2 ai 4 GB possono essere dei valori corretti per gran parte delle istanze.

Query Store Capture Mode: rappresenta la tipologia di query che si vogliono registrare. Per un utilizzo standard lasciare su Auto. In questo modo Query Store registrerà solamente le query più significative in termini di metriche. Assolutamente non mettere mai l’impostazione su All in quanto verrebbero registrate tutte le query rendendo confusa e poco significativa la raccolta, oltre che si raggiungerebbe in fretta la quota Max Size (MB).

Size Based Cleanup Mode: se impostato su Auto, quando si raggiunge il 90% di Max Size (MB) Query Store inizia a rimuovere i dati relativi a query più vecchie e meno onerose, riportando la quantità di dati salvati all’80% di Max Size (MB). Se impostato su Off, Query Store non esegue alcuna pulizia di dati vecchi e quando viene raggiunto il 90% di Max Size (MB) entra nello stato Read Only. Di norma lasciare su Auto.

Stale Query Threshold (Days): rappresenta per quanti giorni i dati vengono mantenuti nel Query Store. Il default è di 30 giorni.

Wait Statistics Capture Mode: determina se si vuole registrare anche le statistiche di wait per le query. Il default è On.

Di seguito uno schema rappresentativo dell’architettura di SQL Server Query Store:

Reportistica di SQL Server Query Store

Una volta attivato Query Store inizierà a raccogliere i dati relativi alle query.

Tramite SSMS è possibile espandere il nodo del database su cui si è attivato Query Store e trovare la sezione dedicata alla reportistica dei dati salvati.

I built-in report disponibili sono i seguenti:

Regressed Queries: questo report mostra tutte le query che hanno subito una regressione delle performance nell’intervallo di tempo considerato. È possibile utilizzare questo report per identificare le query che sono diventate più lente o che consumano più risorse rispetto a prima.

Overall Resource Consumption: questo report mostra il consumo complessivo di risorse delle query in uno specifico intervallo di tempo. Di default i risultati sono mostrati in riferimento all’ultimo mese e relativamente a 4 categorie di risorse: durata, tempo di utilizzo CPU, letture logiche e numero di esecuzioni.

Top Resource Consuming Queries: questo report mostra le query che consumano principalmente risorse in base al tempo della CPU, alle letture logiche, alle scritture logiche, alle letture fisiche o alla durata nello specifico intervallo temporale selezionato. È possibile utilizzare questo report per approfondire i dettagli di ciascuna query e visualizzarne il piano di esecuzione e le statistiche.

Queries With Forced Plans: questo report mostra le query a cui è applicato un piano di esecuzione forzato. Può essere utilizzato per vedere in che modo la forzatura di un piano di esecuzione influisce sulle prestazioni delle query e se è necessario annullarlo o meno.

Queries With High Variation: questo report mostra le query che hanno avuto un’alta variazione nell’intervallo considerato rispetto ad una specifica metrica selezionata (durata, tempo di cpu, utilizzo di memoria, etc).

Query Wait Statistics: questo report permette di vedere le principali wait che affliggono il database e per ogni categoria di wait è possibile vedere quali sono le query che contribuiscono a questa wait.

Tracked Queries: questo report mostra le query che sono state contrassegnate come tracciate e serve per monitorare il comportamento di specifiche query selezionate.

Vantaggi e svantaggi nell’utilizzo di SQL Server Query Store

Query Store rappresenta sicuramente lo strumento di monitoraggio nativo in SQL Server per eccellenza. Basti pensare che, a partire da SQL Server 2022 e per Azure SQL database, Microsoft ha deciso di attivare di default lo strumento Query Store alla creazione di ogni nuovo database.

I possibili vantaggi di uno strumento di questo tipo sono i seguenti:

Monitoraggio delle Prestazioni: fornisce una visione storica delle prestazioni delle query, facilitando l’identificazione di regressioni e possibili miglioramenti.

Risoluzione dei Problemi: aiuta a diagnosticare rapidamente i problemi di prestazioni e a testare gli effetti delle modifiche apportate.

Gestione dei Piani di Esecuzione: permette di analizzare i piani di esecuzione delle query ed eventualmente forzare l’utilizzo di piani di esecuzione specifici per query problematiche.

Ci sono anche dei possibili svantaggi da tenere in considerazione:

Overhead di Prestazioni: se non configurato correttamente, Query Store può introdurre un sovraccarico di prestazioni. Ci si aspetta un overhead di utilizzo di CPU di pochi punti percentuali (1-3%). Tuttavia, bisogna precisare che nelle primissime versioni (SQL Server 2016) lo strumento era ancora poco ottimizzato. Nei rilasci successivi sono state apportate molte migliorie e ottimizzazione sul piano delle performance (nonché anche di alcune metriche registrate).

Gestione dello Spazio: la raccolta di grandi quantità di dati può portare a problemi di spazio sul database e sul disco. È quindi necessario monitorare tale aspetto e configurare in modo corretto lo strumento.

In conclusione, Query Store è uno strumento potente e versatile che, se utilizzato coscientemente, può migliorare la gestione delle prestazioni del database. Tuttavia, è importante bilanciare i benefici con la consapevolezza degli overhead potenziali e gestire attentamente la configurazione e l’uso dello spazio.

di Matteo Dal Bianco, pubblicato il 5 settembre 2024

Introduzione a SQL Server Query Store

Configurazione di SQL Server Query Store

Reportistica di SQL Server Query Store

Vantaggi e svantaggi nell’utilizzo di SQL Server Query Store

Datamaze Srl