Manutenzione indici colonnari: una ricostruzione accorta

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):

1. La percentuale di righe cancellate è superiore ad una soglia fissata (10% nello script)
2. 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