SQL Server 2014–Procédure stockée en code natif

Après avoir installé et créé une première base de donnée pouvant utiliser des tables memory-optimized, il est grand temps de regarder ce qui contribue au gain de performance annoncé dans Hekaton : les procédures stockées en code natif.

Tout d’abord, créons une nouvelle base de test :

CREATE DATABASE HekatonNativeProc 
ON 

PRIMARY (
	NAME = [HekatonNativeProc_data], 
	FILENAME = 'g:\Data\HekatonNativeProc_data.mdf', size=500MB),
 
FILEGROUP [HekatonNativeProc_fs_fg] CONTAINS MEMORY_OPTIMIZED_DATA (
	NAME = [HekatonNativeProc_hk_fs_dir], 
	FILENAME = 'g:\Data\HekatonNativeProc_fs_dir')
 
LOG ON (
	name = [HekatonNativeProc_log], 
	Filename='g:\Data\HekatonNativeProc_log.ldf', size=4GB)

COLLATE Latin1_General_100_BIN2;
GO

J’ai attaché à cette machine virtuelle un disque SSD, représenté ici par le volume G:\. Ce n’est pas un VHDX stocké sur le disque SSD mais bien le disque qui est présenté directement.

Ensuite, créons une table toute simple :

USE HekatonNativeProc
GO

CREATE TABLE [dbo].[HekatonTable] 
( 
	[TestTableID] INT not null ,
	[Filler] char(6000) null,
	[LastModified] datetime null,
	CONSTRAINT [PK_HekatonTable] PRIMARY KEY NONCLUSTERED 
	      HASH ([TestTableID]) WITH(BUCKET_COUNT = 2000000)
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

Il est impératif de bien se souvenir que Hekaton fait partie intégrante de SQL Server. Il ne s’agit pas d’un produit séparé. Ainsi, le client aura le choix d’utiliser ou non cette fonctionnalité et pourra même choisir quelle(s) table(s) sont susceptibles de migrer dans l’environnement Hekaton. Seule les tables les plus critiques seront placées en mémoire alors que d’autres nécessitant des performances moindres pourront se satisfaire d’un stockage disque (disque à plateau ou disque flash).

Hekaton est conçu pour répondre à des attentes sur la performance et la concurrence d’accès. Ce billet va traiter de la performance. Un article à venir traitera de la concurrence, sachant que celle ci ne repose pas sur les tables partitionnées, la réponse actuelle aux problèmes de PageLatch.

Le code de SQL Server est optimisé, vous vous en doutez ! Mais comment faire pour que nos requêtes soient exécutées encore plus rapidement ? Il est possible de jouer sur plusieurs axes : améliorer l’extension graduelle, l’évolutivité (traduction approximatives de scalability), améliorer le nombre de cycles par instructions (CPI) ou diminuer el nombre d’instructions. Après avoir étudié en détail ces possibilité, il est apparu qu’améliorer els 2 premiers points ne pouvait laisser espérer qu’un gain x3 ou x4.

Il restait donc la solution de la diminution du nombre d’instructions. Et ce de manière drastique car pour un facteur x10 en gain de performance, il faut 90% d’instructions en moins et pour aller 100 fois plus vite, il faudrait réduire de 99% le nombre d’instructions. les bases du défi sont posées.

Afin de répondre à ce postulat, il n’était pas possible de se reposer sur le stockage de données actuellement utilisé (le buffer pool, les pages, les extensions, …). Il fallait donc repenser le modèle de stockage des données. D’où le modèle de table Hekaton, la table est entièrement stockée en mémoire (pour rappel : sur les 30 dernières années, le prix de la mémoire a chuté d’un facteur 10 tous les 5 ans).

Pour diminuer le nombre d’instructions, il fallait aussi revoir 3 points :

  • les index qui n’ont d’existence qu’en mémoire pour une table hekaton
  • éliminer les latches et les locks pour améliorer également l’évolutivité
  • compiler les requêtes en code natif, car le code interprété, même pour une simple requête SQL faisant quelques accès aux données peut rapidement représenter des centaines d’instructions.

En compilant le code des procédures stockées en code natif, on atteint donc un des objectifs : réduire le nombre d’instructions a exécuter. Les types de données seront connus lors de la compilation (schema binding) et toutes les décisions qu’il est possible de prendre lors de la compilation le seront, laissant place à un code très optimisé pour la procédure.

Une vue globale de Hekaton permet de distinguer 3 composants principaux :

  • Hekaton storage engine  : gère les tables et index, gère les transactions, les checkpoints, les mécanismes de stockage …
  • Hekaton compiler : permet d’extraire une représentation sous forme d’arbre de la procédure, en tenant compte des requêtes, des méta données des tables et index pour compiler la procédure et produire une DLL en code natif qui sera chargée dans SQL Server
  • Hekaton runtime system : permet l’intégration avec SQL Server

Un schéma résume parfaitement ces quelque lignes :

image

A présent, il est temps de créer la procédure stockée en code natif :

CREATE PROCEDURE [dbo].[Native_InsertRowsHekatonTable]
(
	@rowcount INT,
	@must_delete BIT
)
WITH 
	EXECUTE AS OWNER, 
	NATIVE_COMPILATION, 
	SCHEMABINDING
AS
BEGIN ATOMIC WITH 
	(
		TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
		LANGUAGE = N'us_english'
	)
	
	
	DECLARE @i INT
	SET @i = 0

	DECLARE @d datetime = getdate()

	WHILE (@i < @rowcount)
	BEGIN
		INSERT INTO [dbo].[HekatonTable] 
		VALUES (@i,'Filler',@d)
		SET @i = @i +1
	END

	IF @must_delete = 1
		DELETE FROM [dbo].[HekatonTable]
	
	RETURN @i

END
GO

Attardons nous quelques instances sur le code de cette procédure afin de détailler les nouveautés apportées par Hekaton au niveau de la syntaxe.

Les procédures stockées compilées en code natif sont spécialement conçues pour accéder de manière très rapide aux tables Hekaton. Il n’est pas possible au sein d’une procédure stockée compilée en code natif d’accéder à une table “classique”. Par contre, l’inverse est possible : comme nous le verrons plus tard, le mode Interop permet depuis du code natif d’accéder à une table memory-optimized.

Tout d’abord, la clause WITH introduit l’option native_compilation. Le message est clair … Les procédures stockées natives doivent également être accompagnées des options de schema binding et du contexte d’exécution. En effet, les procédures stockées compilées en code natif ne supportent pas l’option EXECUTE AS CALLER. C’est pourquoi, il parait plus simple d’utiliser un EXECUTE AS OWNER, plutôt que de devoir spécifier un user dans la déclaration de la procédure.

Lors de la création de la procédure stockée, une phase d’optimisation de la requête ou des requêtes est opérée. Du code natif est généré et compilé. Un DLL (oui, oui) est ainsi produite, pour être ensuite chargée dans SQL Server. Cela nous rappelle le processus des procédures et autres fonctions en SQLCLR.

image

La DMV sys.procedures ne laisse rien transparaitre de la compilation native, contrairement à la DMV sys.sql_modules qui possède une nouvelle colonne uses_native_compilation. Quant a la DMV  sys.tables, les champs is_memory_optimized, durability et durability_desc font leur apparition.

Ensuite, un nouveau block d’instruction apparait : BEGIN ATOMIC WITH. Cela permet au compilateur de comprendre comment fonctionne le traitement des erreurs et des transactions lors de la compilation. En effet, le ATOMIC signifie que l’exécution de la procédure stockée est considérée comme atomique, comme une transaction, donc validée ou annulée dans son intégralité. Cela signifie aussi qu’il n’est pas possible d’utiliser els commandes BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN dans une procédure stockée compilée en code natif.

Si vous souhaitez gérer les erreurs dans votre procédure et ainsi provoquer un ROLLBACK, il suffit d’utiliser la commande THROW.

Notez également, que vous pouvez quand même combiner la gestion des transactions avec l’appel de procédures stockées en code natif. Depuis du code TSQL, vous pouvez commencer une nouvelle transaction, faire appel à une procédure stockée native et ensuite faire un COMMIT /ROLLBACK dans votre code TSQL. En cas de ROLLBACK, alors, les modifications opérées par la code natif seront bien évidement annulées. Il existe donc une certain flexibilité.

Pour pouvoir bénéficier de la compilation en code natif, il est indispensable de spécifier

  • le niveau d’isolation des transaction : seulement les niveau d’isolation SNAPSHOT, REPEATABLE READ et SERIALIZABLE  sont supportés
  • le langage qui permet de définir le format des dates et les messages systèmes

Il est également possible de manière optionnelle d’ajouter les paramètres DATEFIRST et DATEFORMAT. S’ils ne sont pas explicitement écrits, alors les valeurs par défaut du langage sont utilisées.

Et les perfs avec tout ça ? Et bien, elles sont au rendez vous …

PRINT '[Native_InsertRowsHekatonTable]'
DECLARE @delete_in_proc BIT = 1
IF @delete_in_proc = 0
	DELETE FROM HekatonTable
DECLARE @Date DATETIME2(7) = getdate()
DECLARE @rows int
EXEC @rows = [dbo].[Native_InsertRowsHekatonTable] 50000,@delete_in_proc
DECLARE @duration INT = DATEDIFF(MILLISECOND, @Date, getdate())
SELECT  @duration [Duration(ms)],@rows [Rows], (@rows*1.00000)/(@duration*0.001)[Avg Rows/s]
GO

Une fois l’exécution terminée, le résultat est pas si mal que ça :

[Native_InsertRowsHekatonTable]
Duration(ms) Rows        Avg Rows/s
———— ———– —————————————
4090         50000       12224.93887530562347188264

Mais il faudrait un point de comparaison avec une table disk-based pour en avoir le cœur net :

CREATE TABLE [dbo].[DiskBasedTable] 
( 
	[TestTableID] INT not null PRIMARY KEY CLUSTERED ,
	[Filler] char(6000) null,
	[LastModified] datetime null
) 
GO

CREATE PROCEDURE [dbo].[TSQL_InsertRowsDiskBasedTable]
(
	@rowcount INT,
	@must_delete BIT
)
AS
BEGIN 	
	
	DECLARE @i INT
	SET @i = 0
	DECLARE @d datetime = getdate()

	WHILE (@i < @rowcount)
	BEGIN
		INSERT INTO [dbo].[DiskBasedTable] 
		VALUES (@i,'Filler',@d)
		SET @i = @i +1
	END


	IF @must_delete = 1
		DELETE FROM [dbo].[DiskBasedTable]
	RETURN @i

END
GO

PRINT '[TSQL_InsertRowsDiskBasedTable]'
DECLARE @delete_in_proc BIT = 1
IF @delete_in_proc = 0
	DELETE FROM DiskBasedTable
DECLARE @Date DATETIME2(7) = getdate()
DECLARE @rows int
EXEC @rows = [dbo].[TSQL_InsertRowsDiskBasedTable] 50000, @delete_in_proc
DECLARE @duration INT = DATEDIFF(MILLISECOND, @Date, getdate())
SELECT  @duration [Duration(ms)],@rows [Rows], (@rows*1.00000)/(@duration*0.001)[Avg Rows/s]
GO

And the winner is : la table Hekaton … Sans surprise.

Avec un PK sur un index cluster :

[TSQL_InsertRowsDiskBasedTable]
Duration(ms) Rows        Avg Rows/s
———— ———– —————————————
28433        50000       1758.52002954313649632469

Avec un PK sur un index non cluster :

[TSQL_InsertRowsDiskBasedTable]
Duration(ms) Rows        Avg Rows/s
———— ———– —————————————
49170        50000       1016.88021151108399430547

On est donc passé de 1700 inserts /sec à 12 200 inserts /sec.
Coooooool.

Comme je vous le disais plus haut il est possible d’accéder à des tables Hekaton depuis du code T-SQL. D’un point de vue vocabulaire, on pourrait parler de code T-SQL interprété. Bien que permettant d’élargir le scope des possibilités offertes par les tables memory-optimized, des ordres tels que TRUNCATE TABLE ou MERGE ne sont pas supportés, idem pour els curseurs ou les requêtes multi bases.

L’avantage de ce mode Interop serait d’abord de faciliter les migrations, car le code client ne change pas et seule le stockage de la table va être modifié, ce qui permet de ne pas avoir à modifier l’applicatif ni à le redéployer. Ensuite, cela permet donc d’éliminer toute contention de type Latch, mais permet surtout de pouvoir au sein d’une même requête faire la jointure entre une table disk-based et une table memory-optimized.

Alors oui, ce sera moins performant que du code natif, mais on gagne en flexibilité.

CREATE PROCEDURE [dbo].[Interop_InsertRowsHekatonTable]
(
	@rowcount INT,
	@must_delete BIT
)
AS
BEGIN 	
	
	DECLARE @i INT
	SET @i =0
	DECLARE @d datetime = getdate()

	WHILE (@i <  @rowcount)
	BEGIN
		INSERT INTO [dbo].[HekatonTable] 
		VALUES (@i,'Filler',@d)
		SET @i = @i +1
	END
	
	IF @must_delete = 1
		DELETE FROM [dbo].[HekatonTable]
	
	RETURN @i

END
GO


PRINT '[Interop_InsertRowsHekatonTable]'
DECLARE @delete_in_proc BIT = 1
IF @delete_in_proc = 0
	DELETE FROM HekatonTable
DECLARE @Date DATETIME2(7) = getdate()
DECLARE @rows int
EXEC @rows = [dbo].[Interop_InsertRowsHekatonTable] 50000,@delete_in_proc
DECLARE @duration INT = DATEDIFF(MILLISECOND, @Date, getdate())
SELECT  @duration [Duration(ms)],@rows [Rows], (@rows*1.00000)/(@duration*0.001)[Avg Rows/s]
GO

L’exécution est clairement plus lente que son alter égo en code natif. Les performance sont similaires voir inférieures à une table disk-based.

[Interop_InsertRowsHekatonTable]
Duration(ms) Rows        Avg Rows/s
———— ———– —————————————
32760        50000       1526.25152625152625152625

On aurait du théoriquement observer un gain de performance. Ce n’est pas le cas. Ce test mérite d’être relancé une fois la version finale disponible.

Une des options des tables memory-optimized, la durabilité, permet de spécifier si les données de la table doit être physiquement stockée sur disque, ou bien si son schéma, sa définition est durable, mais dont les données sont éphémère, un peu comme une table temporaire, sauf qu’elle ne perd que les données mais pas la structure en cas de défaillance et de redémarrage du service.

On va donc créer une nouvelle table, non durable, afin de tester ses performances.

CREATE TABLE [dbo].[NonDurableHekatonTable] 
( 
	[TestTableID] INT not null,
	[Filler] char(6000) null,
	[LastModified] datetime null,
	CONSTRAINT [PK_NonDurableHekatonTable] PRIMARY KEY NONCLUSTERED 
	      HASH ([TestTableID]) WITH(BUCKET_COUNT = 2000000)
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO



CREATE PROCEDURE [dbo].[Native_InsertRowsNonDurableHekatonTable]
(
	@rowcount INT,
	@must_delete BIT
)
WITH 
	EXECUTE AS OWNER, 
	NATIVE_COMPILATION, 
	SCHEMABINDING
AS
BEGIN ATOMIC WITH 
	(
		TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
		LANGUAGE = N'us_english'
	)
	
	
	DECLARE @i INT
	SET @i = 0

	DECLARE @d datetime = getdate()

	WHILE (@i <  @rowcount)
	BEGIN
		INSERT INTO [dbo].[NonDurableHekatonTable] 
		VALUES (@i,'Filler',@d)
		SET @i = @i +1
	END

	IF @must_delete = 1
		DELETE FROM [dbo].[NonDurableHekatonTable]
	
	RETURN @i

END
GO

PRINT '[Native_InsertRowsNonDurableHekatonTable]'
DECLARE @delete_in_proc BIT = 1
IF @delete_in_proc = 0
	DELETE FROM HekatonTable
DECLARE @Date DATETIME2(7) = getdate()
DECLARE @rows int
EXEC @rows = [dbo].[Native_InsertRowsNonDurableHekatonTable] 50000,@delete_in_proc
DECLARE @duration INT = DATEDIFF(MILLISECOND, @Date, getdate())
SELECT  @duration [Duration(ms)],@rows [Rows], (@rows*1.00000)/(@duration*0.001)[Avg Rows/s]
GO

[Native_InsertRowsNonDurableHekatonTable]
Duration(ms) Rows        Avg Rows/s
———— ———– —————————————
357          50000       140056.02240896358543417366

Et si on relance une nouvelle exécution :

[Native_InsertRowsNonDurableHekatonTable]
Duration(ms) Rows        Avg Rows/s
———— ———– —————————————
210          50000       238095.23809523809523809523

Ah oui, ca envoie du bois. Plus de 238 000 inserts/seconde. Vraiment intéressant.

Cela mérite une comparaison avec une table temporaire. Notez qu’il n’est pas possible d’utiliser els tables temporaires dans une procédure stockée native. Néanmoins, si vous souhaitez utiliser une table temporaire, il faudra alors vous tourner vers les variables de type table, qui elles sont autorisées.


CREATE PROCEDURE [dbo].[TSQL_InsertRowsTempTable]
(
	@rowcount INT,
	@must_delete BIT
)
AS
BEGIN 	
	

	CREATE TABLE #TempTable
	( 
		[TestTableID] INT not null PRIMARY KEY CLUSTERED ,
		[Filler] char(6000) null,
		[LastModified] datetime null
	) 

	DECLARE @i INT
	SET @i = 0

	WHILE (@i < @rowcount)
	BEGIN
		INSERT INTO #TempTable 
		VALUES (@i,'Filler',getdate())
		SET @i = @i +1
	END

	IF @must_delete = 1
		DELETE FROM #TempTable
	
	RETURN @i

END
GO



PRINT '[TSQL_InsertRowsTempTable]'
DECLARE @delete_in_proc BIT = 1
DECLARE @Date DATETIME2(7) = getdate()
DECLARE @rows int
EXEC @rows = [dbo].[TSQL_InsertRowsTempTable] 50000,@delete_in_proc
DECLARE @duration INT = DATEDIFF(MILLISECOND, @Date, getdate())
SELECT  @duration [Duration(ms)],@rows [Rows], (@rows*1.00000)/(@duration*0.001)[Avg Rows/s]
GO

[TSQL_InsertRowsTempTable]
Duration(ms) Rows        Avg Rows/s
———— ———– —————————————
20250        50000       2469.13580246913580246913

[TSQL_InsertRowsTempTable]
Duration(ms) Rows        Avg Rows/s
———— ———– —————————————
7517         50000       6651.58972994545696421444

Le second test correspond à un insert sur la table temporaire dont la PK est assurée par un index non cluster.

L’utilisation d’une table temporaire est donc bien plus lent qu’une table non durable memory-optimized. De quoi repenser bien déferrement bien des traitements actuels qui sont lents a cause de la TempDB,bien que stockée sur disque SSD ou FusionIO.

La raison encore une fois tient dans le mode de stockages des informations, ou plus exactement dans l’écriture sur disque des données. Mon précédent article présentait les fichiers DELTA et DATA et stipulait que les IO se basaient sur la technologie du FileStreaming. En effet, le checkpointing est continu et les IO streamés, donc séquentiels, ce qui est beaucoup plus rapide que les accès aléatoires des tables disk-based (même si SQL Server s’efforce d’écrire les pages apparentant aux mêmes extensions d’un bloc).

Le fait de privilégier des accès disque séquentiels se traduit aussi par une consommation de ressources moindre niveau CPU.

Lors des tests réalisés plus haut, si l’on regarde les compteurs disque au travers de l’outil perfmon, on constate effectivement que les inserts dans la table Hekaton engendrent une utilisation plus efficace du disque.

imageimage

On mesure ainsi toute l’importance d’avoir un sous système disque performant en plus d’une quantité de mémoire permettant de stocker les tables Hekaton.

En conclusion, on peut dire que oui, Hekaton apporte un gain réel en terme de performance malgré les contraintes sous-jacentes.

Un prochain article abordera les problématiques de concurrence d’accès, de conflits Read/Write ou Write/Write dus au verrouillage optimiste utilisé par Hekaton.

Stay tuned !

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, est tagué , , . Ajoutez ce permalien à vos favoris.

4 commentaires pour SQL Server 2014–Procédure stockée en code natif

  1. Laurent GRENIER dit :

    Very good article ! Voila une nouveauté qui évitera bien des schémas de bd tortueux pour optimiser les perfs des proc appelées en frontal sur des applications a fort trafic. Penses-tu que ce genre de mécanismes puissent remplacer les mécanismes de cache classiques côté applicatif dans certains cas ? Dans tous les cas ?

    • Christophe dit :

      Honnêtement, je ne sais pas. Il est fort probable que celui puisse solutionner certaines situations. Dans d’autres cas il sera préférable d’avoir un bon middle-tier possédant des fonctionnalités de cache …
      Réponse favorite du consultant : ça dépend !

  2. thesqlgrrrl dit :

    Tres très chouette travail!!! Je viens de tester tous tes scripts sans un seul faux pas donc je ne peux qu’applaudir ce beau et rigoureux boulot!!!! Merci🙂

  3. Ping : Retour sur le PASS Summit | SQL Server dans le détail

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s