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

Découvrez les vues dynamiques de gestion, pour vous permettre de gérer au mieux vos index, de juger de leur pertinence et de créer des index manquants.

Article lu   fois.

L'auteur

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Utilisation des vues dynamiques de gestion

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

explorateur d'objets
explorateur d'objets

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.

II. Informations sur l'utilisation des index

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 :

user_seeks nombre de recherches à travers l'index dûs à une requête utilisateur
user_scans nombre de parcours du noeud feuille de l'index dûs à une requête utilisateur
user_lookups nombre de recherche de clés (bookmark lookups) dûs à une requête utilisateur
user_updates nombre de mises à jour de l'index, dûs à une requête DML (INSERT, UPDATE, DELETE) utilisateur
last_user_seek  
last_user_scan  
last_user_lookup  
last_user_update  
system_seeks nombre de recherches à travers l'index dûs à une requête système
system_scans nombre de parcours du noeud feuille de l'index dûs à une requête interne
system_lookups nombre de recherche de clés (bookmark lookups) dûs à une requête interne
system_updates nombre de mises à jour de l'index, dûs à une requête DML (INSERT, UPDATE, DELETE) interne
last_system_seek  
last_system_scan  
last_system_lookup  
last_system_update  


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 :

 
Sélectionnez

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 :

CLUSTERED un index ordonné, qui trie physiquement les lignes de la table selon sa clé
NON CLUSTERED un index non ordonné : un arbre équilibré dont le noeud feuille pointe sur un RID ou la clé de l'index ordonné
HEAP une table sans index ordonné. Ne correspond pas à un index, mais à une table, donc des pages de données. Vous ne trouverez pas de seek sur un heap
XML Un index XML, créé sur une colonne de type XML


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 :

 
Sélectionnez

-- 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'

III. Obtenir les informations opérationnelles de l'index

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...

partition_number numéro de partition si la table est partitionnée. Base 1
leaf_insert_count Nombre total d'insertions au niveau feuille de l'index
leaf_delete_count Nombre total de suppressions au niveau feuille de l'index
leaf_update_count Nombre total de mises à jour au niveau feuille de l'index
leaf_ghost_count Nombre total de lignes au niveau feuille marquées pour suppression, mais pas encore supprimées. Elles seront supprimées par un thread de nettoyage qui s'exécute à intervalles réguliers.
nonleaf_insert_count Nombre total d'insertions aux niveaux intermédiaires de l'index. Valeur 0 sur une ligne correspondant à une table heap
nonleaf_delete_count Nombre total de suppressions aux niveaux intermédiaires de l'index. Valeur 0 sur une ligne correspondant à une table heap
nonleaf_update_count Nombre total de mises à jour aux niveaux intermédiaires de l'index. Valeur 0 sur une ligne correspondant à une table heap
leaf_allocation_count Nombre total d'allocations de page sur le niveau feuille d'un index ou un heap. Sur un index, une allocation de page correspond à un page split
nonleaf_allocation_count Nombre total d'allocations de page causés par un split, sur les niveaux intermédiaires de l'index. Valeur 0 sur une ligne correspondant à une table heap
leaf_page_merge_count Nombre total de fusions de page sur le niveau feuille
nonleaf_page_merge_count Nombre total de fusions de page sur les niveaux intermédiaires. Valeur 0 sur une ligne correspondant à une table heap
range_scan_count Nombre total de scans (de table ou d'une plage plus petite)
singleton_lookup_count Nombre total de récupérations de lignes distinctes depuis l'index ou la table heap
forwarded_fetch_count Nombre de lignes récupérées à travers un enregistrement de renvoi (forwarding record). Valeurs existant seulement sur une table heap. Donc toujours 0 sur des index
lob_fetch_in_pages Nombre total de pages d'objets larges (large object (LOB)) récupérées, depuis des unités d'allocation LOB_DATA. Signifie la présence de colonnes TEXT, IMAGE, ou VARCHAR(MAX) ou VARBINARY(MAX), ou XML
lob_fetch_in_bytes Nombre total d'octets d'objets larges récupérés
lob_orphan_create_count Nombre total de valeurs d'objets larges orphelines crées pour des opérations en lot. Valeur toujours à 0 sur les index non ordonnés
lob_orphan_insert_count Nombre total de valeurs d'objets larges orphelines insérées durant les opérations en lot. Valeur toujours à 0 sur les index non ordonnés
row_overflow_fetch_in_pages Nombre total de pages de dépassement de page (row-overflow) retournées depuis une unité d'allocation ROW_OVERFLOW_DATA.
row_overflow_fetch_in_bytes Nombre total d'octets de dépassement de page (row-overflow) retournés depuis une unité d'allocation ROW_OVERFLOW_DATA.
column_value_push_off_row_count Nombre total de valeurs de colonne poussées dans une autre page, pour accommoder soit des valeurs LOB, soit des valeurs en dépassement de page (ROW OVERFLOW), durant une insertion ou une mise à jour
column_value_pull_in_row_count Nombre total de lignes réintégrées dans une page de données (unité d'allocation IN_ROW_DATA), depuis des pages de LOB et de dépassement (unité d'allocation LOB_DATA ou ROW_OVERFLOW_DATA), lorqu'une mise à jour de données a diminué la taille de la ligne
row_lock_count Nombre total de verrouillages de ganularité ligne demandés
row_lock_wait_count Nombre total d'attentes de libération d'un verrou de ligne
row_lock_wait_in_ms Temps total d'attente de libération de verrous de ligne, en millisecondes
page_lock_count Nombre total de verrouillages de ganularité page demandés
page_lock_wait_count Nombre total d'attentes de libération d'un verrou de page
page_lock_wait_in_ms Temps total d'attente de libération de verrous de page, en millisecondes
index_lock_promotion_attempt_count Nombre total de tentative d'escalades de verrous
index_lock_promotion_count Nombre total d'escalades de verrous réellement effectués
page_latch_wait_count Nombre total d'attentes de libération d'un latch (latch contention)
page_latch_wait_in_ms Temps total d'attente de libération de latch, en millisecondes
page_io_latch_wait_count Nombre total d'attentes de libération d'un latch d'entrée/sortie de page
page_io_latch_wait_in_ms Temps total d'attente de libération de latch d'entrée/sortie de page, en millisecondes


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 :

 
Sélectionnez

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

IV. Index manquants

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 :

 
Sélectionnez

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 :

 
Sélectionnez

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

V. Conclusion

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.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2007 Rudi Bruchez. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.