Home > Scripts > Manutenzione indici colonnari: una ricostruzione accorta

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:

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

Chi è Massimiliano Buschi

Laureato in informatica, ha maturato esperienza in diversi settori professionali come Sistemista e DBA SQL Server. Attualmente è Senior Data Platform & BI Specialist presso SolidQ Italia, società specializzata in consulenza su Microsoft SQL Server. SolidQ Italia offre un mix di consulenza , mentoring e formazione per accelerare le implementazioni, ridurre i problemi e ampliare la conoscenza dei nostri clienti. Gli esperti SolidQ hanno pubblicato più di 30 libri ed effettuato innumerevoli presentazioni in occasione di importanti convegni tecnici di tutto il mondo. http://www.solidq.com/it/

Leggi Anche

SQL Azure DTU Calculator

Una delle domande più comuni quando si inizia ad utilizzare SQL Azure è legata alla …