SQL Server: New Tools to Diagnose Index Health

Article
08/24/2016

SQL Server

Nouveaux Outils pour diagnostiquer l’intégrité de l’index

Randy Dyess

Vue d'ensemble:

Fragmentation de l’index
Usage de l’index
Activité opérationnelle de l’index

Télécharger le code de cet article: DyessSQLIndex2007_03.exe (151KB)

La mise au point des index de la base de données est l’un des éléments principaux de toute liste de vérification des réglages de la performance SQL Server. La capacité de l’optimiseur de requête SQL Server à utiliser correctement les index pendant l’exécution d’une requête dépend non seulement de la création efficace d’index mais

aussi de leur intégrité. Une série de vues et de fonctions de gestion dynamique (DMV, DMF), introduites dans le SQL Server™ 2005, peut aider les administrateurs de base de données à déterminer l’efficacité de leurs index et découvrir tout problème de performance.

Les DMV et DMF vous permettent d’examiner un serveur puis de renvoyer des informations à partir de l’état du serveur pour vous aider à surveiller l’intégrité et la performance de l’instance du serveur et de diagnostiquer des problèmes. Les administrateurs de base de données familiarisés avec les versions précédentes de SQL Server remarqueront que ces DMV et DMF les remplaceront grâce à l’utilisation de commandes DBCC, l’exécution de certaines procédures système stockées, l’interrogation de plusieurs tables système et la capture d’événements à l’aide du générateur de profils SQL.

Trois fonctions et vues clés à savoir : sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats et sys.dm_db_index_operational_stats sont disponibles pour vous aider à savoir si vos index fonctionnent comme prévu. Ils vous permettent de consulter les modèles de verrouillage et d’E/S de vos index, et de savoir s’ils sont utilisés par l’optimiseur de requête de façon à ne pas induire de contention inutile dans la base de données.

Fragmentation de l’index

Le DMF sys.dm_db_index_physical_stats a été créé pour remplacer DBCC SHOWCONTIG et pour afficher une fragmentation de l’index. Cependant, contrairement à DBCC SHOWCONTIG, qui place un verrou partagé (S) sur la table contenant l’index, sys.dm_db_index_physical_stats place seulement une intention de verrou partagé (IS), ce qui réduit largement le risque de blocage de la table pendant l’exécution de la fonction.

Pour définir la fragmentation d’un index en utilisant sys.dm_db_index_physical_stats, vous examinerez une combinaison de trois colonnes dans le résultat de la fonction. La fragmentation logique d’index (fragmentation d’étendue de tas) peut être déterminée en observant la valeur renvoyée dans la colonne avg_fragmentation_in_percent. La fragmentation logique correspond au pourcentage de pages non ordonnées au niveau feuille d’un index, tandis qu’une fragmentation de l’étendue est le pourcentage d’étendues non ordonnées au niveau feuille d’un index. La fragmentation logique et de l’étendue peut affecter la performance d’un index en nécessitant des E/S et un mouvement de la tête du disque supplémentaires, la tête de disque devant sauter pour pouvoir lire des pages dans l’ordre. Vous devez vous efforcer de garder la fragmentation logique et d’étendue aussi près de zéro que possible.

La fragmentation interne d’un index est le pourcentage de remplissage de pages. Il est certain que la page d’index doit être aussi pleine que possible, bien que vous deviez équilibrer remplissage et nombre d’insertions dans les pages d’index afin de garder le nombre de fractionnements de pages au minimum absolu.

L’argument avg_page_space_used_in_percent de sys.dm_db_index_physical_stats doit être examiné pour déterminer le remplissage des pages d’index. Pour s’assurer que le nombre est configuré proche de 100 pourcent, ajustez le taux de remplissage d’un index en observant le nombre de fractionnements de pages qui se produisent. À un moment donné, le nombre de fractionnements de pages augmentera de façon spectaculaire, indiquant que vous avez défini un taux de remplissage de l’index plus élevé que prévu. L’ajustement fillfactor (taux de remplissage) de l’index prend du temps et les tests ne doivent pas être conduits sans une bonne planification. (Pour les index n’ayant pas d’insertions aléatoires, il est possible de paramétrer les taux de remplissage à 100 sans se soucier de l’augmentation de fractionnements de page).

Pour déterminer les niveaux de fragmentation de tous les index dans la table AdventureWorks.HumanResources.Employee, vous pourriez utiliser l’instruction ci-après :

SELECT * 
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL 
-- NULL to view all indexes; 
-- otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') -- We want all information

Utilisez ce DMF pour déterminer automatiquement les index qui doivent être reconstruits, ceux qui doivent être réorganisés et ceux qui n’ont pas besoin de maintenance. L’examen des valeurs des colonnes avg_page_space_used_in_percent et avg_fragmentation_in_percent de ce DMF pour la fragmentation d’index (se trouvant hors d’une logique et d’un seuil de densité admis) peut vous aider à déterminer les opérations à exécuter sur l’index.

Selon l’état de vos index, l’exemple affiché à la figure 1 peut ne pas retourner de données dans votre copie de la base de données AdventureWorks, mais il peut être facilement adapté à d’autres bases de données.

Figure 1 Examen de la fragmentation et du remplissage de page

--Reorganize the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 10 
AND avg_fragmentation_in_percent < 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 75 
AND avg_page_space_used_in_percent > 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

Vous pourrez aisément stocker les résultats des requêtes dans une variable de table puis effectuer une boucle de la variable de table pour générer une chaîne dynamique pour l’instruction ALTER INDEX correcte (voir la figure 2).

Figure 2 Création d’une chaîne ALTER INDEX dynamique

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

--Table to hold results
DECLARE @tablevar TABLE(lngid INT IDENTITY(1,1), objectid INT,
index_id INT)

INSERT INTO @tablevar (objectid, index_id)
SELECT [object_id],index_id
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

SELECT 'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.'
+ OBJECT_NAME(objectid) + ' REBUILD' 
FROM @tablevar tv
INNER JOIN sys.indexes ind
ON tv.objectid = ind.[object_id]
AND tv.index_id = ind.index_id
INNER JOIN sys.objects ob
ON tv.objectid = ob.[object_id]
INNER JOIN sys.schemas sc
ON sc.schema_id = ob.schema_id

Usage de l’index

Tandis que sys.dm_db_index_physical_stats est un outil de remplacement puissant de DBCC SHOWCONTIG pouvant aider à révéler l’intégrité d’un index, vous serez souvent confrontés au problème plus complexe de la détermination des index utiles aux requêtes exécutées sur une table. Souvent les développeurs de base de données ou les administrateurs créent des index sur une table qui, selon eux, sera utilisée par l’optimiseur de requête pendant l’exécution d’une requête. Dans les versions précédentes de SQL Server, il était difficile de savoir si ces index étaient effectivement utilisés. Soit il fallait annuler l’index et voir si la performance des requêtes en était affectée soit capturer les projets d’exécution des requêtes et analyser l’usage de l’index.

sys.dm_db_index_usage_stats est une nouvelle vue de gestion dynamique qui facilite la compréhension de l’utilisation des index par l’optimiseur de requête et de l’exécution des requêtes sur une table. Cette vue peut être examinée pour déterminer l’utilité d’un index, vous permettant d’annuler tout index non utilisé par l’optimiseur de requête. Vous n’avez plus à vous soucier d’index qui gaspillent l’espace de stockage ou de la maintenance d’index inutilisés qui diminuent les performances de la base de données.

En examinant les résultats de cette vue de gestion dynamique (DMV) pour les index avec zéro recherche et analyse, vous pouvez déterminer si un index a été utilisé ou non depuis le dernier démarrage de SQL Server. Sachez toutefois qu’un grand nombre de DMV et DMF ne sont pas maintenues et se remettent à zéro une fois que SQL Server est relancé. Tenez en compte lorsque vous utilisez une DMV ou une DMF pour déterminer l’usage d’un index. L’index peut simplement ne pas être nécessaire depuis le dernier redémarrage du service mais vous en aurez besoin pour les requêtes de rapports de fin de semaine, de fin de mois ou trimestriels.

Pour afficher tous les index dans une instance qui n’a pas été utilisée depuis le dernier redémarrage du service SQL Server, vous pouvez utiliser l’instruction suivante :

SELECT DB_NAME(database_id),OBJECT_NAME([object_id])
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND system_seeks = 0
AND system_scans = 0
AND system_lookups = 0

Activité opérationnelle de l’index

La DMF sys.dm_db_index_operational_stats sera très utile pour vous aider à comprendre l’activité opérationnelle de vos index. Vous pouvez l’utiliser pour afficher les activités d’E/S, de verrouillage, de blocage et de méthode d’accès sur chaque index d’une base de données ; vous pouvez ainsi comprendre comment vos index sont utilisés et diagnostiquer les problèmes de verrouillage d’index dus à une activité d’E/S étendue ou à l’existence d’un « point sensible » dans l’index.

Utilisez le temps d’attente de verrouillage de cette DMF pour vous aider à établir le temps qu’il faut aux opérations LECTURE et ÉCRITURE pour acquérir l’accès aux ressources de l’index. Vous pourriez ainsi déterminer si le sous-système disque utilisé pour stocker l’index est suffisant pour l’activité d’E/S de l’index. Il peut également indiquer si la conception et l’usage de l’index n’ont pas introduit un point sensible tandis qu’une activité étendue dans une ou plusieurs pages d’un index peut provoquer une contention pour les données contenues dans ces pages. Une telle contention conduit souvent au verrouillage excessif d’opérations tentant de LIRE ou ÉCRIRE dans cette zone.

La figure 3 affiche la méthode de détermination des modèles d’E/S et de verrouillage de tous les index de la table AdventureWorks.HumanResources.Employee.

Figure 3 Détermination de modèles d’E/S et de verrouillage

SELECT page_latch_wait_count --page latch counts
,page_latch_wait_in_ms --page latch wait times
,row_lock_wait_in_ms --row lock wait times
,page_lock_wait_in_ms --page lock wait times
,row_lock_count --row lock counts
,page_lock_count --page lock counts
,page_io_latch_wait_count --I/O wait counts
,page_io_latch_wait_in_ms --I/O wait times
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
)

Pour en savoir plus

Les DMV et DMF présentés dans cet article peuvent faire l’objet d’utilisations supplémentaires. Prenez le temps de consulter les articles de la documentation en ligne de SQL Server décrivant les fonctions et vues indiquées dans la barre latérale « Ressources Supplémentaires » afin de comprendre la grande variété d’informations que vous pourrez extraire et examiner grâce à elles.

Pour en savoir plus sur les DMF et DMV d’index n’ayant pas été abordés dans cet article, consultes le blog (en anglais) publié par l’équipe d’optimisation de requête SQL Server à blogs.msdn.com/queryoptteam/570176.aspx.

Ressources supplémentaires

Randy Dyess est le mentor de Solid Quality Learning, où il est spécialiste des systèmes OLTP SQL Server. Randy est l’auteur d’un grande nombre de livres et d’articles au sujet de SQL Server. Il est le fondateur et l’auteur principal de www.TransactSQL.Com et www.Database-Security.Info