IndexOptimize, Update Statistics

Datamaze
11.07.24 01:28 PM Comment(s)

Nell'articolo precedente abbiamo trattato la stored procedure IndexOptimize di Ola Hallengren, un valido strumento per la manutenzione degli indici. Opportunamente parametrizzata, questa procedura offre anche la possibilità di aggiornare le statistiche di SQL Server con alcune opzioni davvero interessanti. Andiamo ora a completare la panoramica delle funzionalità disponibili, ponendo il focus sull’aggiornamento delle statistiche.


Cosa sono le statistiche (SQL Server Statistics)?

Le statistiche sono una raccolta di informazioni salvate da SQL Server, ovvero dati fondamentali per l'ottimizzazione delle prestazioni delle query. 

Questi dati raccolti entrano in gioco per aiutare il motore di SQL Server e il suo ottimizzatore per: 
  • Ottimizzare l’esecuzione delle query 
  • Scelta degli Indici 
  • Stime più precise dei Costi delle query 
  • Scelta dei piani di Esecuzione più efficienti 
  • Gestione del Carico di Lavoro 

Si intuisce facilmente come l’aggiornamento delle statistiche sia basilare per permettere a SQL Server di prendere “le decisioni corrette” via via che i dati nei database aumentando ed evolvono. 

Esistono inoltre diversi articoli della community che sottolineano che, con l’aumentare delle prestazioni degli storage, oggigiorno statistiche aggiornate hanno spesso pari valore e oltre ad indici “maniacalmente” mantenuti. 
 

Microsoft Maintenance Plan – Update Statiscs

Analizziamo in prima istanza cosa è messo a disposizione da Microsoft. Per eseguire l’aggiornamento delle statistiche è possibile creare o aggiungere un task “Update Statistics” al workflow del nostro piano di manutenzione.               
 
Update Statistics
Oltre alla consueta selezione dei database sui cui vogliamo eseguire l’operazione abbiamo due possibili parametrizzazioni.

1) Tipologia di update: 
  • Tutte le statistiche.
  • Solo statistiche “di colonna”.
  • Solo statistiche “di indici”.

2)  Tipologia di scansione: 
  • Scansione completa.
  • Scansione analizzando una porzione di righe come valore assoluto o in percentuale.

Una volta impostati i parametri desiderati non resta che schedulare il piano attraverso SQL Server Agent. 

IndexOptimize – Update Statistics

Come già visto per gli indici, la stessa funzionalità viene estesa tramite l’esecuzione della stored procedure IndexOptimize opportunamente parametrizzata.              

Il comando con i parametri minimi per un’esecuzione è il seguente: 
EXEC IndexOptimize @Databases = 'ALL_DATABASES'
	,@UpdateStatistics = 'ALL'
Dove i parametri sono: 
  • Databases: indicando la lista dei datatabase che vanno inclusi.
  • UpdateStatistics: la tipologia di statistiche che vanno incluse.

Il comando così configurato è già schedulabile e va ad aggiornare tutte le statistiche di tutti i database presenti nell’istanza bersaglio. 

IndexOptimize – Update Statistics, elenco parametri

@Databases: permette di fornire la lista dei database da considerare nell’elaborazione ognuno separato da virgola “,”. 

Fanno parte della lista delle parole riservate: 
  • ALL_DATABASES: tutti i database 
  • SYSTEM_DATABASES: tutti i database di sistema 
  • USER_DATABASES: tutti i database definiti dagli utenti 
  • AVAILABILITY_GROUP_DATABASES: tutti i database presenti in Availability Group 

La lista può essere personalizzata ulteriormente con i l’utilizzo di: 
  • “ - “: per indicare di escludere un certo database 
  • “%”: come caratteri jolly per le ricerche con wildcard 

Esempio: 
EXEC [master].dbo.IndexOptimize @Databases = “SYSTEM_DATABASES
	,Advent %
	,- AdventureWorks2019”
Tutti i database di sistema, più tutti i database che iniziano per “Advent” escluso “AdventureWorks2019”.

@UpdateStatistics: indica quale tipologia di statistiche sono da includere, i valori possibili sono: 
  • ALL: tutte le statistiche 
  • INDEX: aggiorna solo statistiche di indice 
  • COLUMNS: aggiorna solo statistiche di colonna 
  • NULL: valore di default, in questo caso non viene eseguito nessun aggiornamento 

NB: è possibile eseguire una manutenzione indici e statistiche nella stessa sessione. In questo caso è obbligatorio specificare questo parametro con un valore diverso da NULL. 

@OnlyModifiedStatistics: il parametro permette di eseguire l’aggiornamento solo se è stata modificata almeno una riga dall’ultimo Update Statistics. 
I valori possibili sono: 
  • “Y”: aggiornamento solo se una riga modificata dall’ultima esecuzione 
  • “N”: aggiornamento in ogni caso 

Il parametro risulta particolarmente interessante ai fini delle prestazioni, nel caso di molto tabelle statiche si può risparmiare tempo saltandole e non disponibile nel Maintenance Plan di Microsoft! 

@StatisticsModificationLevel: per specificare la soglia in percentuale di righe modificate per aggiornare le statistiche. Generalmente su cambiamenti minimi potrebbe non essere vantaggioso andare ad aggiornare le statistiche. 

Va posto un accento su questa soglia che, come da documentazione, può venire scavalcata da un’altra condizione dinamica calcolata a partire dalla funzione SQRT. 

Lascio questo link [https://www.sqlskills.com/blogs/erin/updating-statistics-with-ola-hallengrens-script/] per un ottimo approfondimento, il concetto da sottolineare è che soprattutto per tabelle con grande numero righe la regola in percentuale può facilmente essere sovrascritta!  

NB: OnlyModifiedStatistics e StatisticsModificationLevel sono mutualmente esclusivi, è possibile specificare solamente uno dei due parametri! 

@StatisticsSample: permette di specificare la percentuale della tabella che viene raccolta per determinare l’aggiornamento, un valore del 100% equivale ad una FULL scan. 
Se un valore non viene impostato sarà SQL Server a determinare la quota per la raccolta del campione. 

@ StatisticsResample: permette di specificare se utilizzare il campione più recente disponibile (valore “Y”) o demandare a SQL Server (valore “N”). Il default è “N”. 

NB: StatisticsSample e StatisticsResample sono mutualmente esclusivi, è possibile specificare solamente uno dei due parametri! 

@LogToTable: parametro che permette di far sì che la procedura per ogni singolo indice accodi una riga nella tabella di log con i risultati del comando stesso. La tabella di log viene creata nel database master “dbo.CommandLog”. 

I valori possibili sono “Y” (Si) o “N” (No), default “N”. 

La tabella ha il valore aggiunto di poter essere usata come storico ed è facile costruire delle query di analisi tempi e trend. 
Index Optimize Table

@Execute: permette di specificare se la procedura deve essere eseguita a meno. I valori possibili sono “Y” (Si) o “N” (No), default “Y”.

Indici e Statistiche

Va sottolineato che Indici e Statistiche possono essere mantenuti con lo stesso comando nella stessa sessione: IndexOptimize in base ai parametri forniti eseguire entrambi i comandi uno di seguito all’altro per ogni tabella presa in esame. Nel caso si volesse perseguire questa strada va tenuto conto però dei parametri @Databases e @Indexes in quanto eventuali regole di esclusione su database e/o tabelle valgono per entrambi! Per una spiegazione più dettagliata di questi parametri è possibile consultare il precedente articolo.


Esempio d’uso e schedulazione

Il numero parametri disponibili volendo aggiornare solamente le statistiche è nettamente inferiore alla parte indici, un comando genericamente accettato può essere il seguente:

EXECUTE dbo.IndexOptimize @Databases = 'ALL_DATABASES'
	,@UpdateStatistics = 'ALL'
	,@OnlyModifiedStatistics = 'Y'
	,@LogToTable = 'Y'
Il comando andrà ad aggiornare le statistiche: 
  • Per tutti i db (@Databases = 'ALL_DATABASES') 
  • Tutte le tipologie (@UpdateStatistics = 'ALL') 
  • Solo dove sono state apportate modifiche alle righe 
  • Loggando il risultato sul DB 

Determinato il comando vediamo i passaggi minimi per schedulare il comando come avviene comunemente tramite l’agent di SQL Server: 

1) Dal menu di SQL Server Agent dal menu contestuale avviare la procedura guidata per la creazione di un nuovo job. 

SQL Server Agent

2) Aggiungere nome del Job. Impostare come owner un utente con privilegi adeguati solitamente può essere impostata un’utenza dedicata la lancio dei job con permessi elevati. 

Index New Job

3) Spostarsi nella tab Steps e aggiungere un nuovo passo con il tasto New … 

Index Optimize script

4) Compilare il nome del passo, selezionare come tipo “T-SQL” e impostare il comando. Impostare il database dove è stata installata IndexOptimize (tipicamente master). 

T-SQL

5) Nella tab “Schedules” aggiungere una nuova schedulazione con il tasto New, e impostare la ricorrenza desiderata. Nell’esempio si richiede una schedulazione giornaliera a partire dalle ore 05:00. 

Schedules

6) Nella tab “Notifications” è possibile aggiungere notifiche a completamento o fallimento del job. Nell’esempio è richiesto l’invio di una mail all’operatore DBA se il job fallisce. 

IndexOptimize Notifications
Cliccando OK il job viene creato! 

7) Dall’Activity Monitor è possibile verificare il job appena creato, eseguire un lancio manuale e verificare la prossima schedulazione pianificata da SQL Server Agent.
Activity Monitor

Conclusioni

Con questa seconda parte si conclude la panoramica su IndexOptimize. Come sempre l’intento è duplice: dare risalto a validi strumenti messi a disposizione alla community e dare qualche spunto per migliorare le attività che ogni DBA tipicamente deve pianificare. Grazie per la lettura. 

di Riccardo Trattenero, pubblicato l'11 luglio 2024.

Fonti: