Enjoy Your SQL Fillfactor & Indice su Identity - Francesco Quaratino
23 gennaio 2010

Fillfactor & Indice su Identity

E' risaputo che per la manutenzione degli indici di database di grandi dimensioni è opportuno lasciare perdere il Maintenance Plan messo a disposizione dal Management Studio, e intervenire, mediante Job T-SQL che realizzino una manutenzione più mirata, ovvero la Rebuild piuttosto che la Reorganize in funzione:

  • del livello di frammentazione di ciascun indice (le linee guida sono (a) Rebuild se > 30% e (b) Reorganize se >5% AND <30%)
  • e del numero di data pages di cui è composto l'indice (dalla mia esperienza sono da 1.000 in su le data pages significative per cui vale la pena impegnarsi nella manutenzione).

Il livello di frammentazione e il numero di pagine dati posso essere facilmente dedotte dalla sys.dm_db_index_physical_stats().

A tale proposito segnalo una soluzione alternativa al Maintenance Plan basato appunto su T-SQL disponibile su CodePlex: http://ola.hallengren.com/.

Ma proprio in questi Job, se dovessimo decidere programmaticamente il fillfactor da re-impostare in fase di Rebuild (..la Reorganize usa il fillfactor originale o quello re-impostato dall'ultima Rebuild..), quale potrebbe essere un elemento obbiettivo da applicare?

Una risposta potrebbe essere questa:

  • Se la prima colonna dell'indice è un Identity => (FILLFACTOR = 100)

Ecco come identificare questa condizione:

 

 

 

Daltronde è noto che una colonna di tipo identity è una ottima candidata al ruolo di indice clustered poichè il suo comportamento autoincrementante rende poco probabile la possibilità che avvengano page split.

Filed under: , , , ,

Comments

# mtammacco said:

Ciao Franco,

molto interessante il tuo post per quello che mi riguarda, non tanto per il tema della manutenzione degli indici quanto soprattutto per quanto affermato nell'ultima riga circa la creazione ottimale dell'indice clustered.

Sinceramente ho sempre saputo (ma posso sbagliarmi) che l'indice clustered è ottimale quando usato da query che ritornano un set di risultati molto grosso, e questo grazie all'ordinamento automatico delle chiavi dell'indice, come afferma anche la documentazione tecnica (msdn.microsoft.com/.../ms190639.aspx).

E' anche vero però che la stessa documentazione afferma che una colonna identity, insieme ad altri requisiti, è un valido candidato per un indice clustered.

Ma una query su una colonna identity restituisce sempre zero o 1 riga, in disaccordo con il punto di cui sopra.

Mi piacerebbe conoscere il tuo punto di vista e/o la tua esperienza su questo argomento.

Ciao!

lunedì 25 gennaio 2010 15.11
# sgainz said:

ciao maurizio,

la scelta dell'indice clustered è la classica scelta che ha diverse soluzioni a seconda della situazione. Le mie linee guida sono sostanzialmente queste:

(a) per tabelle anagrafiche (es. clienti, prodotti) in genere esiste un codice alfanumerico di piccole dimensioni (max 10 char)identificativo che poi è l'oggetto principale delle WHERE e inoltre PRIMARY KEY. Quella sarà il mio Clustered!

(b) per tabelle parametriche (es. codici iva, nazioni) il problema non sussiste proprio perchè sono così piccole che, preferisco definire una chiave primaria surrogata di tipo char di pochissimi byte (es char(3)). Quella sarà il mio Clustered!

(b)per tabelle soggette principalmente a insert/update (es. TesteVendite, DettagioVendite) ci tengo a scongiurare il page-split, in quel caso uso un identity clustered ;-)

bye

mercoledì 27 gennaio 2010 11.32