180
Chapitre 6. Utilisation des index
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 nonclustered 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 dits orphelins (orphan
LOB).
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 nœud feuille],
leaf_allocation_count as [page splits sur nœud feuille],
nonleaf_insert_count + nonleaf_update_count +
nonleaf_delete_count as [écritures sur nœuds intermédiaires],
nonleaf_allocation_count
as [page splits sur nœuds 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
6.2.2 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ées dans un espace de
cache, et peuvent être requêtées à travers trois vues système, qui offrent toutes les
informations nécessaires pour la création de l’index : colonnes qui devraient composer la clé, colonnes à inclure dans le nœud 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 certaines 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.
Chapitre 6. Utilisation des index
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 nonclustered 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 dits orphelins (orphan
LOB).
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 nœud feuille],
leaf_allocation_count as [page splits sur nœud feuille],
nonleaf_insert_count + nonleaf_update_count +
nonleaf_delete_count as [écritures sur nœuds intermédiaires],
nonleaf_allocation_count
as [page splits sur nœuds 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
6.2.2 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ées dans un espace de
cache, et peuvent être requêtées à travers trois vues système, qui offrent toutes les
informations nécessaires pour la création de l’index : colonnes qui devraient composer la clé, colonnes à inclure dans le nœud 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 certaines 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.