Utiliser les vues dynamiques de gestion pour gérer les index

A partir de la version 2005 de SQL Server, vous avez à votre disposition, dans le schéma sys, un certain nombre de vues système qui portent non pas sur les métadonnées de la base en cours, mais sur l'état actuel du SGBD. Ces vues sont nommées "vues dynamiques de gestion", ou "dynamic management views". Elles sont toutes préfixées par dm_. Vous les trouvez dans l'explorateur d'objets, sous vues / vues système

Vous y trouverez la plupart des vues disponibles. Certaines n'y figurent pas car elles sont non documentées, ou sont implémentées sous forme de fonctions. Nous allons découvrir quelques unes de ces vues, dédiées aux index, qui vont vous permettre d'optimiser leur utilisation, et permettre d'améliorer les performances de votre système.

Certaines de ces vues dynamiques de gestion se basent sur une instance cachée de trace (cf. article sur les performances où le profiler est abordé), qui tourne en permanence avec l'instance SQL. Cette trace est très peu coûteuse en performances et les résultats qu'elle permet d'obtenir sont, comme vous le verrez, fort utiles. Si vous désirez néanmoins la désactiver, il est possible de le faire.
Les données retournées par ces vues dynamiques sont maintenues dans un cache spécial de la mémoire adressée par SQL Server. Elles ne sont pas persistées sur le disque. Les compteurs sont donc remis à zéro lors du redémarrage du service SQL. Par conséquent, pour que ces vues vous offrent des informations utiles, vous ne devez les consulter qu'après un temps significatif d'utilisation du serveur.

Un index créé n'est pas forcément utilisé. Notamment, l'optimiseur peut choisir de parcourir la table au lieu d'utiliser l'index, si celui-ci est peu sélectif (cf. mon article sur les statistiques). Tout index a un coût de maintenance, c'est-à-dire qu'il doit répercuter en temps réel toutes les modifications apportées dans la table, sur les colonnes qui composent sa clé. Un index inutilisé est donc pénalisant pour les performance, ainsi bien sûr que pour l'espace de stockage : il consomme de l'espace disque sans aucune utilité. Il faut donc le supprimer. Mais comment savoir si un index est utilisé ou non ? Avant 2005, l'opération était longue et compliquée. Désormais, vous avez à disposition une vue dynamique qui vous offre toutes les informations nécessaires. cette vue s'appelle sys.dm_db_index_usage_stats. Elle affiche les informations suivantes :

Un seek et un parcours de l'arbre équilibré de l'index, pour obtenir avec la ligne correspondante avec un maximun d'efficacité. Plus le nombre de seeks est élevé, plus l'index est réellement utilisé à sa pleine capacité.
Un scan ("analyse" dans la traduction française de SQL Server) est le parcours complet du noeud feuille (leaf node) de l'index. Le noeud feuille est le dernier niveau, autrement dit la base de l'index, où chaque recherche se termine. Un scan sur un index ordonné (clustered) correspond à un scan sur la table, puisque le noeud feuille d'un index ordonné est la table elle-même.
Le lookup (bookmark lookup), ou "recherche de clés" dans la traduction française, correspond à la nécessité, lorsqu'une recherche sur l'index non ordonné a atteint le noeud feuille, de retrouver les lignes correspondantes dans la table. Les colonnes lookup de la vue sys.dm_db_index_usage_stats indiquent que l'index a participé à une opération de recherche de clés. Elle n'a de sens que sur un index ordonné. En effet, la recherche de clé ne se produit qu'à partir d'un index non ordonné. Cette recherche peut se faire soit sur un RID (Row ID, ou identifiant de ligne) dans le cas d'une table "heap" (sans index ordonné), soir sur la clé de l'index ordonné si la table en comporte un. Dans ce dernier cas, la recherche de clé se fait donc par un parcours de l'index ordonné. C'est ce parcours qui est indiqué dans les colonnes lookup de la vue dynamique.
L'update, enfin, est simplement l'opération de maintenance de l'index, lorsqu'une colonne qui compose sa clé est insérée, mise à jour ou supprimée.

Voici un exemple de requête impliquant la vue dynamique sys.dm_db_index_usage_stats :

USE AdventureWorks
GO

SELECT 
	SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ius.object_id) as tbl,
	i.name as idx, 
	i.type_desc as idxType,
	i.is_unique,
	i.is_primary_key,
	user_seeks, 
	user_scans, 
	user_lookups, 
	user_updates, 
	last_user_seek, 
	last_user_scan, 
	last_user_lookup, 
	last_user_update, 
	system_seeks, 
	system_scans, 
	system_lookups, 
	system_updates, 
	last_system_seek, 
	last_system_scan, 
	last_system_lookup, 
	last_system_update 
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
JOIN sys.tables t ON i.object_id = t.object_id
WHERE database_id = DB_ID()
ORDER BY tbl

Afin de rendre le résultat lisible, il est utile de joindre la vue avec la vue système sys.indexes, qui nous permet de retrouver le nom de l'index, son type, s'il est un index de clé primaire, et si l'index est unique (il garantit l'unicité de sa clé).
Les valeurs possibles de sys.indexes.type_desc sont :

Que faire du résultat de cette requête ?
C'est simple : si vous constatez un très petit nombre de seeks sur un index non ordonné, et un grand nombre d'updates, cela signifie que votre index est peu utile, et coûteux à maintenir. Vous pouvez donc considérer sa suppression. Répétons-nous : ne vous basez sur ces résultats qu'après un laps de temps suffisamment représentatif depuis le dernier lancement du service SQL.
Voici par exemple une requête listant les index n'ayant pas été utilisés, et supprimables, ainsi que du code générant la suppression des index inutilisés :

-- index supprimables
SELECT 
	SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id) as tbl,
	i.name as idx, 
	ISNULL(user_updates, 0) + ISNULL(system_updates, 0) as updates 
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN 
	(sys.indexes i 
	JOIN sys.tables t ON i.object_id = t.object_id )
	ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
	i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
	t.type_desc = 'USER_TABLE'
ORDER BY tbl

-- génération de code pour supprimer les index inutiles
SELECT 
	'DROP INDEX [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME(i.object_id) + '].[' + 
	i.name + ']' 
FROM sys.dm_db_index_usage_stats ius
RIGHT JOIN 
	(sys.indexes i 
	JOIN sys.tables t ON i.object_id = t.object_id )
	ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0 AND
	i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND
	t.type_desc = 'USER_TABLE'

La fonction dynamique sys.dm_db_index_operational_stats vous permet d'obtenir des informations sur la vie et les opérations de maintenance d'un index. Il s'agit d'une fonction, qu'il faut donc appeler en passant des paramètres : nom de la base, nom de la table, nom de l'index, nom de la partition.
Des paramètres passés à NULL permettent de retourner des lignes pour chaque index de la base/table.
Vous pouvez ainsi mesurer si l'index est plus ou moins coûteux à maintenir, s'il doit opérer de nombreux splits de pages pour accomoder de nouvelles lignes, quelles sont les temps d'attentes sur l'index...

Pour comprendre ce tableau, nous devons expliquer quelques principes de stockage et de verrouillage :

Une table de type HEAP est simplement une table qui n'est pas physiquement ordonnée par un index ordonné (clustered). Une table HEAP stocke ses lignes sans ordre déterminé. Ainsi, pour référencer dans un index une ligne d'une table HEAP, SQL Server utilise un pointeur, appelé RID (Row ID), composé d'un identifiant de fichier, de page, puis de position à l'intérieur de la page.

Lorsqu'une ligne dans une table HEAP est mise à jour, que la taille de la ligne grandit et qu'il ne reste plus de place dans la page, la ligne est déplacée dans une autre page. Pour éviter une mise à jour de tous les index non ordonnés pour accommoder le nouveau RID de la ligne, SQL Server pose, à la place de l'ancienne ligne, une référence vers la nouvelle position de ligne, ce qu'on appelle un enregistrement de renvoi (forwarding record).

Lors d'une opération de traitement par lot (bulk operation), SQL Server crée des LOB pour stocker temporairement les données. Ces LOB sont dit orphelins (orphan LOB).

La page de données contenant des lignes a une taille fixe de 8KO, une ligne ne peut dépasser cette taille. En SQL Server 2005, un mécanisme a été mis en place pour permettre de se libérer de cette limite pour certains types de données : varchar, xml. Lorsque le remplissage d'une colonne d'un de ces types de données dépasse la limite de 8060 octets, une page supplémentaire est créée pour y stocker les valeurs supplémentaire, dans une unité d'allocation particulière. Ce mécanisme est nommé "dépassement de page" (row overflow).

Le latch est un verrou plus léger que le verrou traditionnel, utilisé pour protéger des ressources système, comme les index ou les tables internes. Il demande moins de ressources et permet d'optimiser le verrouillage des pages d'index durant leur parcours par un seek. En cas de forte pression, une contention peut se produire sur les latches : une attente trop longue pour la libération de ressources système, par exemple dans tempdb, sur les tables système, à cause d'une trop grande fréquence de création/destruction de tables temporaires.

Vous pouvez donc utiliser cette fonction système pour collecter des statistiques physiques, d'utilisation, de charge ou de contention sur vos tables et vos index. Exemple :

SELECT	object_name(s.object_id) as tbl, 
		i.name as idx, 
		range_scan_count + singleton_lookup_count as [pages lues],
		leaf_insert_count+leaf_update_count+ leaf_delete_count as [écritures sur noeud feuille],
		leaf_allocation_count as [page splits sur noeud feuille],
		nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count as [écritures sur noeuds intermédiaires],
		nonleaf_allocation_count as [page splits sur noeuds intermédiaires]
from sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL) s
JOIN sys.indexes i ON i.object_id = s.object_id and i.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
order by [pages lues] desc

Lors de la génération du plan d'exécution de chaque requête (phase d'optimisation), le moteur relationnel teste différents plans d'exécution et sélectionne le moins coûteux. Dans certains cas, ce moteur d'optimisation a la capacité de constater que la requête aurait été bien mieux servie si un index avait été présent. Cette information est retournée lorsqu'on affiche un plan d'exécution détaillé en XML.
Les informations d'index manquants sont également stockés dans un espace de cache, et peuvent être requêtés à travers trois vues système, qui offrent toutes les informations nécessaire pour la création de l'index : colonnes qui devraient composer le clé, colonnes à inclure dans le noeud feuille, avec un compteur qui indique le nombre de fois où l'index aurait été utile.
Cette information n'est pas exhaustive, dans le sens ou l'optimiseur ne va pas détecter tous les index manquants. Il ne le fait que sur cetraines requêtes, lorsqu'il a la capacité de comprendre qu'un index aurait été utile. Cela ne vous décharge pas du travail d'optimiser les autres requêtes par la création d'index. Pour cette tâche supplémentaire, l'outil nommé DTA (Database Tuning Advisor) vous sera également utile.
Mais, concerant les vues dynamiques d'index manquants, voici une requête qui vous donne toutes les informations :

SELECT	object_name(object_id) as objet, d.*, s.*
FROM	sys.dm_db_missing_index_details d 
INNER JOIN sys.dm_db_missing_index_groups g
	ON	d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
	ON	g.index_group_handle = s.group_handle
WHERE	database_id = db_id()
ORDER BY  s.user_seeks DESC, object_id

Comme elle vous dit tout, vous pouvez même faire générer par une requête le code DDL nécessaire à la création des index, ainsi, vous n'avez plus qu'à copier le code généré et à l'exécuter.
Toutefois, ne créez pas forcément tous les index conseillés, mais concentrez-vous sur ceux dont le nombre de "user seeks" est le plus importants : cette mesure vous indique combien de fois l'index aurait pu être utile.
Voici un exemple de code pour générer vos index :

SELECT	
	'CREATE INDEX nix$' + lower(object_name(object_id)) + '$' 
	+ REPLACE(REPLACE(REPLACE(COALESCE(equality_columns, inequality_columns), ']', ''), '[', ''), ', ', '_')
	+ ' ON ' + statement + ' (' + COALESCE(equality_columns, inequality_columns) + ') INCLUDE (' + included_columns + ')',
object_name(object_id) as objet, d.*, s.*
FROM	sys.dm_db_missing_index_details d 
INNER JOIN sys.dm_db_missing_index_groups g
	ON	d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
	ON	g.index_group_handle = s.group_handle
WHERE	database_id = db_id()
ORDER BY  s.user_seeks DESC, objet

Grâce aux vues dynamiques de gestion, vous avez la possibilité de suivre en temps réel le comportement de SQL Server et des diverses couches assurant le fonctionnement optimal du SGBD. Nous avons détaillé les quelques vues permettant de suivre l'utilisation des index pour vous permettre d'optimiser sans effort les performances. Prenez le temps de découvrir les autres vues sys.dm_..., elles seront des alliées précieuses au quotidien pour vos besoins de supervision et d'optimisation.