Il y a quelques jours, lors de ma présentation “La performance de chargement des données”, on m’a demandé quand, en termes de performance, l’identité devrait être utilisée comme clé d’index groupé, et quand ce n’est pas le cas. Donc, tout d’abord, à partir de SQL Server 2012, une séquence est en fait un meilleur choix que l’identité. Elle est plus flexible, plus ajustable et, étant externe à la table, elle permet des migrations plus faciles. Après avoir réglé cela, plongeons dans la performance.

Traditionnellement, nous voulons que notre clé d’index groupé soit:

• Étroite: Afin que l’index groupé et les index non groupés qui y pointent soient aussi minces que possible.
• Statique: Afin que nous n’ayons pas à mettre à jour les index groupés et non groupés, générer de la fragmentation en le faisant et subir des verrous et éventuellement des impasses.
• Toujours croissante: Afin que nous ne générions pas de divisions de pages et de fragmentation lors de l’insertion de données dans la table et que nous n’endommagions ainsi à la fois les performances d’insertion et de requête.

Pour la grande majorité des systèmes (environ 95% d’entre eux), ce sera un très bon choix. Le problème commence lorsque DE NOMBREUSES sessions insèrent des données dans la table en parallèle.

En examinant la structure d’un index, lorsqu’on travaille avec une clé toujours croissante, toutes les lignes iront à la même page, qui est la dernière page de l’index. Lors de l’insertion de données dans n’importe quelle page, une session doit acquérir un verrou sur cette page. Un verrou est un objet de synchronisation pour les structures en mémoire comme les pages de données qui sont dans le pool de mémoire tampon. Lors de l’insertion de données dans la page, chaque session doit acquérir un verrou exclusif afin qu’aucune autre session ne puisse modifier la structure de la page pendant que la session insère des données. Cela signifie qu’une seule session peut insérer des données dans la page à un moment donné, et si vous avez de nombreuses sessions qui écrivent dans la table en même temps, chacune d’entre elles devra attendre un temps considérable jusqu’à ce que son tour arrive pour insérer des données. Ce problème est appelé le problème d’insertion en queue.

Comment savoir si vous avez un problème:

1. Pendant que les insertions sont en cours sur la table suspecte, interrogez sys.dm_os_waiting_tasks.
2. Vérifiez si vous avez de nombreuses sessions en attente avec une attente PAGELATCH sur la même page.
3. Activez le drapeau de trace 3604 en utilisant la syntaxe: DBCC TRACEON (3604).
4. Exécutez DBCC PAGE pour voir le contenu de la page pertinente. Dans notre cas: DBCC PAGE (8, 1, 10078, -1).
5. Dans le volet des résultats, recherchez ObjectId.
6. Ensuite, prenez cet ObjectId et trouvez la table associée en utilisant la fonction Object_Name (dans notre cas, 1013578649): SELECT OBJECT_NAME (1013578649).
7. Est-ce votre table? Vous souffrez très probablement du problème d’insertion en queue.

Mise à jour: Kenneth Fisher (Blog | Twitter) a suggéré une meilleure façon de remplacer les étapes 3 à 6 et de trouver la table associée plus rapidement. Nous utiliserons les détails de description des ressources que nous avons récupérés ci-dessus avec la fonction sys.dm_database_page_allocations. 8 est l’ID de la base de données, 1 est l’ID du fichier et 10078 est l’ID de la page verrouillée. Cela nous amène à la requête suivante:

SELECT object_name(object_id,database_id)
FROM sys.dm_db_database_page_allocations(8,null,null,null,null)
WHERE allocated_page_file_id = 1
AND allocated_page_page_id = 10078

Alors, quand devrais-je utiliser l’identité? Si vous ne souffrez pas du problème d’insertion en queue, vous pouvez très probablement utiliser une clé toujours croissante comme l’identité. Si vous en souffrez, vous devez commencer à réfléchir à d’autres solutions.

Solutions:

Lorsque vous avez ce problème, il est temps de laisser l’identité (ou la séquence) derrière vous et de travailler avec d’autres méthodes. Il existe quelques options principales:

1. Charger dans un tas, ce qui fonctionne mieux avec le chargement parallèle.
2. Travailler avec In-Memory OLTP, qui utilise des structures de données sans verrouillage pour atténuer les verrous.
3. Travailler avec une clé non séquentielle comme GUID.
4. Travailler avec le partitionnement par hachage, qui sépare l’index groupé en quelques arbres d’index physiques distincts.
5. Remplir la table de sorte que chaque page ne contienne qu’une seule ligne (si votre table est petite).
6. Utiliser une clé métier qui se répartit dans tout l’index et pas à un endroit spécifique.
7. Mettre en œuvre un “index inversé”.

Dans le prochain article, je passerai en revue les solutions en détail.

L’article Quand devez-vous utiliser l’identité comme clé d’index groupé? est apparu en premier sur Madeira Data Solutions.