Préparation de la table In Memory (nom de code Hekaton)

Comme vous le savez certainement, In Memory (en SQL 2014 - 12.0.2000 en tout cas) arrive avec plusieurs contraintes. La table HEAP small_table que j'utilise depuis le début de mon analyse sur l'isolation ne peut pas être créée en l'état.
Une des limitation est qu'une table InMemory si elle est déclarée durable (c'est à dire que son contenu est conservé au démarrage de l'instance), doit avoir une PrimaryKey. Pour éviter que l'ajout d'une PK optimise toutes mes requêtes (et donc m’oblige à relancer mon benchmark avec les autres niveaux d'isolation), j'ai choisi d'ajouter une clé de type Identity.
Cette PK est protégée par un index de type HASH (type d'index optimal pour les tables InMemory) et pour les HASH INDEX, vous devez correctement choisir le nombre de BUCKET. Pour cela, je vous renvoie sur la doc Microsoft ; pour mon cas, j'ai pris un bucket_count de 512 ce qui est certainement un peu faible (1024 aurait peut-être été plus optimal). InMemory table creation

Du côté des SELECT, j'ai vérifié que l'ajout de cette PK ne modifie pas fortement les plans d'exécutions. Je continue donc mon analyse en conservant les temps d'attentes relevés sur mes tests précédents.

Procédure small_table_update

Dans le mode In Memory, vous pouvez utiliser des procédures qui sont stoquées sur disque compilées. Ces procédures sont les Natively Compiled Stored Procedures et comme pour les tables In Memory, elles sont plus performantes lors de charge transactionnelle importante. En effet, en temps normal, les procédures sont compilées lors de leur exécution ; ici ces procédures sont déjà compilées. Par contre, pour pouvoir utiliser ce mode, il y a différentes contraintes ; notamment il est impossible d'utiliser l'opérateur LIKE ce qui est mon cas dans la procédure du benchmark.
J'ai choisi de garder le même code de procédure et donc de ne pas utiliser ce mode de procédure compilée nativement mais de rester sur le mode classique où la procédure est compilée lors de son appel (son exécution).

Quelle isolation avec les tables In Memory ?

Dans ce mode-là, les niveaux d'isolation sont traités de façon totalement différentes. Pour commencer, avec Hekaton, les tables ne sont pas stockées sur disque dans les fichiers MDF, NDF et LDF que nous connaissons mais dans un ensemble de fichiers présents dans un nouveau FILEGROUP. Pour optimiser les performances OLTP, Microsoft a choisi d'avoir des fichiers sur disque de taille fixe qui contiennent toutes les lignes de la table. Lors des INSERTS, de nouveau fichiers sont créés pour les nouvelles lignes (optimisés par lots). En parallèle, les modifications sur les tables ne sont plus conservées via le fichier de transaction log, mais via des fichiers de masques qui indiquent les lignes qui ont été supprimées par des DELETES.
Les UPDATES sont traités comme un DELETE suivi d'un INSERT.
Avec ce mode de fonctionnement, les anciennes valeurs sont conservées tant qu'une transaction active pourrait en avoir besoin ; et un processus asynchrone va supprimer les anciennes données lorsqu'elles ne sont plus utiles (sorte de garbage collector).

Les transactions d'un point de vue applicatif peuvent lire les données en READ_COMMITED. Il peut y avoir des cas où 2 transactions essaient de mettre à jour la même donnée, dans ce cas, SQL Server détecte une incompatibilité et renvoie une erreur (41302) sur la 2ème transaction qui fait une modification. Ce cas ne devrait pas se produire avec mon benchmark puisque chaque session (worker) modifie des lignes différentes.

Les résultats

Avec une table In Memory, SQL Server donne des performances excellentes ! Mon benchmark permet de voir que la session qui fait les SELECTS (le monitored_thread) n'est plus du tout impactée par les autres sessions et cela même si le select_ratio diminue.
Vous pouvez-voir sur le graphique ci-dessous que les performances sont très bonnes. Les 2 seuls événements systèmes sont :

  • SOS_SCHEDULER_YIELD : c'est la preuve que SQL Server attend la libération de la CPU qui est occupée par un process autre qu'SQL Server
  • XTP_HOST_WAIT : je n'ai pas eu beaucoup d'information sur cet event mais XTP = eXtrem Transactional Processing qui est la dénomination interne pour In Memory. Je pense que SQL Server attend une ressource système (Kernel CPU ?) lors de cet event

Dans tous les cas, les attentes sont vraiment faibles voir, illisibles sur le graphique :-)

InMemory wait event

Par ailleurs, côté client, le benchmark est paramétré pour valider les transactions sans utiliser le mode Auto-Commit ; nous pouvons donc comparer ces performances à celles que nous avons sur Oracle (les 2 ont le même niveau d'isolation). Voici un graphique global qui reprend les 4 paramétrages que j'ai réalisé avec mon benchmark. Pour SQL Server, le cas "1- MSSQL Default" ne peut pas être comparé aux autres puisque c'est le paramétrage par défaut de SQL Server avec donc un mode AutoCommit et des locks sur les lectures (voir article précédent sur ce test). 4 paramétrages

Conclusion

Avec ce mode In Memory, SQL Server propose un mode très performant pour les tables qui subissent une charge transactionnelle importante avec un select_ratio plus faible qu'habituellement. Il faut garder à l'esprit qu'il y a encore beaucoup de contraintes pour pouvoir utiliser ce mode (pas de FK pour commencer) mais cela sera certainement corrigé par les futures versions d'SQL Server.

Je vous donne ici mon fichier Excel contenant toutes les mesures que j'ai réalisé sur mon benchmark avec les différents paramétrages. Vous pouvez vérifier mes analyses.
Pour ces exécutions, j'ai utilisé 10 threads qui font chacun 1000 fois la boucle principale (numberOfRun = 1000).

N'hésitez pas à me dire ce que vous pensez de mon analyse et ma démarche. Je pense que certain points du benchmark pourait être optimisés pour avoir de meilleures performances mais c'est à mon avis le cas sur les 2 systèmes. Je ne souhaitais pas faire de comparaison entre les 2 systèmes du point de vue de la performance brute (quel système est le plus rapide) mais voir les effets du select_ratio sur une session qui ne fait que de la lecture (SELECT).