Auto Create Statistics e Auto Update Statistics

Quando SQL Server crea e aggiorna le statistiche ?
Se abbiamo abilitato le opzioni Auto Create Statistics e Auto Update statistics SQL server crea questi oggetti automaticamente e li mantiene aggiornati automaticamente quando necessario.
Ma a cosa servono le statistiche ?
Servono al Query Optimizer per identificare il piano d'esecuzione ottimale delle nostre query. Senza statistiche aggiornate l'optimizer potrebbe creare un piano d'esecuzione non ottimale e di conseguenza le nostre query potrebbero risultare estremamente lente, dando origine a seri problemi relativi alle performance delle applicazioni che richiedono a SQL i dati.

Le best practice consigliano nella maggioranza dei casi di abilitare le opzioni Auto Create ed Auto Update Statistics per delegare a SQL Server la gestione di questi oggetti fondamentali per ottenere performance adeguate.

Quando è abilitata l'opzione Auto Create Statistics ed effettuiamo una query di selezione (Select) su una tabella, SQL ricerca le statistiche sul/sui campi coinvolti nella Where. Se non vi sono statistiche relative a quel/quei campi SQL automaticamente le crea. A questo punto il query optimizer è in grado di selezionare un piano d'esecuzione ottimale.

Ma se effettuiamo delle operazioni di scrittura (Insert,Update,Delete) sulla tabella ?
In questo caso SQL Server (Salvo differenti impostazioni che vedremo tra poco) segue la seguente regola :

Tabelle con meno di 500 righe : Non fa nulla dato che la soglia di aggiornamento delle statistiche è di 500 operazioni di modifica
Tabella con 500 o più righe : La soglia di aggiornamento delle statistiche è di 500 + 20% delle righe contenute nella tabella

Faccio un esempio.

Tabella con 500 righe e su cui effettuiamo delle insert.
Se inseriamo 25 righe SQL non aggiornerà le statistiche presenti.
Se inseriamo 500 righe + 20% delle righe già presenti, cioè 600 righe, SQL aggiornerà le statistiche presenti per trovare un piano d'esecuzione ottimale.

--Creo una tabella di test
If (Object_id('tabStats') is not null)
 drop table tabStats
create table tabStats
(
 campo1 int,
 campo2 int
)

--La popolo con 500 Righe
;with 
Tab2Rows As( Select 1 riga union all Select 1 riga ),
Tab4Rows As( Select 1 riga from Tab2Rows A, Tab2Rows B ),
Tab16Rows As( Select 1 riga from Tab4Rows A, Tab4Rows B ),
Tab256Rows As( Select 1 riga from Tab16Rows A, Tab16Rows B ),
Tab65535Rows As( Select 1 riga from Tab256Rows A, Tab256Rows B ),
Totale As(Select ROW_NUMBER() over (order by riga) riga from Tab65535Rows)
Insert into tabStats
Select riga r1, riga r2 from Totale where riga <= 500 and riga <= 100000

--Creo un indice non cluster sulla colonna campo1
create index idx on tabStats (campo1)

Quando creiamo un indice SQL Server crea anche le sue statistiche.

Facciamo ora una query su Campo2, non indicizzato :

Select * from tabStats
 where campo2 = 25

Come potete vedere SQL server ha creato una statistica relativa a campo 2.

Ciò è avvenuto perchè l'opzione Auto Create Statistics è abilitata sul Database

Proviamo ora a monitorare con il SQL Server Profiler quando viene effettuato l'aggiornamento automatico delle statistiche. Per farlo seleziono l'evento "Auto Stats" ed inserisco 500 righe + 20% di quelle già presenti, in tutto inserisco 600 righe.

--Inserisco 600 Righe
;with 
Tab2Rows As( Select 1 riga union all Select 1 riga ),
Tab4Rows As( Select 1 riga from Tab2Rows A, Tab2Rows B ),
Tab16Rows As( Select 1 riga from Tab4Rows A, Tab4Rows B ),
Tab256Rows As( Select 1 riga from Tab16Rows A, Tab16Rows B ),
Tab65535Rows As( Select 1 riga from Tab256Rows A, Tab256Rows B ),
Totale As(Select ROW_NUMBER() over (order by riga) riga from Tab65535Rows)
Insert into tabStats
Select riga r1, riga r2 from Totale where riga > 500 and riga <= 1100

--Effettuo una Select
Select * from tabStats
 where campo1 = 25

Grazie al Profiler verifichiamo che SQL Server effettua l'aggiornamento delle statistiche

La nostra tabella ora contiene 1100 Righe. Quante ne devo inserire per far si che SQL riaggiorni le statistiche ? Ne devo inserire 500 + 20% di 1100, cioè 500 + 220. Proviamo:

;with 
Tab2Rows As( Select 1 riga union all Select 1 riga ),
Tab4Rows As( Select 1 riga from Tab2Rows A, Tab2Rows B ),
Tab16Rows As( Select 1 riga from Tab4Rows A, Tab4Rows B ),
Tab256Rows As( Select 1 riga from Tab16Rows A, Tab16Rows B ),
Tab65535Rows As( Select 1 riga from Tab256Rows A, Tab256Rows B ),
Totale As(Select ROW_NUMBER() over (order by riga) riga from Tab65535Rows)
Insert into tabStats
Select riga r1, riga r2 from Totale where riga > 1100 and riga <= 1820

Select * from tabStats
 where campo1 = 380

Sempre col profiler vediamo che è presente l'aggiornamento delle statistiche:

Proviamo ad inserire solo 500 righe

;with 
Tab2Rows As( Select 1 riga union all Select 1 riga ),
Tab4Rows As( Select 1 riga from Tab2Rows A, Tab2Rows B ),
Tab16Rows As( Select 1 riga from Tab4Rows A, Tab4Rows B ),
Tab256Rows As( Select 1 riga from Tab16Rows A, Tab16Rows B ),
Tab65535Rows As( Select 1 riga from Tab256Rows A, Tab256Rows B ),
Totale As(Select ROW_NUMBER() over (order by riga) riga from Tab65535Rows)
Insert into tabStats
Select riga r1, riga r2 from Totale where riga > 1820 and riga <= 2320

Select * from tabStats
 where campo1 = 2320

In questo caso nessuna operazione di aggiornamento viene effettuata.

La regola quindi è confermata, perchè le statistiche vengano aggiornate devo effettuare (500 + 20% delle righe già presenti in tabella) modifiche. Il nostro esempio si basa su una tabella microscopica per SQL Server.... Immaginiamo ora una tabella contenente circa 100.000.000 di righe e che ogni settimana subisce 1.000.000 di operazioni di Insert. Le select effettuate sulla nostra tabella riguardano i dati modificati nell'ultima settimana. Quanto tempo passerà prima che le nostre select possano beneficiare di statistiche aggiornate ? La soglia di aggiornamento delle statistiche è 500 + 20%, quindi 500 + 20.000.000. Passeranno quindi 20 settimane prima di beneficiare di statistiche aggiornate. :-( Al fine di evitare questi spiacevoli inconvenienti che possono inficiare le performance del Db siamo costretti ad implementare ad intervalli regolari dei job di aggiornamento delle statistiche, oppure ad utilizzare il flag –T2371. Questo flag va abilitato manualmente dal Configuration Manager e può essere utilizzato anche da SQL Server 2008 R2 SP1 ed abbassa la soglia dell'auto aggiornamento delle statistiche all'aumentare del numero di record contenuti nella tabella. La soglia viene abbassata in modo dinamico, all'aumentare delle righe contenute nella tabella la soglia si abbasa. Per maggiori info leggete il post di Juergen Thomas che ha effettuato dei test. Se non si vuole abilitare il flag -T 2371 basta pianificare dei piani di manutenzione che a cadenze prefissate e regolari implementino le operazioni di Reorganize/Rebuild degli indici che aggiornano le statistiche sottostanti. Per le colonne non indicizzate ma che hanno delle statistiche è necessario effettuare un Update manuale.

Spero vi sia utile.

Luca