[SQL Server] In-Memory OLTP : les différents types d’indexes d’une table optimisée en mémoire

Ce billet présente les différents types d’indexes d’une table optimisée en mémoire, apparus à 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.

In-Memory OLTP et indexation

Toute table optimisée en mémoire supporte principalement 2 types d’indexes non-cluster In-Memory OLTP :

  • Les indexes de hachage (ou hash indexes), qui offrent de meilleurs performances pour les requêtes avec des prédicats d’égalité. Ces indexes possèdent un paramètre particulier lors de leur création : le bucket_count, qui permet de spécifier la taille initiale table de hachage allouée à la table optimisée en mémoire. Son importance est telle qu’une valeur trop élevée peut conduire à une vampirisation rapide de la mémoire tandis qu’une valeur trop petite peut induire des soucis de performances sur les recherches et insertions de données en mémoire. En règle générale, il faut affecter une à deux fois la valeur du nombre de clés d’index uniques.
  • Les indexes de type range, qui offrent de meilleures performances pour les requêtes basées sur des inégalités, des intervalles de valeurs…

Chaque table optimisée en mémoire doit posséder au moins un index (et jusqu’à 8 en tout), créé lors de la définition de la table concernée (via CREATE TABLE), l’instruction CREATE INDEX n’étant pas supportée dans l’univers In-Memory OLTP. Les indexes en cluster ne sont pas supportés. Les indexes In-Memory OLTP n’existent qu’en mémoire, et leur structure de données n’est en aucun cas stockée sur disque. De plus, les opérations sur les indexes ne sont pas archivées dans le journal des transactions, permettant ainsi des économies de ressources. De ce fait, chaque fois que l’instance est redémarrée, ou la base concernée restaurée/récupérée, les indexes In-Memory OLTP sont recréés en mémoire. Contrairement aux indexes traditionnels de l’univers OLTP, ces 2 types d’indexes ne sont pas basées sur une structure de données en B-Tree. La section suivante va brièvement présenter tout particulièrement le fonctionnement d’un index de hachage.

Fonctionnement d’un index de hachage     

L’index de hachage en bref…

Un index de hachage est une structure de données constituée de N buckets (ou compartiments, ou slots) organisés sous forme d’un tableau, et contenant, chacun, un point vers une ligne de données. Il est utilisé comme point d’entrée pour les tables optimisées en mémoire. Les indexes de hachage peuvent être consultés dans la vue de catalogue sys.hash_indexes.

Fonction de hachage

La lecture de toute table optimisée en mémoire nécessite l’utilisation d’un index de hachage permettant de localiser les données requêtées dans la mémoire ; Tout index de hachage utilise une fonction de hachage pour établir les correspondances entre les clés d’index et les données. Concrètement, une fonction de hachage mappe des données de longueur variable avec des données de longueur fixe via un algorithme déterministe. Un exemple simple serait l’utilisation d’une chaîne qui retourne sa longueur. Par exemple, F(‘Microsoft’)=9, F(‘Oracle’)=6, F(‘IBM’)=3,…, où F(x) est la fonction de hachage. Et dans le contexte de l’utilisation d’un index de hachage, si l’on prend en compte un index à 9 buckets utilisant notre fonction de hachage, alors les pointeurs seront placés sur le 9ème bucket pour Microsoft, le 6ème pour Oracle et le 3ème pour IBM. En résumé :

Les principales caractéristiques d’une fonction de hachage sont les suivantes :

  • Plusieurs clés d’index peuvent être mappées au même bucket. On parle alors de hash collision (ou collision de hachage), ce qui peut impacter les performances des lectures. Ceci sera abordé dans un billet dédié.
  • Comme indiqué plus haut, la fonction de hachage utilise un algorithme déterministe. En l’occurrence, la même clé d’index est toujours référencée par le même bucket.
  • Et enfin, une fonction de hachage est utilisée pour tous les indexes de hachage, de façon équilibrée respectant la loi de Poisson, avec distribution non-uniforme des valeurs des clés d’index sur les buckets.

Tests de performances d’un index de hachage et d’un index range Les tests sont effectués au sein d’une machine dotée de 8 Go de RAM et 4 CPUs (avec MAXDOP limité à 2).     Avant de commencer… Créons une base de données InMemoryDB avec fichier de données filestream, table durable optimisée en mémoire (avec index de hachage) et table sur disque (avec index en cluster) :

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('InMemoryTableData','U') IS NOT NULL
DROP TABLE InMemoryTableData
GO
CREATE TABLE InMemoryTableData
(InMemoryTableData_ID INT NOT NULL,
 InMemoryTableData_Name VARCHAR(255) NOT NULL,
 InMemoryTableData_Date DATETIME NOT NULL, CONSTRAINT PK_InMemoryTableData PRIMARY KEY NONCLUSTERED HASH (InMemoryTableData_ID) WITH (BUCKET_COUNT = 100000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
IF OBJECT_ID('OnDiskTable','U') IS NOT NULL
DROP TABLE OnDiskTable
GO
/** On-disk table **/
CREATE TABLE OnDiskTable
(
OnDiskTable_ID INT NOT NULL,
OnDiskTable_Name NVARCHAR(255) NOT NULL,
OnDiskTable_Date DATETIME NOT NULL,
     CONSTRAINT PK_OnDiskTable PRIMARY KEY CLUSTERED (OnDiskTable_ID))
GO

Et complétons chaque table de près de 1 000 000 enregistrements aléatoires (sans signification particulière) :

USE InMemoryDB
GO
/** Variable declarations **/
DECLARE @i INT

/** Processing InMemoryTableData **/
SET @i=0
WHILE @i < 1000000
    BEGIN
         SET @i+=1;
         INSERT INTO InMemoryTableData(InMemoryTableData_ID,InMemoryTableData_Name,InMemoryTableData_Date)
                VALUES (@i,CONVERT(VARCHAR(10),CAST(NEWID() AS VARBINARY(16)),2),DATEADD(DD,@i%365,'2013-12-31'));
    END;
/** Processing OnDiskTable **/
SET @i=0
WHILE @i < 1000000
    BEGIN
         SET @i+=1;
         INSERT INTO OnDiskTable(OnDiskTable_ID,OnDiskTable_Name,OnDiskTable_Date)
                VALUES (@i,CONVERT(VARCHAR(10),CAST(NEWID() AS VARBINARY(16)),2),DATEADD(DD,@i%365,'2013-12-31'));
    END;
GO

    Entrée dans le vif du sujet

  • Scénario 1 : test de performances d’une requête avec un prédicat d’égalité.
    • Pour la table durable optimisée en mémoire.
USE InMemoryDB
GO
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT InMemoryTableData_ID, InMemoryTableData_Name, InMemoryTableData_Date
FROM InMemoryTableData
WHERE InMemoryTableData_Date = '2014-03-16'
GO

En guise d’informations principales sur les temps CPU et d’exécution :

SQL Server Execution Times: CPU time = 375 ms, elapsed time = 433 ms.

Pour le plan d’exécution suivant :

On note bien qu’aucune opération I/O n’a été effectuée.

  • Pour la table sur disque.
USE InMemoryDB
GO
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT OnDiskTable_ID
      ,OnDiskTable_Name
      ,OnDiskTable_Date
FROM OnDiskTable
WHERE OnDiskTable_Date = '2014-03-16';
GO

En guise d’informations principales sur les temps CPU et d’exécution :

Table 'OnDiskTable'. Scan count 5, logical reads 5687, physical reads 1, read-ahead reads 5614, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 108 ms, elapsed time = 906 ms.
Avec pour plan d’exécution :

On retient que la version In-Memory OLTP est logiquement la moins coûteuse comparée à celle sur disque, grâce à des temps I/O inexistants. Mais on peut noter cependant que le plan d’exécution indique que la requête peut être améliorée en ajoutant un index manquant sur la table sur disque. Ainsi, en ajoutant l’index manquant…

USE [InMemoryDB]
GO
CREATE NONCLUSTERED INDEX [IDX_Date_INC_ID_Name]
	ON [dbo].[OnDiskTable]([OnDiskTable_Date])
		INCLUDE ([OnDiskTable_ID],[OnDiskTable_Name])
GO

… on obtient les informations statistiques suivantes :

Table 'OnDiskTable'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 245 ms.

Soit un temps d’exécution plus rapide qu’avec la table optimisée en mémoire, grâce à un index seek :

Mais toutefois, si l’on ajoute un index de hachage supplémentaire sur la colonne InMemoryTableData_Date

/** Durable memory-optimized table **/
IF OBJECT_ID('InMemoryTableData','U') IS NOT NULL
DROP TABLE InMemoryTableData
GO
CREATE TABLE InMemoryTableData
(
	InMemoryTableData_ID INT NOT NULL,
	InMemoryTableData_Name VARCHAR(255) NOT NULL,
    InMemoryTableData_Date DATETIME NOT NULL,
	CONSTRAINT PK_InMemoryTableData PRIMARY KEY NONCLUSTERED HASH (InMemoryTableData_ID) WITH (BUCKET_COUNT = 100000),
    INDEX IDX_InMemoryTableData_Date NONCLUSTERED HASH(InMemoryTableData_Date) WITH (BUCKET_COUNT=3000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

… on obtient les résultats suivants :

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 163 ms.
Avec un plan d’exécution indiquant un index seek sur l’index de hachage fraîchement créé sur la colonne InMemoryTableData_Date :

Soient des performances supérieures à la version sur disque, même quand cette dernière est correctement indexée, et confirmant ainsi l’assertion suivante : les indexes de hachage boostent les performances des requêtes ayant des prédicats d’égalité.

  • Scénario 2 : test de performances avec un prédicat d’inégalité.
    • Pour la table durable optimisée en mémoire.
USE InMemoryDB
GO
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT InMemoryTableData_ID, InMemoryTableData_Name, InMemoryTableData_Date
FROM InMemoryTableData
WHERE InMemoryTableData_Date <= '2014-03-16'
GO

En guise d’informations principales sur les temps CPU et d’exécution :

SQL Server Execution Times: CPU time = 577 ms, elapsed time = 3876 ms.
Pour le plan d’exécution suivant :

On note ainsi que l’optimiseur de requêtes n’effectue plus d’index seek, mais plutôt un scan d’index particulièrement coûteux sur l’index de hachage de la clé primaire.

  • Pour la table sur disque.
USE InMemoryDB
GO
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT OnDiskTable_ID, OnDiskTable_Name, OnDiskTable_Date
FROM OnDiskTable
WHERE OnDiskTable_Date <= '2014-03-16';
GO

En guise d’informations principales sur les temps CPU et d’exécution :

Table 'OnDiskTable'. Scan count 1, logical reads 1090, physical reads 1, read-ahead reads 1085, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 172 ms, elapsed time = 3790 ms.
Avec pour plan d’exécution indiquant un index seek :

On note que les temps de traitement sont particulièrement longs dans le cas de la version In-Memory OLTP. Cela s’explique par le fait que l’index de hachage n’est pas adapté pour les opérations sur des prédicats d’inégalité. Pour faire face à cette situation, il suffit d’ajouter un index de type range, ce qui nécessitera une recréation de la table optimisée en mémoire :

/** Durable memory-optimized table **/
IF OBJECT_ID('InMemoryTableData','U') IS NOT NULL
DROP TABLE InMemoryTableData
GO
CREATE TABLE InMemoryTableData
(
	InMemoryTableData_ID INT NOT NULL,
	InMemoryTableData_Name VARCHAR(255) NOT NULL,
    InMemoryTableData_Date DATETIME NOT NULL,
	CONSTRAINT PK_InMemoryTableData PRIMARY KEY NONCLUSTERED HASH (InMemoryTableData_ID) WITH (BUCKET_COUNT = 100000),
    INDEX IDX_InMemoryTableData_Date NONCLUSTERED HASH(InMemoryTableData_Date) WITH (BUCKET_COUNT=3000),
    INDEX IDXR_InMemoryTableData_Date NONCLUSTERED(InMemoryTableData_Date) -- index range
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

En relançant la requête sur le prédicat d’inégalité, on obtient les temps suivants qui démontrent une certaine amélioration des performances :

SQL Server Execution Times: CPU time = 187 ms, elapsed time = 3272 ms.
Et si on jette un coup d’œil à son plan d’exécution…

… on note qu’un index seek a été effectué avec l’utilisation de l’index range. Ceci a eu pour effet d’optimiser les temps de traitement, et en d’autres termes, renforcer l’idée selon laquelle les indexes range fonctionnent mieux que les indexes de hachage dans le cas de prédicats d’inégalités.

Moralité : à chaque création d’une table optimisée en mémoire, il faut prévoir éventuellement la création d’un (ou plusieurs) indexes de type range si des requêtes incluant des prédicats d’inégalités sont utilisées.

Limitations

L’indexation en environnement In-Memory OLTP possède quelques limitations, dont voici une liste non-exhaustive :

  • Chaque table optimisée en mémoire ne peut supporter que jusqu’à 8 indexes.
  • Les indexes ne peuvent pas être ajoutés dans une table optimisée en mémoire déjà créée (via CREATE INDEX, par exemple). Une recréation de la table est nécessaire.
  • Toute colonne couverte par un index ne doit pas être nullable.
  • Les colonnes de type chaînes de caractères doivent avoir une collation BIN2. Cette limitation est en fait un avantage (bien qu’elle ait un impact sur la sensitivité et la casse des caractères) : une telle collation augmente grandement les temps de recherche grâce à une comparaison binaire.

Pour aller plus loin… Gardez œil ici pour d’autres billets sur le même sujet. Vous pouvez également jeter un coup d’œil aux BOL, pour plus de lectures : http://msdn.microsoft.com/en-us/library/dn511012.aspx.

Publicités

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