SQL Server 2014 : Updatable Clustered Columnstore Index

Tra le svariate novità che introdurrà Sql Server 2014 vi sono anche i Clustered ColumnStored Index.
Sono uno strumento molto potente qualora si abbia la necessita di applicare aggregazioni e raggruppamenti su tabelle dei fatti molto grosse.
I Columnstore Index introdotti da SQL Server 2012 (ne ho parlato in un vecchio post) impedivano di aggiornare la tabella su cui erano stati creati mentre i Clustered ColumnStored Index non hanno più questa limitazione.
Per utilizzare i nuovi Clustered Columnstore Index è necessario che l'intera tabella sia convertita da Rowstore (Heap o Clustered che sia) a Columnstore.
Per eseguire questa operazione dobbiamo rimuovere tutti gli indici dalla tabella ed eseguire il comando :
Create Clustered Columnstore Index IdxName On MyTable.
Di fatto la tabella stessa è il Clustered ColumnStore Index.

La modalità di salvataggio dei dati in ColumnStore mode prevede che ogni colonna della tabella sia salvata in modalità compressa in una struttura fisicamente separata dalle altre colonne della tabella stessa.
Questa struttura fisica da un enorme vantaggio a quelle operazioni di aggregazione e raggruppamento su tabelle dei fatti di grosse dimensioni; separando fisicamente le colonne tra loro evito letture inutili che aumenterebbero la quantità di I/O necessario per reperire tutti i dati. Inoltre i dati stessi di ogni colonna sono salvati in un formato compresso. Ciò significa che anche in termini di allocazione di spazio su disco ottengo notevoli benefici(anche in termini di performance visto che riduco ulteriormente l'I/O necessario).

Vediamo un pò di terminologia:

Rowgroup

1. Gruppo di righe salvate in modalità compressa e in formato ColumnStore.

Column Segment

1. Gruppo di valori di una sola colonna salvati in modo compresso e contenuti nella medesima struttura.
2. Ogni colonna è salvata in strutture separate dalle altre e tutte sono suddivise in segmenti.
3. Ogni RowGroup contiene un Column Segment di tutte le colonne nella tabella.

Rowstore

1. I dati sono salvati su righe e colonne "contenute fisicamemente nella medesima struttura" 
2. Area temporanea dove vengono inserite le nuove righe in attesa d'essere aggiunte al columnstore.

DeltaStore

1. Il DeltaStore è ciò che rende i Clustered Columnstore Index editabili....
2. E' composto da Rowstore contenenti le righe che nel tempo andrò ad inserire nella tabella.

Vediamo come funzionano in scrittura i Clustered ColumStore Index

Esecuzione di Insert

Quando eseguiamo un inserimento in una tabella ColumnStore la nuova riga viene salvata nel RowStore aperto nel DeltaStore. Se non esistono RowStore ne viene creato ed aperto uno. Solo quando il Rowstore raggiunge la soglia di 2^20 (1.048.576 record) verrà chiuso. A questo punto un background process chiamato "Tuple-Mover" cerca nel DeltaStore i RowStore chiusi, li comprime e li sposta nel ColumnStore.

 
Esecuzione di Delete

Se invece dobbiamo eliminare delle righe dalla tabella abbiamo 2 comportamenti distinti

1. Se la riga è nel DeltaStore verrà fisicamente eliminata.
2. Se la riga è nel ColumnStore verrà marcata come "cancellata" ma fisicamente rimarrà al suo posto. Sarà eliminata fisicamente solo durante la Rebuild della tabella.

Vediamo degli esempi.

1. INSERT

Creo un DB contenente una tabella con Clustered ColumnStore Index popolata con 100.000 Record

Use Master
Go

Create Database TestClusteredColumnStore
Go

Use TestClusteredColumnStore
Go

Create table TabClusteredColumnStore 
( 
 id int identity,
 valore1 int, 
 valore2 int, 
 valore3 int, 
 valore4 int, 
 valore5 int, 
) 
Go

/***************************************************************************************
ATTENZIONE : questa operazione trasforma la tabella da HEAP(Rowstore) a ColumnStore
***************************************************************************************/
create clustered columnstore index idxClustColStore on TabClusteredColumnStore
Go

--Popolo la tabella con 100000 Record
;With Cte as (Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B )
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B )
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B )
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B )
 ,cteMolti as (Select A.Riga From Cte65536 A, Cte65536 B )
 ,Results as (Select Row_number() Over(Order By Riga) Riga From cteMolti)
Insert into TabClusteredColumnStore
Select Riga,Riga,Riga,Riga,Riga from Results Where Riga <= 100000
Go

Eseguiamo questa vista di sistema :

--Ho un solo RowGroup aperto nel deltastore
select object_name(object_id)TabName
  ,* 
from sys.column_store_row_groups
Go

La sys.column_store_row_groups è una vista di sistema che mi restituisce informazioni su ogni Clustered ColumnStore Index presente nel database.

Il risultato è il seguente

Come vedete la tabella TabClusteredColumnStore ha un DeltaStore contenente un RowStore che a sua volta contiene 100.000 Record. Lo stato del RowStore è OPEN, quindi in grado di ricevere ulteriori record.
Provo adesso ad inserire abbastanza righe per raggiungere la soglia massima.
La soglia è 1048576 , quindi dato che ne ho già inserite 100000 devo aggiungerne altre 948576 + 1 in modo da causare la chiusura del RowStore.

--Arrivo alla soglia del Rowstore
;With Cte as (Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B )
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B )
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B )
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B )
 ,cteMolti as (Select A.Riga From Cte65536 A, Cte65536 B )
 ,Results as (Select Row_number() Over(Order By Riga) Riga Riga From cteMolti)
Insert into TabClusteredColumnStore
Select Riga,Riga,Riga,Riga,Riga from Results Where Riga <= 948577
Go

Rieseguo la select di prima su sys.column_store_row_groups.

Come vedete il RowStore è stato chiuso e contiene 1048576 righe. Inoltre è stato creato un RowStore nuovo contenente una sola riga, inserita dall'istruzione precedente.
Il Background process Tuple-Mover alla prima esecuzione troverà il RowStore in stato CLOSED e lo inserirà in modalità compressa nel ColumnStore index.

Rieseguo dopo qualche minuto la medesima query su sys.column_store_row_groups.











Lo stato ora è COMPRESSED, il che significa che le righe sono state inserite nel Clustered ColumnStore Index dal Tuple-Mover.

2. DELETE

Cosa accade quando eseguo la delete di una riga già nel Clustered ColumnStore Index ?

Delete From TabClusteredColumnStore Where id = 1
Go

Come detto prima, se la riga è nel ColumnStore verrà marcata come eliminabile ma non verrà eliminata fisicamente dalla struttura.

L'eliminazione fisica delle righe dal ColumnStore verrà eseguita quando sarà effettuato il rebuild del Clustered ColumnStore Index.

Cosa accade quando eseguo una Delete di una riga in RowStore in stato OPEN nel DeltaStore ?
(La riga con id 1048577 è nel RowStore OPEN)

Delete From TabClusteredColumnStore Where id = 1048577
Go

Come detto prima se la riga è in un RowStore in stato OPEN nel DeltaStore allora verrà eliminata definitivamente.

Ed effettivamente viene eliminata, la colonna Total_Rows restituita dalla vista mi dice che ora le righe sono 0 (Nella esecuzione precedente mi dava 1 Riga per il RowStore in stato OPEN).


3. Data Compression

I dati di ColumnStore Index e di Clustered ColumnStore Index vengono salvati in modalità compressa. Per farci un'idea del risparmio in termini di spazio e di I/O creo una tabelle esattamente uguale a TabClusteredColumnStore ma con la struttura standard a cui siamo abituati da anni e la popolo con lo stesso numero di righe

Create table TabStandardRowStore 
( 
 id int identity primary key clustered, 
 valore1 int, 
 valore2 int, 
 valore3 int, 
 valore4 int, 
 valore5 int, 
) 

;With Cte as (Select 1 Riga union All Select 1)
 ,Cte4 as (Select A.Riga From Cte A, Cte B )
 ,Cte16 as (Select A.Riga From Cte4 A, Cte4 B )
 ,Cte256 as (Select A.Riga From Cte16 A, Cte16 B )
 ,Cte65536 as (Select A.Riga From Cte256 A, Cte256 B )
 ,cteMolti as (Select A.Riga From Cte65536 A, Cte65536 B )
 ,Results as (Select Row_number() Over(Order By Riga) Riga From cteMolti)
Insert into TabStandardRowStore
Select Riga,Riga,Riga,Riga,Riga from Results Where Riga <= 1048576
Go

Vediamo quanto spazio abbiamo risparmiato

Exec sp_spaceused 'TabClusteredColumnStore'
Exec sp_spaceused 'TabStandardRowStore'

 

 

 

Il risparmio direi che è notevole, la tabella TabClusteredColumnStore alloca meno della metà dello spazio della sua gemella con la struttura tradizionale.

In SQL Server 2014 inoltre è stato aggiunto un nuovo tipo di compressione ancora più efficiente.
Lo applico alla tabella TabClusteredColumnStore

Alter table TabClusteredColumnStore rebuild with(Data_compression = ColumnStore_Archive)

Riverifico lo spazio allocato

che è ulteriormente calato.....

Per maggiori info sulla compressione dei dati nei ColumnStore Index vi consiglio di leggere il blog di Niko Nuegebauer.

N.B. La rebuild ha ricostruito il Clustered ColumnStore Index e ha anche eliminato la riga eliminata precedentemente.

Ciao

Luca