[SQL Server] In-Memory OLTP : présentation des procédures stockées nativement compilées en mémoire

Ce billet présente les procédures stockées nativement compilées en mémoire, apparues à partir de SQL Server 2014.

Pour comprendre ce qu’est l’In-Memory OLTP (Hekaton) vous pouvez aller ici. Et si vous souhaitez en savoir plus sur les tables optimisées en mémoire et leur mode de durabilité, vous pouvez aller . Ainsi qu’ici, si vous souhaitez comprendre la différence entre un index scan et un index seek.

Présentation des procédures stockées nativement compilées en mémoire

    Notions de procédures stockées nativement compilées en mémoire

Les procédures stockées nativement compilées en mémoire sont comparables à des procédures stockées T-SQL classiques, à la différence qu’elles sont compilées – en mémoire – en code natif (code C, pour être exact). Là où une procédure stockée classique est compilée lors de sa première exécution, avec création et réutilisation d’un plan d’exécution en cache, une procédure stockée nativement compilée en mémoire est, elle, « parsée » et compilée à sa création, et exécutée comme un composant DDL externe par le moteur In-Memory OLTP.

De cette façon l’utilisation d’une procédure stockée nativement compilée en mémoire s’accompagne d’un gain de performances non-négligeable en termes de temps d’exécution. Le schéma ci-dessous (tiré du papier blanc de Microsoft sur le moteur Hekaton ; voir lien en fin de sous-section) :


Ce qu’il faut retenir, grosso-modo :

  • Le code DDL (et non DLL) de création de la procédure stockée et les métadonnées associées aux tables optimisées en mémoire et indexes utilisés sont récupérés par le moteur Hekaton (ou In-Memory OLTP) et fusionnés pour former une structure de données nouvelle appelée MAT (Mixed Abstract Tree).
  • La structure de données MAT est ensuite transformée en une autre appelée PIT (pour Pure Imperative Tree), beaucoup plus simple à manipuler.
  • La structure de données PIT est ensuite utilisée pour générer un fichier texte constitué d’un code source C, et sotcké dans les répertoires d’installation de l’instance SQL Server concernée.
  • Sitôt le code C généré, le compilateur Visual C/C++ est appelé pour produire les fichiers OBJ qui seront ensuite utilisés pour générer les fichiers DLL.
  • L’OS est ensuite chargé de porter les fichiers DLL fraîchement générés dans l’espace d’adressage de SQL Server, où ils seront exécutés nativement.

Pour en savoir plus, vous pouvez jeter un coup d’œil ici : http://research.microsoft.com/pubs/193594/Hekaton%20-%20Sigmod2013%20final.pdf
(et principalement dans la section 5.2).

Intérêt d’une procédure stockée nativement compilée en mémoire

Dans notre exemple de tests visible en fin de billet, la procédure stockée nativement compilée en mémoire offre de bien meilleures performances par rapport à celle classique (qui utilise, en fait, le mode interop pour accéder à la table optimisée en mémoire).

Cela ne signifie en aucun cas, cependant, qu’il vaille remplacer toutes les procédures stockées classiques par des procédures stockées nativement compilées en mémoire. Principalement du fait de très nombreuses contraintes et limitations (voir principales limitations, plus loin).

En règle générale, l’intérêt d’une procédure stockée nativement compilée en mémoire est son utilisation dans un contexte où une table subit fréquemment des blocages, voire deadlocks, indépendamment des considérations matérielles.

Dans tous les cas, il est de bon ton de procéder à une série de benchmarks avant de décider de l’utilisation ou non d’une procédure stockée nativement compilée en mémoire, surtout qu’il existe quelques subtilités à prendre en compte comme, par exemple, le cas d’une procédure stockée nativement compilées en mémoire et paramétrées, qui peuvent avoir des temps d’exécution anormalement longs en cas de nommage de ses paramètres lors de son appel (problème détectable avec hekaton_slow_parameter_passing).

Quelques informations subsidiaires sont consultables ici : http://msdn.microsoft.com/en-us/library/dn452287.aspx.

Principales limitations

Comme toute fonctionnalité qui se respecte, les procédures stockées optimisées en mémoire possèdent, bien sûr, quelques limitations, dont voici une liste non-exhaustive :

  • Utilisation limitée aux tables optimisées en mémoire.
  • Non-support des curseurs.
  • Non-support des vues.
  • Non-support des CTEs (Common Table Expressions).
  • Non-support des instructions d’insertion multi-lignes.
  • Non-support des niveaux d’isolation READ UNCOMMITTED et READ COMMITTED.
  • Non-support des tables temporaires (stockées dans tempDB), contrairement aux tables variables
  • Non-support d’un certain nombre d’options : RECOMPILE, COMPUTE,…
  • Non-support d’un certain nombre d’opérateurs : PIVOT, UNPIVOT, EXCEPT, INTERSECT, EXISTS, CONTAINS,
  • Non-support de RAISERROR (contrairement à TRY… CATCH et THROW), pour la gestion d’erreurs.

Il existe des limitations dans SQL Server 2014 qui ont été levées à partir de SQL Server 2016. En voici une liste non-exhaustive de fonctionnalités concernées :

  • LEFT et RIGHT OUTER JOIN.
  • SELECT DISTINCT.
  • OR, NOT.
  • Sous-requêtes.
  • Appels de procédures stockées imbriquées.
  • UNION, UNION ALL
  • ALTER PROCEDURE.

Pour finir, la DMV sys.sql_modules permet d’identifier les procédures stockées qui ont accès aux tables optimisées en mémoire (sachant que la colonne uses_native_compilation indique s’il d’une procédure stockée nativement compilée en mémoire (valeur 1) ou non (0)).

Utilisation technique d’une procédure stockée nativement compilée en mémoire

Création d’une procédure stockée nativement compilée en mémoire

Créons une base de données InMemoryDB avec fichier de données filestream, et une table durable optimisée en mémoire (avec index de hachage) Emp :

USE master
GO
IF EXISTS
(SELECT * FROM sys.databases
 WHERE name=N'InMemoryDB')
	DROP DATABASE InMemoryDB
GO

CREATE DATABASE InMemoryDB
ON PRIMARY
(NAME = InMemoryDB_Data,
 FILENAME=N'D:\SQL Server\MSSQL2014\SQL\SQL Data\InMemory_Data.mdf',
 SIZE = 250MB,
 FILEGROWTH = 100MB),
 FILEGROUP InMemoryDB_InMemoryDB CONTAINS MEMORY_OPTIMIZED_DATA
( NAME = InMemoryDB_InMemoryDB,
  FILENAME=N'D:\SQL Server\MSSQL2014\SQL\SQL Data\InMemoryDB_InMemoryDB.mdf')
 LOG ON
( NAME = InMemoryDB_Log,
  FILENAME=N'D:\SQL Server\MSSQL2014\SQL\SQL Logs\InMemory_Log.ldf',
  SIZE = 100MB,
  FILEGROWTH = 50MB);
GO

USE InMemoryDB
GO
/** Durable memory-optimized table **/
IF OBJECT_ID('Emp','U') IS NOT NULL
	DROP TABLE Emp
GO

CREATE TABLE Emp
(
	Emp_ID INT NOT NULL,
	Emp_Name VARCHAR(255) NOT NULL,
        Seniority INT NOT NULL,
  CONSTRAINT PK_Emp PRIMARY KEY NONCLUSTERED HASH (Emp_ID) WITH (BUCKET_COUNT = 5000)
)
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Créons une procédure stockée nativement compilée en mémoire ayant pour but l’insertion de 5 000 enregistrements dans la table optimisée en mémoire précédemment créée :

USE InMemoryDB
GO
IF OBJECT_ID('imusp_insertEmp','P') IS NOT NULL
DROP PROCEDURE imusp_insertEmp
GO
CREATE PROCEDURE imusp_insertEmp
   WITH NATIVE_COMPILATION,
   SCHEMABINDING,
   EXECUTE AS OWNER
   AS
     BEGIN ATOMIC WITH
     (
      TRANSACTION ISOLATION LEVEL=SNAPSHOT,
      LANGUAGE='french' 
     )
   DECLARE @i INT=0
   WHILE @i < 5000
   BEGIN
       SET @i+=1;
       INSERT INTO dbo.Emp(Emp_ID,Emp_Name,Seniority)
          VALUES (@i,CONVERT(VARCHAR(10),CAST(NEWID() AS VARBINARY(16)),2),CONVERT (INTEGER,40*RAND()+1))
   END
END
GO

Comme on peut le souligner, la création d’une procédure stockée nativement compilée en mémoire est très simple. Et ce que l’on doit retenir sont les éléments suivants qui constituent les options de création d’une procédure stockée nativement compilée en mémoire :

  • NATIVE_COMPILATION : sert de condition sine qua non pour la création d’une procédure stockée nativement compilée en mémoire.
  • SCHEMABINDING : permet de référencer les tables optimisées en mémoire utilisées par la procédure stockée en mémoire. De cette façon, lesdites tables ne pourront pas être supprimées tant que leur lien avec la procédure stockée ne sera pas rompu.
  • EXECUTE AS … : spécifie le contexte d’exécution de la procédure stockée nativement compilée en mémoire, le contexte d’exécution par défaut (EXECUTE AS CALLER)
    n’étant pas supporté, pour des raisons de performances (un tel contexte d’exécution nécessitant, au préalable, une série de vérifications de la sécurité au cours de l’exécution). Les contextes d’exécution acceptés sont : EXECUTE AS OWNER (propriétaire de la procédure stockée nativement compilée en mémoire), EXECUTE AS <utilisateur> (utilisateur ayant des droits d’exécution) et EXECUTE AS SELF (soi-même, si l’on possède les droits idoines).
  • BEGIN ATOMIC … END : constitue le corps de la procédure stockée nativement compilée en mémoire, sous la forme d’un seul bloc de code atomique. L’intérêt de cette option est de garantir l’atomicité (voir principes ACID) de la procédure stockée durant son exécution. Si une transaction est appelée en dehors du contexte d’une transaction active, une nouvelle transaction sera alors démarrée. Transaction qui ne sera validée qu’à la fin du bloc atomique.
  • TRANSACTION ISOLATION LEVEL : spécifie le niveau d’isolation à utiliser, pour la transaction atomique. Les valeurs supportées sont : SNAPSHOT, SERIALIZABLE et REPEATABLEREAD.
  • LANGUAGE : spécifie la langue à utiliser (voir sys.syslanguages), principalement pour les formats de dates et d’heures et les messages systèmes.

D’autres options non-primordiales existent : DATEFORMAT (pour le formatage de la date), DATEFIRST

Tests de performances

Afin de quantifier les gains de performances apportés par l’utilisation d’une procédure stockée nativement compilée en mémoire, créons une procédure stockée classique remplissant les mêmes objectifs que celle nativement compilée en mémoire créée précédemment :

USE InMemoryDB
GO
IF OBJECT_ID('usp_insertEmp','P') IS NOT NULL
   DROP PROCEDURE usp_insertEmp
GO
CREATE PROCEDURE usp_insertEmp
AS
BEGIN
   DECLARE @i INT=0
   WHILE @i &lt; 5000
   BEGIN
	SET @i+=1;
        INSERT INTO dbo.Emp(Emp_ID,Emp_Name,Seniority)
	    VALUES (@i,CONVERT(VARCHAR(10), CAST(NEWID() AS VARBINARY(16)),2),CONVERT(INTEGER,40*RAND()+1))
   END
END
GO

Le lancement de…

  • La procédure stockée nativement compilée en mémoire imusp_insertEmp
USE InMemoryDB
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXEC imusp_insertEmp
GO

… donne les temps suivants :

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 279 ms.
  • La procédure stockée T-SQL classique usp_insertEmp
USE InMemoryDB
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXEC usp_insertEmp
GO

… donne, quant à elle, les temps suivants :

SQL Server Execution Times:
CPU time = 1123 ms, elapsed time = 3951 ms.

Soient des temps plus lents que pour la version nativement compilée en mémoire. Y compris en cas de réexécution (pour réutilisation du cache sans passer par la case compilation) :

SQL Server Execution Times:
CPU time = 1108 ms, elapsed time = 3746 ms.

En toute transparence :

  • Les tests sont effectués sur une machine dotée de 4 CPUs et 8 Go de RAM.
  • Avant chaque lancement, la table Emp est bien sûr nettoyée (DELETE FROM Emp).

Génération des fichiers de compilation

A l’instar d’une table optimisée en mémoire, la création d’une procédure stockée nativement compilée en mémoire est accompagnée de la génération de fichiers DLL permettant leur exécution (voir plus haut).

La DMV sys.dm_os_loaded_modules permet d’identifier les fichiers DLL créés lors la définition des procédures stockées nativement compilées en mémoire (ainsi que des tables optimisées en mémoire) :

SELECT name,description
FROM sys.dm_os_loaded_modules
WHERE description='XTP Native DLL'
GO

Ce qui donnera les informations suivantes :

Comme dans le cas des tables optimisées en mémoire, les fichiers sont stockés au sein d’un sous-dossier d’un dossier appelé « xtp » (par défaut, placé dans le même répertoire que le fichier de données filestream). Ce sous-dossier porte le numéro d’identifiant de la base de données d’appartenance (et consultable via sys.databases, par exemple).

Les fichiers relatifs à la procédure stockée nativement compilée en mémoire sont préfixés d’un « xtp_p » (« xtp_t » faisant référence à une table optimisée en mémoire).

Pour avoir une idée des paramètres utilisés par le compilateur lors de l’invocation de la procédure stockée nativement compilée en mémoire, il suffit d’ouvrir le fichier de sortie OUT, dont voici un extrait :

Microsoft (R) Incremental Linker Version 11.00.60605.1
Copyright (C) Microsoft Corporation. All rights reserved.
/out:xtp_p_8_325576198.dll
/ltcg
/debug
/dll
/implib:xtp_p_8_325576198.lib
"/LIBPATH:C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn\Xtp\gen\lib"
"/LIBPATH:C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn\Xtp\VC\lib"
/noentry
/nodefaultlib
/incremental:no
/ltcg
/wx
/FUNCTIONPADMIN
xtp_p_8_325576198.obj
hkgenlib.lib
hkruntime.lib
hkengine.lib
hkgenexp.exp
hkcrt.lib
hkk32.lib
hkversion.obj
Generating code
Finished generating code

Ce fichier est très utile pour le troubleshooting, dans la mesure où il archive des messages (avertissements, erreurs, etc…) du compilateur C associés à la procédure stockée concernée.

Pour aller plus loin…

Ce billet a permis d’avoir un aperçu de ce qu’est une procédure stockée nativement compilée en mémoire.

Gardez un œil ici pour d’autres billets sur le même sujet, notamment du point de vue des performances.

Vous pouvez également jeter un coup d’œil ici, pour plus de lectures : http://msdn.microsoft.com/en-us/library/dn511014.aspx.

Publicités

Un commentaire sur “[SQL Server] In-Memory OLTP : présentation des procédures stockées nativement compilées en mémoire

  1. A reblogué ceci sur Mohamed A. Cherif – SQL Server, BI, Big Data…et a ajouté:

    Update avec prise en compte des dernières nouveautés de SQL Server 2016 autour des procédures stockées nativement compilées en mémoire.

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