Sql Server 2014 CTP1 : Un vero In-Memory Oltp Database management System

Oggi è uscito SQL Server 2014 CTP1 !!!
Era necessario visto che SQL 2012 è poco più di anno che è uscito in RTM ? Cioè... ancora non abbiamo apprezzato tutte le sue potenzialità che Microsoft ha pensato bene di rilasciare, seppur in CTP il suo successore.....
Che cosa avranno mai sviluppato per giustificare un così repentino rilascio ?!?!?
Una cosa su tutte.... Hekaton, o nel suo nome definitivo In-Memory OLTP !!!!

In-Memory OLTP è tecnologia tutta nuova che anche grazie alla diffusione dei sistemi HW a 64 bit, consente di sfruttare in modo impressionante la RAM della macchina, migliorando di 10x,o addirittura 100x le performance dei nostri processi sfruttando quelle che sono definite "In-Memory optimized Tables" e le "Natively compiled stored procedures".

Le In-Memory Optimized Table non sono altro che delle tabelle che risiedono interamente in memoria con i loro indici.

Le Natively compiled stored procedures sono degli oggetti compilati in "Machine Code" che di conseguenza potrebbero ulteriormente migliorare le perfomance nel process dei dati . Questi oggetti li affronterò in un post successivo.

Ovviamente ciò non significa che questi nuovi oggetti siano la panacea per tutti i mali dei nostri DB....
In-Memory OLTP è una tecnologia che va sfruttata con raziocinio per poter ottenere il top delle performance senza "sprechi" inutili di RAM.

Ovviamente non sono utilizzabili su piattaforme 32 bit....

Fino a SQL Server 2012 l'utilizzo della RAM da parte dell'engine era esclusivamente fatto per la gestione dei dati in fase di processo sotto forma di datapage. Mano a mano che essi "invecchiavano" venivano scaricati senza troppi riguardi dalla RAM.... Se poi una query ne avesse nuovamente fatto esplicita richiesta essi sarebbero stati riletti dal disco, con ovvie ripercussioni sulle performance dovute alle problematiche di accesso allo storage, di sincronizzazione delle transazioni, di allocazione della RAM, di utilizzo della CPU etc... Il tutto si traduce in performance non sempre all'altezza soprattutto in sistemi non particolarmente dotati in quanto a memoria ma che subiscono moltissime transazioni contemporanee.

Con SQL Server 2012 sono stati inoltre introdotti i ColumnStore Index e xVelocity che hanno migliorato le performance in modo incredibile semplicemente sfruttando in modo più intensivo la RAM.
Column Store Index ed xVelocity hanno mostrato in minima parte quale vantaggio comporta un migliore utilizzo della RAM, seppur con qualche limitazione(Per esempio una tabella dotata di columnstore index non poteva più subire scritture).

Ok bello.... Ma come funzionano le In-Memory Optimized Table ?
Partiamo dal fatto che NON funzionano come le tabelle tradizionali che possiamo d'ora in poi definire Disk-based tables. A differenza di queste ultime, le In-Memory Tables non hanno la classica allocazione basata su datapages così come gli indici non hanno la classica struttura B-Tree. Sono strutturalmente concepite in modo totalmente differente. Queste tabelle mantengono costantemente la loro struttura ed i dati in RAM. La loro creazione ha una sintassi leggermente differente rispetto a quella delle tabelle tradizionali.

Il vantaggio prestazionale è dato dal fatto che l'engine non ha la necessità di leggere le datapages contenenti i dati dal disco e di posizionarle nella buffer cache perché i dati sono SEMPRE presenti in ram. Ogni operazione su queste tabelle è implementata in RAM. Queste tabelle sono poi corredate di un'insieme di file di Checkpoint che sì risiedono sul disco, ma sono utilizzati solo ed esclusivamente nelle fasi di recovery. Questi file risiedono in un filestream filegroup
N.B. Non è necessario abilitare la feature del Filestream dal Configuration Manager per poter sfruttare le In-Memory Tables.

Il Database deve essere quindi creato specificando un apposito Filegroup repository dei file di checkpoint.

Use Master
Go

CREATE DATABASE Hekaton_Test     
ON PRIMARY(NAME = [PRIMARY],     
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_DiskBasedTable.mdf')
   ,FILEGROUP [Hekaton_InMemory_FG] 
    CONTAINS MEMORY_OPTIMIZED_DATA    
     (NAME = [Hekaton_InMemory_Dir],FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\In_Memory_Table')
LOG ON (name = [Hekaton_log], Filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton_Test\Hekaton_Test_log.ldf')  
Go

L'opzione CONTAINS MEMORY_OPTIMIZED_DATA identifica il/i filegroup che conterranno i file di checkpoint.

Prima di creare la tabella In-Memory è bene sapere che NON tutti i tipi dato sono supportati.
I tipi di dati supportati sono :
bit
inyint
smallint
int
bigint
money
smallmoney
float
real
datetime
smalldatetime
datetime2
date
time
char(n)
varchar(n)
nchar(n)
nvarchar(n)
sysname
binary(n)
varbinary(n)
Uniqueidentifier

Non sono supportati i tipi di dati LOB, Xml, CLR e il (max).

Una riga non deve assolutamente superare la lunghezza di 8060 byte. Non sono previste ROW_OVERFLOW Allocation Unit.
La tabella deve avere almeno un indice e non può averne più di 8.
Tutte le tabelle, escludendo quelle create con l'opzione SCHEMA_ONLY devono avere una Primary Key definita.
Tutti gli indici necessari devono essere creati al momento della create della tabella.
In questo momento sono supportate le sole Windows Collate BIN2.

Non è possibile definire Foreign Key, Check, DML Trigger, Unique Index oltre alla Pk, identity....
Non posso modificarne lo schema e non posso aggiungere indici dopo la creazione della tabella.

Se per ottenere delle performance eccezionali siamo disposti a rinunciare a quanto detto allora possiamo procedere, altrimenti meglio optare per le tabelle tradizionali.

Creo la tabella In-Memory Table

Use Hekaton_Test  
Go
Create table MemoryTable
(
 id int not null PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 5000000)
 ,Nome varchar(25) COLLATE Latin1_General_100_BIN2 not null
 ,Cognome varchar(50) COLLATE Latin1_General_100_BIN2 not null
 ,Indirizzo varchar(50) COLLATE Latin1_General_100_BIN2
 INDEX inMemoryIdx NONCLUSTERED HASH (Cognome,Nome) WITH(BUCKET_COUNT = 5000000) 
)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); 
GO

Come vedete la sintassi differisce di poco dal comando DDL tradizionale.
Ho il solito Create table seguito dalla definizione dei campi.
NONCLUSTERED HASH WITH(BUCKET_COUNT = 1024) identifica il tipo di indice che vado a creare.

La CTP1 supporta solo indici HASH (Nel Blog di Kalen Delaney trovate un'ottima descrizione di come funzionano)
Detto per sommicapi... Un Hash index altro non è che un array di puntatori che consentono di accedere alle righe della tabella grazie ad una funzione di hash sulla chiave dell'indice stesso, ogni elemento dell'array è chiamato Hash Bucket. E' indispensabile selezionare un BUCKET_COUNT adeguato alla cardinalità attesa dei dati contenuti nella tabella. Come spiegato bene da Kalen Delaney un valore sottostimato causa la crescita della row chain che diminuisce le performance, un valore troppo elevato spreca memoria.

Se non si conosce la cardinalità è bene ripiegare su Range index che saranno disponibili dalla CTP2.

L'opzione MEMORY_OPTIMIZED = ON identifica che la tabella è di tipo in-memory
L'opzione DURABILITY può assumere 2 valori :
1) SCHEMA_AND_DATA = Significa che la definizione della tabella ed i suoi dati dovranno SEMPRE risiedere in memoria ed in caso di restart del server essi dovranno essere ricostruiti partendo dai file di checkpoint e dal T-Log
2) SCHEMA_ONLY = Le modifiche ai dati non vengono loggate, ciò significa che a seguito del shutdown dell'istanza i dati eventualmente contenuti saranno irrimediabilmente persi. Il restart dell'istanza comporta la ricreazione della tabella totalmente priva di dati. Questa modalità la trovo utile per aumentare ulteriormente le performance durante le fasi di ETL che necessitano di tabelle di staging.

Per fare un raffronto di performance creo la medesima tabella anche in modo tradizionale

Use Hekaton_Test  
Go
Create Table DiskTable
(
 id int Primary Key Clustered
 ,Nome varchar(25)
 ,Cognome varchar(50)
 ,Indirizzo varchar(50)
 INDEX OnDiskIdx NONCLUSTERED (Cognome,Nome)
)
ON [PRIMARY]
GO

Popolo Entrambe le tabelle con 5000000 di record

Use Hekaton_Test  
Go

--Tabella Disk Based

Set statistics Io On
Set statistics Time On
;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)
 ,CteMany as (Select A.Riga From Cte65536 A, Cte65536 B)
 ,Results as (Select ROW_NUMBER()Over(Order by Riga) Riga From CteMany)
Insert into DiskTable(id, nome, Cognome, indirizzo)
Select Riga
  , 'Nome ' + Cast(Riga as char(8)) 
  , 'Cognome ' + Cast(Riga as char(8))     
  , 'Indirizzo ' + Cast(Riga as char(8)) 
from Results where Riga <= 5000000
Set statistics Io Off
Set statistics Time Off

--Tabella In-Memory

Set statistics Io on
Set statistics Time on

;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)
 ,CteMany as (Select A.Riga From Cte65536 A, Cte65536 B)
 ,Results as (Select ROW_NUMBER()Over(Order by Riga) Riga From CteMany)
Insert into MemoryTable(id, nome, Cognome, indirizzo)
Select Riga
  , 'Nome ' + Cast(Riga as char(8)) 
  , 'Cognome ' + Cast(Riga as char(8))     
  , 'Indirizzo ' + Cast(Riga as char(8)) 
from Results where Riga <= 5000000
Set statistics Io Off
Set statistics Time Off
GO

Ecco qui i risultati

Come potete vedere per inserire 5000000 Record la tabella tradizionale ha impiegato 2 minuti e mi restituisce il numero di datapages coinvolte dall'operazione. Tengo a precisare che sto utilizzando un disco SSD per la virtual Machine, quindi le performance di I/O sono ottime anche per la disk-based Table

L'inserimento nella tabella In-Memory non restituisce nessun valore relativo all'I/O dato che NON utilizza l'allocazione tradizionale a datapages e come tempo d'esecuzione restituisce 12 secondi......

Non c'è che dire.... Un guadagno notevolissimo in termini di performance !!! Dopotutto la Ram è sempre molto più veloce del disco....
Anche di un disco SSD ;-)

Direi che la tecnologia In-Memory sia davvero il nuovo punto di svolta per ottenere performance sempre migliori.

Ciao

Luca