Avete presente le procedure di manutenzione degli indici di Hola Hallengren?
Se no, vi consiglio di guardarle perché sono molto utili. Ecco il link: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Se le conoscete, magari vi è capitato di notare che, nelle versioni più recenti di SQL Server, la manutenzione degli indici colonnari richiede uno sforzo ulteriore.
A questo proposito vi segnalo un articolo di dal blog Microsoft:
https://blogs.msdn.microsoft.com/sqlcat/2015/07/08/sql-2014-clustered-columnstore-index-rebuild-and-maintenance-considerations/
Quello che occorre ricordare è che la ricostruzione dell’indice è più pesante rispetto ad un B-Albero e si compone di una fase iniziale di costruzione del dizionario che non è parallelizzabile. Inoltre la dimensione del dizionario e il grado di parallelismo durante la creazione/manutenzione dell’indice influiscono sulla forma finale dell’indice colonnare.
Nell’articolo si propone uno script per ricostruire gli indici (partizionati se necessario) sulla base dei seguenti criteri (in OR):
- La percentuale di righe cancellate è superiore ad una soglia fissata (10% nello script)
- Il dizionario non è pieno (ovvero è più piccolo di 16MB) e il numero medio di righe per gruppo di righe (RowGroup) è inferiore alla soglia fissata (500k righe) e ci sono più di 500k righe totali.
La prima condizione deriva dal fatto che una cancellazione negli indici colonnari è solo logica e diventa effettiva (quindi lo spazio si libera) solo dopo una ricostruzione dell’indice.
La seconda condizione ci dice di ricostruire l’indice se il numero medio di righe per gruppo è inferiore a 500k righe, ovvero metà del numero di righe massimo per gruppo per cui il gruppo è considerato chiuso e quindi compresso in background dal thread preposto (‘Tuple-Mover’).
Entrambe le condizioni sono applicate alle singole partizioni, se presente un partizionamento della tabella.
Successivamente, lo script produce i comandi di ALTER INDEX con un MAXDOP calcolato secondo la seguente logica:
- Se il numero totale di righe è inferiore a un milione (quindi un singolo rowgroup non pieno) allora MAXDOP = 1.
- Se il numero totale di ‘milionate’ di righe è inferiore al MAXDOP possibile (in base alle impostazioni Resource Governor) allora il MAXDOP è pari al numero di ‘milionate’ effettive (quindi un processore per ogni RowGroup).
- Se abbiamo più ‘milionate’ dei processori disponibili allora MAXDOP = 0 (li usiamo tutti).
Bene, lo script ci può essere utile nella manutenzione dei nostri indici colonnari, ma gli script di Ola Hallengren hanno molte utili funzionalità come il log dei comandi, i filtri su db o indici specifici, i limiti di tempo, etc…
Quello che ho voluto fare è prendere la stored procedure di Ola ‘dbo.IndexOptimize’ e farne una versione che comprende la metodologia di manutenzione descritta sopra.
Eccola. Si chiama “dbo.ColumnStoreOptimize”.
Ho aggiunto alcuni nuovi parametri:
/* new parameters*/
@DeletedRowsPercent Decimal(5,2) = 10,
@RGQuality int = 500000,
@PercentageRGQualityPassed smallint = 20,
@IndexesTypeFilter varchar(6) = ‘Column’, — Row, NULL
/* end new parameters */
Il filtro per tipo di indice ci permette di lavorare solo su indici colonnari o per righe o entrambi in caso di NULL.
Tutte le modifiche che ho apportato alla procedura originale sono evidenziate dalla coppia di commenti:
/* news */
/* end news */
Ho dovuto applicare le modifiche solo alle versioni di SQL Server >= 2012 (tramite apposito filtro) perché manca una tabella di sistema usata per reperire informazioni sui gruppi di righe (la sys.column_store_row_groups).
Spero vi possa essere utile. Provare sempre in ambiente di Test prima di usare in Produzione.
*con milionate di righe intendo numero totale di righe diviso un milione