Locked pages in memory et Instant File Initialization sont deux options de performance utilisés fréquemment avec SQL Server. Pour rappel la première option d’empêcher la pagination du buffer pool de SQL Server par le système d’exploitation et la deuxième permet de réduire considérablement les temps d’allocation de fichier en supprimant la phase de remplissage de zéro des fichiers de bases de données. Ceci n’est valable uniquement que pour les fichiers de données SQL Server. Pour bénéficier de ces deux options de performances  il faut octroyer au compte de service SQL Server les privilèges nécessaires. Cependant dans beaucoup de littérature et très certainement par habitude, c’est le compte de domaine utilisé par le service SQL qui bénéficie de ces privilèges dans la plupart des cas. Mais qu’en est-il avec l’apparition des SID de service ?

Avant la version 2008 de SQL Server on avait gère le choix entre pouvoir octroyer ces droits soit au compte de domaine ou au groupe prédéfini créé pendant l’installation d’une installation SQL Server de la forme SQLServerMSSQLUser$[servername]$MSSQLServer pour une instance par défaut. Dans le premier cas lorsque l’on change de compte utilisateur il faudra penser à paramétrer le nouveau compte utilisé avec les privilèges nécessaires. Dans l’autre cas cette gymnastique n’est plus nécessaire mais par définition l’ensemble des membres potentiels du groupe concerné pourront bénéficier de ce privilège. Cela peut être bien entendu problématique.

Depuis SQL Server 2008 (et Windows 2008) on a vu l’apparition des SID de services qui permettent de créer une identité propre de service. L’avantage bien entendu est qu’il est possible d’utiliser directement cet identifiant pour isoler les ressources utilisées par une instance SQL. Une autre utilisation moins répandue est d’octroyer les privilèges cités plus hauts directement à l’identité du service SQL d’une instance donnée.

Pour savoir si cela fonctionne nous avons selon l’option de performance des outils à disposition. Pour le verrouillage de page en mémoire il suffit d’utiliser la commande DBCC MEMORYSTATUS alors que pour Instant File Initialization on pourra utiliser les traces flag 3004 et 3605. L’activation de ces traces flag permettent de pouvoir lire les informations d’initialisation de fichier directement dans le journal d’erreur SQL.

Test 1 : compte de domaine utilisateur classique sans privilèges

Pour les pages verrouillés en mémoire :

 

Pour Instant File Initialization :

USE [master]
GO

CREATE DATABASE [TEST] ON  PRIMARY
( NAME = N’TEST’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST.mdf’ , SIZE = 2048MB , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N’TEST_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_log.LDF’ , SIZE = 100MB , MAXSIZE = 2048GB)
GO

L’idée ici est de créer une base de données et de visualiser ensuite les événements générés dans le journal d’erreur SQL. On peut voir avec l’activation des traces flag 3004 et 3605 dans les options de démarrage de démarrage du service SQL (-T3004; -T3605) les informations relatives à l’initialisation des fichiers et de leur remplissage par des 0 (Zeroing …). On constate que les 2 types de fichiers sont concernés dans ce premier cas.

Test 2 : paramétrage du SID de service SQL avec les privilèges nécessaires pour l’activation de nos 2 options de performances. Pour la prise en compte des deux options il faudra redémarrer l’instance SQL Server.

Pour le verrouillage de pages en mémoire :

On vérifie à nouveau avec DBCC MEMORYSTATUS :

Pour Instant File Initialization :

On recrée ensuite notre base de données avec le même script que précédemment :

USE [master]
GO

DROP DATABASE [TEST]
GO

CREATE DATABASE [TEST] ON  PRIMARY
( NAME = N’TEST’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST.mdf’ , SIZE = 2048MB , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N’TEST_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TEST_log.LDF’ , SIZE = 100MB , MAXSIZE = 2048GB)
GO

Vous remarquerez au passage que le temps de création de la base a nettement diminué (du moins c’est le cas chez moi). On vérifie à nouveau dans le journal d’erreur SQL :

On constate cette fois que seul le fichier journal est concerné (ce qui est normal comme expliqué plus haut).

CQFD !!!

David BARBARIN (Mikedavem)
MVP SQL Server