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:
Fonte notizia
www.datamaze.it blogs post introduzione-a-sql-server-query-store
