Salve a tutti,
L’argomento di oggi ha, per chi lavora con SQL Server da molti anni, un significato particolare perché va ad impattare uno degli aspetti più granitici ed immutabili nel tempo, sai che è così da (n) versioni e (m) anni, con tutti i problemi del caso, ed un bel giorno Ti svegli ed alcune di queste certezze non ci sono più ! :)
L’oggetto della mia meraviglia, e del presente post, è un nuovo meccanismo per la gestione “dinamica” delle statistiche, introdotto con la SP1 di SQL Server 2008 R2 e che sarà anche nella prossima versione di SQL Server (nome in codice “Denali”): quello che ora cambia, se abilitate il trace flag –T2371, è la magica soglia (di modifiche ad una colonna/e di una tabella) oltre la quale una certa statistica viene aggiornata automaticamente da SQL Server.
Fino a ieri, la regoletta che potete trovare in varie documentazioni su Internet era la seguente, per l’aggiornamento dinamico di una statistica (“recompilation threshold”):
Con varie sfaccettature, questa soglia statica (cioè il numero “20%”) non è mai cambiata dalla sua introduzione in SQL 7.0, parliamo quindi di circa 13 anni fa !
Il problema di questa percentuale fissa è che, su tabelle molto grandi come numerosità di righe, tale soglia è troppo alta e si rischia che SQL Server non scateni mai l’aggiornamento automatico delle statistiche, con conseguenti problemi di accuratezza dei “query plan” generati.
Per farVi un esempio concreto, immaginate la seguente situazione:
Riuscite a vedere il problema? Con la soglia fissa al 20%, le statistiche di questa tabella saranno aggiornate non prima di 20gg (1M di record x 20gg = 20M record =circa il 20% delle righe della tabella) !!!
Con tabelle di queste dimensioni, spesso partizionate, la soglia classica di aggiornamento delle statistiche non basta mai, per cui è altrettanto spesso necessario inserire un “job” del “SQL Agent” che periodicamente (non certo ogni 20gg !) forzi l’aggiornamento delle statistiche.
Con questo nuovo meccanismo, la soglia di auto-aggiornamento delle statistiche non è più fissa al 20% ma varia, nella sua percentuale, dinamicamente in base al numero di record di una tabella: purtroppo al momento l’informazione esatta su come varia questa percentuale non è di carattere pubblico :), ma facendo qualche test numerico è facilmente intuibile ;), il concetto importante da considerare è che più record ha una tabella e più si abbassa la percentuale oltre la quale scatta l’aggiornamento delle statistiche, ecco alcuni esempi:
Per chi non avesse ancora indovinato la funzione matematica che governa la soglia dinamica, rendo ancora più facile la cosa con il seguente grafico tratto da un post del collega Juergen Thomas (grazie !), il nostro maggiore esperto di SAP:
NOTA: Non Vi aspettate che questo meccanismo entri in azione su tabelle piccole, come potete vedere dal grafico la soglia è identica fino a (circa) 25000 righe, poi il comportamento cambia.
OK, sembra tutto fantastico, ma qualcuno dovrebbe chiedersi: pechè devo abilitare un “trace flag” per avere questo meccanismo ed invece non è un comportamento di default di SQL Server?
La cosa ha molto senso ed è una consuetudine del gruppo di sviluppo di SQL Server: ogni modifica al codice e/o nuova funzionalità che cambi il comportamento dell’engine di SQL deve essere attivata esplicitamente (e consciamente), ed i “trace flags” adempiono proprio a questo scopo, oltre ovviamente a molti altri. Vorrei poi puntualizzare che non è detto che questo meccanismo porti beneficio a tutti i clienti: è vero che le statistiche saranno aggiornate più frequentemente, ma ciò vuol dire anche un maggiore numero di “ricompilazioni” delle stored procedure e delle query adhoc, quindi (tra le altre cose) un maggiore utilizzo della CPU.
Come si abilita in maniera “permanente” questo meccanismo ? Và inserito il trace flag –T2371 negli “startup parameters” dell’istanza SQL utilizzando il tool “SQL Configuration Manager”, tab “Advanced” nelle proprietà del servizio SQL Server, riga “startup parameters”: attenzione, dovete inserire –T2371 in coda a quello che già c’è, pre-pendendo un punto e virgola (;). Dopo l’inserimento del trace flag di SQL tramite SQL Configuration Manager, e relativo reboot, verificate che nella parte iniziale dell’ERRRORLOG compaia una cosa del genere:
Registry startup parameters:
-d C:\SQL2008R2\MSSQL10_50.SQL2008R2\MSSQL\DATA\master.mdf
-e C:\SQL2008R2\MSSQL10_50.SQL2008R2\MSSQL\Log\ERRORLOG
-l C:\SQL2008R2\MSSQL10_50.SQL2008R2\MSSQL\DATA\mastlog.ldf
-T 2371
IMPORTANTE: Sebbene dobbiate inserire -T2371 senza spazio tra –T e 2371 negli “startup parameters”, nell’ERRORLOG lo spazio invece compare, non Vi preoccupate è normale e deve essere così altrimenti avete commesso un errore nella configurazione;
Anche per oggi è tutto, mi raccomando: testate, testate ed ancora testate…..
--Igor Pagliai—