SQL Server 2012: Column Store Index & Query Performance

Una delle novità di SQL Server 2012 è quella dei ColumnStore Index.
Come dice il nome, questo nuovo tipo di indici, invece di contenere righe, contiene i valori delle colonne. Ogni colonna compresa nell'indice ha una propria struttura di allocazione che consente al DB di implementare operazioni di raggruppamento in modo molto più efficiente rispetto agli indici tradizionali.

Lo scopo di un ColumnStore Index è quello di rendere estremamente rapide e "leggere" le query contenenti operazioni di summarizing e di grouping che, solitamente, effettuiamo in database di tipo OLAP.
Questo tipo di query su un OLAP, molto frequentemente, richiede la lettura se non di tutta la tabella, di buona parte di essa, ma gli indici tradizionali non sono utili a tale scopo.

Supponiamo di avere il seguente Database :

Create Database MyLittleDw
Go

Use MyLittleDw
Go

Alter database MyLittleDw Set Recovery Simple
Go

Create table DimProdotti
(
 idProdotto tinyint Primary Key Clustered,
 Descrizione varchar(25)
)
Go

Create table DimVenditori
(
 idVenditore tinyint Primary Key Clustered,
 Nome varchar(25)
)
Go

Create table DimTerritori
(
 idTerritorio tinyint Primary Key Clustered,
 Descrizione varchar(25)

)
Go

Create table FactVenditeClustered
(
 idVendita int identity(-2147483648,1)Primary Key Clustered,
 idVenditore tinyint,
 idProdotto tinyint,
 idTerritorio tinyint,
 Qta int,
 Importotot money,
)
Go

Create table FactVenditeColumnStore
(
 idVendita int identity(-2147483648,1)Primary Key Clustered,
 idVenditore tinyint,
 idProdotto tinyint,
 idTerritorio tinyint,
 Qta int,
 Importotot money,
)
Go

Popoliamo il Db con qualche dato.

--Prodotti
;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)
 ,Results as (Select Row_Number() Over(Order by riga)Riga From Cte65536)
insert into DimProdotti
Select Riga,'Prodotto ' + cast(Riga as varchar(25))  from Results
 Where Riga <= 255
Go

--Venditori
;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)
 ,Results as (Select Row_Number() Over(Order by riga)Riga From Cte65536)
insert into DimVenditori
Select Riga,'Venditore ' + cast(Riga as varchar(25))  from Results
 Where Riga <= 255
Go

--DimTerritori
Insert into DimTerritori values(1,'Italia'),(2,'Francia'),(3,'Germania'),(4,'Inghilterra'),(5,'Austria'),(6,'Russia'),(7,'Spagna'),(8,'Portogallo'),(9,'Slovenia'),(10,'Croazia'),(11,'Serbia'),(12,'Albania')
Go

Insert into FactVenditeClustered
Select C.idVenditore
 ,A.idProdotto
 ,B.idTerritorio
 , ABS(CAST(NEWID() AS binary(6)) %1000) + 1 Qta
 , ABS(CAST(NEWID() AS binary(6)) %1000) + 1 Importotot
from DimProdotti A, DimTerritori B, DimVenditori C
Go 20--Eseguo 20 volte l'inserimento in FactVenditeClustered, potrebbe essere lento

Insert into FactVenditeColumnStore
Select C.idVenditore
 ,A.idProdotto
 ,B.idTerritorio
 , ABS(CAST(NEWID() AS binary(6)) %1000) + 1 Qta
 , ABS(CAST(NEWID() AS binary(6)) %1000) + 1 Importotot
from DimProdotti A, DimTerritori B, DimVenditori C
Go 20--Eseguo 20 volte l'inserimento in FactVenditeColumnStore, potrebbe essere lento

Le tabelle FactVenditeClustered e FactVenditeColumnStore sono identiche e contengono i medesimi dati, cioè 15606000 record. Sono anche composte dallo stesso numero di datapage

Select object_name(Object_id),index_type_desc,index_level,page_count,record_count 
from sys.dm_db_index_physical_stats(db_id(),object_id('FactVenditeClustered'),null,null,'Detailed')

Select object_name(Object_id),index_type_desc,index_level,page_count,record_count 
from sys.dm_db_index_physical_stats(db_id(),object_id('FactVenditeColumnStore'),null,null,'Detailed')

Creiamo ora sulla tabella FactVenditeColumnStore anche un'indice di tipo ColumnStore. La sintassi è la stessa che si utilizza per la creazione di un indice stabdard, basta aggiungere la keyword "Columnstore"

create columnstore index idxColStore on FactVenditeColumnStore(importotot,qta,idVenditore)

Questa operazione può essere molto pesante sia per l'I/O che per la CPU

Vogliamo ora estrarre, per ogni venditore, la quantità totale dei prodotti venduti e il fatturato totale da entrambe le tabelle.

Set statistics io on
Set statistics Time on
Select idVenditore, Sum(Importotot) Importo,Sum(qta) Qta from FactVenditeClustered
group by idVenditore

Select idVenditore, Sum(Importotot) Importo,Sum(qta) Qta from FactVenditeColumnStore
group by idVenditore
Set statistics io Off
Set statistics Time Off

Entrambe le query restituiscono gli stessi risultati, ma li reperiscono in modi e con performance totalmente differenti.

Per l'elaborazione della tabella FactVenditeClustered l'optimizer ha scelto una Clustered Index Scan. Per l'elaborazione della tabella FactVenditeColumnStore l'optimizer ha scelto una Columnstore Index Scan.
La query sulla tabella FactVenditeClustered pesa per il 94% nel batch, mentre quella su FactVenditeColumnStore pesa per il rimanente 6%.
Ma perchè questa differenza ?
L'indice clustered, cioè la tabella FactVenditeClustered, contiene per ogni riga, i valori di tutte le colonne, il che aumenta a dismisura il numero di datapage necessarie all'elaborazione della query.
L'indice columnstore creato sulla tabella FactVenditeClustered contiene, in strutture separate, i dati delle sole colonne indicizzate. Ciò significa che i dati della colonna idProdotto sono fisicamente separati da quelli della colonna idVenditore e così via.... In questo modo il numero di datapage necessarie si riduce moltissimo.
Inoltre i dati vengono compressi, il che diminuisce ulteriormente le datapage necessarie all'elaborazione della query riducendo l'I/O.

Meno I/O effettuo migliori saranno le performance delle mie query.
Ovviamente anche i ColumnStoreIndex hanno risvolti negativi....
Non possono essere creati in modo clustered.
La tabella su cui viene creato il ColumnStore Index non può subire attività di scrittura. Per poter inserire, aggiornare o cancellare record è necessario Disabilitare il ColumnStoreIndex e ricostruirlo al termine dell'operazione. Ma come dicevo prima quest'ultima è un'operazione molto pesante.
I ColumnStoreIndex non supportano, ovviamente, la SEEK. Se abbiamo query che fanno uso della FORCESEEK quest'ultima opzione impedirà all'optimizer l'uso dell'indice ColumnStore, con ovvie ripercussioni sulle performance.
Ciao
Luca