[SQL Server] In-Memory OLTP : de l’importance du paramétrage du BUCKET_COUNT

Ce billet aborde le cas d’une option particulière utilisée lors de la création d’un index de hachage d’une table optimisée en mémoire : le BUCKET_COUNT.

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 . Vous pouvez également aller ici, pour en savoir plus sur les indexes et les buckets dans le domaine de l’In-Memory OLTP.

Qu’est-ce que le BUCKET_COUNT ?

Le BUCKET_COUNT représente le nombre de buckets qu’un index de hachage possède. Un bucket peut être vu comme un compartiment (ou slot) où est positionné une ligne de données.

Comme précisé ici, l’importance du BUCKET_COUNT 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.

La taille maximale du BUCKET_COUNT d’un index de hachage est de l’ordre de 1 073 741 824. Sachant qu’un bucket « pèse » 8 octets, on peut déduire que la quantité de mémoire maximale qu’un index pourrait utiliser serait de 8 Go (pour être exact, 8 589 934 592).

 

Détermination de la valeur idéale d’un BUCKET_COUNT

En règle générale, il faut affecter une à deux fois la valeur du nombre de clés d’index uniques.

Différentes méthodes permettent de savoir si la valeur du BUCKET_COUNT d’un index est adéquate ou non. Toutefois, la DMV sys.dm_db_xtp_hash_index_stats offre des informations intéressantes sur les indexes de hachage, telles que :

  • L’identifiant de la table (object_id) et de l’index cible (index_id).
  • Le nombre total de buckets (total_bucket_count)
    utilisé par chaque index concerné, à leur création.
  • Le nombre de buckets vides (empty_bucket_count).
  • La longueur moyenne de la chaine de lignes de données (avg_chain_length).
  • La taille maximale de la chaîne de lignes de données (max_chain_length).

Les informations relatives à la chaine de lignes permettent d’avoir une idée du nombre de clés « hachées » à un même bucket. Elles permettent également de déterminer la présence ou non de clés en double (en comparant empty_bucket_count avec avg_chain_length) ou s’il manque des buckets (avg_bucket_count et max_bucket_count élevés en comparaison d’empty_bucket_count).

Pour être factuel, nous allons effectuer quelques démonstrations techniques sur une machine dotée de 8 Go de RAM et 4 CPUs :

  • Création d’une base de données supportant l’In-Memory OLTP :
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
  • Création d’une table, avec index de hachage doté d’un BUCKET_COUNT égal à 64 buckets.
USE InMemoryDB
GO
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,
CONSTRAINT PK_InMemoryTableData PRIMARY KEY NONCLUSTERED HASH (InMemoryTableData_ID) WITH (BUCKET_COUNT=64)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA)
GO
  • Alimentation de ladite table de 10 000 lignes de données.
USE InMemoryDB
GO
DECLARE @i INT=0
WHILE @i < 10000
BEGIN
    BEGIN
        INSERT INTO dbo.InMemoryTable(InMemoryTable_ID, InMemoryTable_Name)
            VALUES (@i,@i)
        SET @i += 1
    END
GO

La phase de préparation de notre exemple faite, utilisons la DMV sys.dm_db_xtp_hash_index_stats pour avoir un aperçu des informations relatives à l’index de hachage :

USE InMemoryDB
GO
SELECT s.object_id
	,OBJECT_NAME(s.object_id) AS 'table_name'
	,s.index_id
	,s.total_bucket_count
	,s.empty_bucket_count
	,s.avg_chain_length
	,s.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats s
WHERE OBJECT_NAME(s.object_id)='InMemoryTable'
GO

La requête ci-dessus donne le résultat suivant :


Comme on peut le remarquer, les valeurs d’avg_chain_length et max_chain_length sont supérieures au nombre total de buckets de l’index utilisé. Cela laisse entendre que la valeur de BUCKET_COUNT spécifiée lors de la création de l’index de hachage était trop basse. D’ailleurs, cela joue un rôle non-négligeable au niveau des performances.

Ainsi, lançons la requête suivante permettant de peupler la table de test de 10 000 enregistrements supplémentaires :

DECLARE @i INT=10000
WHILE @i < 20000
BEGIN
        INSERT INTO dbo.InMemoryTable(InMemoryTable_ID, InMemoryTable_Name)
            VALUES (@i,@i)
        SET @i += 1
END
GO

Ce qui donne :


Comme on peut le souligner, l’opération DML a contribué à augmenter la valeur moyenne et maximale de la longueur de la chaîne d’enregistrements. De plus, la durée d’exécution de l’opération est de l’ordre de 4 secondes, et le nombre total de buckets étant fixe, il faut donc envisager une recréation de la table avec une valeur de BUCKET_COUNT plus adaptée.

Pour avoir une idée, il suffit de multiplier par 1, 1.5 ou 2 le nombre de clés distinctes de la table cible (dans notre exemple ci-dessous, 2) :

USE InMemoryDB
GO
SELECT POWER(2, CEILING(LOG(COUNT(0))/LOG(2))) AS 'BUCKET_COUNT'
FROM (SELECT DISTINCT InMemoryTable_ID
	  FROM InMemoryTable) t
GO

Ce qui donnera le résultat suivant :


Après avoir recréé la table InMemoryDB, comme dans notre exemple initial plus haut, mais avec un index de hachage doté d’un BUCKET_COUNT égal à 32768 (au lieu de 64), et inséré 10 000 enregistrements initiaux, nous obtenons les informations suivantes issues de la DMV sys.dm_db_xtp_hash_index_stats :


Et après avoir réinséré 10 000 enregistrements supplémentaires :

Il est important de retenir que l’avg_chain_length doit idéalement être à 1, pour des raisons de performances, d’après Microsoft. Dans la section suivante, nous allons justement aborder la question des performances.

 

BUCKET_COUNT et performances

Dans la section précédente, nous avons vu comment déterminer la valeur idéale d’un BUCKET_COUNT. Maintenant se pose la question suivante : la valeur d’un BUCKET_COUNT impacte-t-elle les performances des opérations DML sur la table optimisée en mémoire concernée ?

Pour répondre à la question, reprenons la table InMemoryTable avec BUCKET_COUNT fixé à 64 buckets, et créons une procédure stockée nativement compilée en mémoire (afin de prendre la pleine mesure, en termes de performances, de l’In-Memory OLTP) destinée à insérer 1 million d’enregistrements :

USE InMemoryDB
GO
CREATE PROCEDURE usp_insert_inmemorytable
WITH
NATIVE_COMPILATION,
SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN
    ATOMIC
WITH
    (
        TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='us_english'
    )
    DECLARE @i INT=0
    WHILE @i < 1000000
    BEGIN
        INSERT INTO dbo.InMemoryTable(InMemoryTable_ID, InMemoryTable_Name)
			VALUES (@i,@i)
        SET @i += 1
    END
END
GO

En lançant la procédure stockée pour l’insertion de 1 million d’enregistrements, nous obtenons 5 minutes en guise de temps d’exécution.

En revanche, si l’on recrée la table InMemoryTable avec, cette fois-ci, une valeur de BUCKET_COUNT égale à 1000000 (soit autant que le nombre d’enregistrements attendus), nous obtenons une durée d’exécution de l’ordre de… 2 secondes. Par curiosité, si l’on jette un coup d’œil aux informations de la DMV sys.dm_db_hash_index_stats, nous aurons droit aux informations suivantes :


L’index de hachage possède assez de buckets pour accueillir 1 million d’enregistrements.

Pour information, la version avec le BUCKET_COUNT à 64 donne :

Moralité : un nombre suffisant de buckets permet d’accélérer les temps de réponse, en matière d’insertions et de sélections. En effet, moins il y a de buckets, plus il y a des clés qui référencient le même bucket, ce qui augmente la longueur de la chaîne de lignes, que SQL Server doit maintenir durant les insertions et scanner durant les sélections. Dans la mesure où d’après Microsoft, un avg_chain_length doit idéalement être égal à 1, la version avec le BUCKET_COUNT à 64 doit donc être changée : soit en augmentant la valeur initiale à 1 million de buckets au moins, soit en adoptant un index non-cluster.

Il est toutefois important de retenir qu’une valeur de BUCKET_COUNT trop élevée peut impacter la volumétrie de la mémoire utilisée, ce qui signifie qu’outre le fait de bien déterminer le BUCKET_COUNT, assez de RAM doit être prévue pour les données.

Pour aller plus loin…

Ce billet a permis d’avoir un aperçu de l’importance de l’affection d’une bonne valeur de BUCKET_COUNT d’un index de hachage, lors de la création d’une table optimisée en mémoire.

Gardez œil ici pour d’autres billets autour de l’In-Memory OLTPVous pouvez également jeter un coup d’œil ici, pour plus de lectures :  http://msdn.microsoft.com/en-us/library/dn494956.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