SQL Server 2014 CTP1 : Natively compiled stored procedures

Un'altra novità introdotta da SQL Server 2014 CTP1 sono le Natively compiled stored procedures.
Queste SP altro non sono che codice T-SQL compilato nativamente che può essere eseguito dalla CPU senza alcun passaggio intermedio, risparmiando tempo e risorse.

Dato che non sono necessari passaggi intermedi per eseguire una Natively Compiled Stored Procedure è evidente che i miglioramenti in termini di performance possono essere moltissimi.

Facciamo un esempio

Creo un database contenente una In-Memory Table

Use Master
Go

--Creo il DB
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

USE Hekaton_Test
Go

--Creo la tabella
CREATE TABLE dbo.MemoryTable
(
 id int not null PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 65536)
 ,campo varchar(25) COLLATE Latin1_General_100_BIN2 not null
)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); 
GO

--La popolo
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 MemoryTable 
Select Riga, 'Valore ' + Cast(Riga as varchar(5)) From results
GO

Creo ora una Stored Procedure tradizionale e la Natively Stored Procedure

--Creo una Stored Procedure tradizionale
CREATE PROCEDURE Dbo.SP_TSQL_MEMORY_TAB
 @id int
AS
 declare @c varchar(25)

 Select @c = A.campo from dbo.Memorytable A Where A.id = @id

 Update dbo.Memorytable Set campo = campo + 'AAAA' Where id = @id
 
 Delete from dbo.Memorytable where id = @id
 Insert into dbo.Memorytable values(@id, @c)
GO

--Creo una Natively Compiled Stored Procedure
CREATE PROCEDURE Dbo.SP_NATIVE_COMPILATION_MEMORY_TAB
 @id int not null
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english'
)
 declare @c varchar(25)

 Select @c = A.campo from dbo.Memorytable A Where A.id = @id

 Update dbo.Memorytable Set campo = campo + 'AAAA' Where id = @id
 
 Delete from dbo.Memorytable where id = @id
 Insert into dbo.Memorytable values(@id, @c)

END
GO

Escludendo alcune Keyword particolari la sintassi non è molto dissimile da quella necessaria alla creazione di SP tradizionali.

WITH NATIVE_COMPILATION : Identifica che stiamo creando una SP Natively compiled
SCHEMABINDING : E' obbligatorio che la SP sia creata con lo SCHEMABINDING
EXECUTE AS : E' obbligatorio e supporta i valori OWNER, SELF o user. CALLER non è supportato.
BEGIN ATOMIC : Identifica il corpo della SP che sarà eseguito come un unico blocco atomico.

Il blocco Atomic dovrà inoltre avere specificato anche
TRANSACTION ISOLATION LEVEL :E' obbligatorio e specifica il Transaction Isolation Level utilizzato dal body della Sp
LANGUAGE : E' obbligatorio e specifica il Language che sarà utilizzato dalla sp.

Eseguo ora la stored procedure tradizionale per ogni riga contenuta nella in-memory table e mi faccio restituire il tempo di esecuzione in millisecondi.

DECLARE @t datetime;
SET @t = GETDATE();

declare @i int = 1
While @i <= 65536
begin

 
 --Exec sp_executesql N'Dbo.SP_NATIVE_COMPILATION_MEMORY_TAB @i',N'@i int',@i=@i
 Exec sp_executesql N'Dbo.SP_TSQL_MEMORY_TAB @i',N'@i int',@i=@i
 Set @i+=1

end
select DATEDIFF( MILLISECOND , @t , current_timestamp );

Ed ecco il risultato

Ora eseguo la Natively Compiled Stored Procedure e mi faccio restituire il tempo di esecuzione in millisecondi

DECLARE @t datetime;
SET @t = GETDATE();

declare @i int = 1
While @i <= 65536
begin

 
 Exec sp_executesql N'Dbo.SP_NATIVE_COMPILATION_MEMORY_TAB @i',N'@i int',@i=@i
 --Exec sp_executesql N'Dbo.SP_TSQL_MEMORY_TAB @i',N'@i int',@i=@i
 Set @i+=1

end
select DATEDIFF( MILLISECOND , @t , current_timestamp );

Ed ecco il risultato

Passare da circa 50 secondi a meno di 20 solo compilando nativamente la stored procedure è un ottimo guadagno.
Peccato non sia così facile trasformare le Stored procedure tradizionali in Natively Compiled.
In primo luogo queste nuove SP possono accedere solo a In-Memory Table(non supportano l'accesso a Disk-Based Table) e non tutti i comandi T-SQL sono supportati.
Per esempio non è possibile farne l'alter.
Qui trovate la lista dei cosa è supportato e cosa non lo è.

Ma effettivamente, dove viene salvato il codice ?
Se guardiamo l'INFORMATION_SCHEMA.ROUTINES vediamo che entrambe le SP sono salvate come metadati nelle tabelle di sistema.

Select SPECIFIC_CATALOG
  ,SPECIFIC_SCHEMA 
  ,SPECIFIC_NAME
  ,ROUTINE_TYPE
  ,ROUTINE_DEFINITION  
from Information_Schema.ROUTINES

Se però la Natively Compiled sp fosse disponibile solo nei metadati i vantaggi in performance che abbiamo appena visto non li avremmo ottenuti.
SQL Server, all'atto della creazione della Sp, oltre ad inserirla nei metadati, crea un modulo compilato nativamente e lo carica in memoria rendendolo istantaneamente disponbile, non solo, salva anche un file .c sul disco.
Questo file contiene la definizione del modulo nativo che sql ha generato e che ha caricato in memoria.

E' possibile reperire la lista di tutti i moduli creati dagli utenti e i loro file .c con una nuova DMV, la sys.dm_os_loaded_modules.

SELECT * FROM sys.dm_os_loaded_modules
WHERE DESCRIPTION = 'XTP Native DLL'

E questo è un estratto del contenuto del file .c

struct hkt_485576768
{
 long hkc_1;
 unsigned short hkvdo[2];
};
struct hkis_48557676800002
{
 long hkc_1;
};
struct hkif_48557676800002
{
 long hkc_1;
};
unsigned short GetSerializeSize_485576768(
 struct HkRow const* hkRow)
{
 struct hkt_485576768 const* row = ((struct hkt_485576768 const*)hkRow);
 return ((row->hkvdo)[1]);
}
HRESULT Serialize_485576768(
 struct HkRow const* hkRow,
 unsigned char* buffer,
 unsigned short bufferSize,
 unsigned short* copySize)
{
 return (RowSerialize(hkRow, (GetSerializeSize_485576768(hkRow)), buffer, bufferSize, copySize));
}
HRESULT Deserialize_485576768(
 struct HkTransaction* tx,
 struct HkTable* table,
 unsigned char const* data,
 unsigned short datasize,
 struct HkRow** hkrow)
{
 return (RowDeserialize(tx, table, data, datasize, sizeof(struct hkt_485576768), (sizeof(struct hkt_485576768) + 25), hkrow));
}
unsigned short GetSerializeRecKeySize_485576768(
 struct HkRow const* hkRow)
{
 struct hkt_485576768 const* row = ((struct hkt_485576768 const*)hkRow);
 unsigned short size = sizeof(struct hkif_48557676800002);
 return size;
}

Questo file serve solo per troubleshooting dato che il modulo è già in memoria.

Attenzione : Dato che queste SP non verranno più Ricompilate se non a seguito di Restart del server, è indispensabile crearle quando la/le tabelle che andranno ad utilizzare conterranno un campione rappresentativo dei dati e le statistiche dovranno essere aggiornate.
Qui trovate maggiori dettagli.

I File risultanti dalla compilazione di queste SP non sono compresi nel backup del database perché non sono necessari.
Al momento del restore le Natively compiled Stored Procedure saranno ricompilate, dopotutto nei metadati è presente la loro definizione.

Ciao

Luca