SQL – Etienne ZINZINDOHOUE

Un article de zinzineti    5 Commentaires

S’il est vrai que les index (les bons) participent à l’amélioration des performances d’une base de données,il ne faut pas perdre de vue que sa maintenance à un coût et donc pénalisant pour la performance. Alors comment trouver, au profit de la performance, le juste milieu ?. Voici une série de requêtes qui permettent de :
–> Afficher le nombre d’index manquants par base sur une instance
–> Afficher les index manquants et leur bénéfice
–> Afficher le coût des index non utilisés pour décider de leur suppression ou pas.
–> Mettre en perspective les index utilisés et leur coût de maintenance

/*========================================================================================================================
– Titre : Nombre d’index manquants par base de données
— Desciption : Afficher le nombre d’indexes manquants par bases de données
— Auteur : Etienne ZINZINDOHOUE
========================================================================================================================*/

SELECT  
    DB_NAME(database_id) AS [Base]
    ,count(*) AS [Nombre Indexes Manquants]  
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY [Nombre Indexes Manquants] DESC;

/*========================================================================================================================
– Titre : Quel est le bénéfice d’un index manquant ?
— Desciption : Afficher les 20 premiers index manquants et leurs bénéfices
— Auteur : Etienne ZINZINDOHOUE
=========================================================================================================================*/

SELECT TOP 20  
ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)*0.01,1) AS [Bénéfice Cumulé (%)] --Bénéfice cumulé pour les requêtes utilisateurs
,avg_user_impact AS [Bénéfice Moyen (%)] --Bénéfice moyen (en pourcentage) dont les requêtes utilisateur pourraient tirer parti si ce groupe d'index manquants était implémenté. Cela signifie que le coût des requêtes diminuerait, en moyenne, de la valeur de ce pourcentage si ce groupe d'index manquants était implémenté.
, [statement] AS [Table]  
, equality_columns AS [ListeColonnesEgalite] -- Liste de colonnes, séparées par des virgules, qui contribuent aux prédicats d'égalité au format :table.column = constant_value  
, inequality_columns AS [ListeColonnesInegalite] -- Liste de colonnes, séparées par des virgules, qui contribuent aux prédicats d'inégalité, par exemple, les prédicats au format : table.column > constant_value  
, included_columns AS [ListeColonnesCouverture] -- Liste de colonnes, séparées par des virgules, requises comme colonnes de couverture pour la requête  
FROM sys.dm_db_missing_index_groups g  
INNER JOIN sys.dm_db_missing_index_group_stats s  
  ON s.group_handle = g.index_group_handle  
INNER JOIN sys.dm_db_missing_index_details d  
  ON d.index_handle = g.index_handle  
ORDER BY [Bénéfice Cumulé (%)] DESC;

/*========================================================================================================================
-- Titre : Coût des index non utilisés
— Description : Afficher les 20 premiers index non utilisés et leur coût de maintenance. ces index non utilisés faut-il les supprimés ?
— Auteur : Etienne ZINZINDOHOUE
========================================================================================================================*/

--Les variables  
DECLARE @Nom_BaseEnCoursTraitement VARCHAR(50),@ID_BaseEnCoursTraitement VARCHAR(50), @SQL VARCHAR (max), @DEL VARCHAR (max)
--Créer et alimenter la table #LISTE_BASES avec le nom des bases (sauf les bases systèmes) de l'instance
SELECT database_id,name
INTO #LISTE_BASES  
FROM sys.databases
WHERE name not in ('master','model','msdb','temp')
ORDER BY [database_id]
--Créer la table #TempIndexesInutilises des indexes non utilisés
SELECT TOP 1
         DB_NAME() AS [Base]
        ,OBJECT_NAME(s.[object_id]) AS [Table]
        ,i.name AS [Index]
        ,user_updates    AS [UserRequetesUpdates]
        ,system_updates    AS [SystemRequetesUpdates]
INTO #TempIndexesInutilises
FROM   sys.dm_db_index_usage_stats s  
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]  
    AND s.index_id = i.index_id  
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND    user_seeks = 0
    AND user_scans = 0  
    AND user_lookups = 0
    AND s.[object_id] = - 333  -- Numéro bidon, c'est juste pour recupérer la structure de la table.
;
-- Tant qu'il y a des bases à traitées  
WHILE EXISTS (SELECT database_id FROM #LISTE_BASES )  
BEGIN
--Se positionner sur la 1ère ligne de la table temporaire
SET @ID_BaseEnCoursTraitement  = CAST((SELECT TOP 1 database_id FROM #LISTE_BASES)AS int)  
SET @Nom_BaseEnCoursTraitement  = (SELECT TOP 1 [name] FROM #LISTE_BASES)
-- Insert les infos de la base en cours de traitement dans la table de sortie
SET @SQL = N'USE '+ @Nom_BaseEnCoursTraitement + '; INSERT INTO #TempIndexesInutilises  
SELECT TOP 20    
DB_NAME()
,OBJECT_NAME(s.[object_id])  
,i.name  
,user_updates -- Nombre de mises à jour par requête utilisateur
,system_updates -- Nombre de mises à jour par requête système  
FROM  sys.dm_db_index_usage_stats s  
    INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] AND s.index_id = i.index_id  
WHERE   s.database_id = ' + @ID_BaseEnCoursTraitement +'  
   AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 -- objet créé lors de l''installation de SQL Server -- 0 = FAUX et 1 = Vrai
   AND s.user_seeks = 0  -- Nombre de recherches résultant de requêtes utilisateur pour lesquelles l''index recommandé du groupe pourrait avoir été utilisé
   AND s.user_scans = 0  -- Nombre d''analyses résultant de requêtes utilisateur pour lesquelles l''index recommandé du groupe pourrait avoir été utilisé
   AND s.user_lookups = 0 -- Nombre de recherches de signets par les requêtes utilisateur
   AND i.name IS NOT NULL    
ORDER BY user_updates DESC ;'
EXEC (@SQL);  
-- Supprimer la base traitée de la table temporaire
SET @DEL = 'DELETE FROM #LISTE_BASES WHERE database_id = '+ @ID_BaseEnCoursTraitement +';'
EXEC (@DEL)
END
-- Afficher les résultats  
SELECT TOP 20 [Base]
,[Table]
,[Index]
,[UserRequetesUpdates] AS [MAJ index par requête utilisateur]
,[SystemRequetesUpdates] AS [MAJ index par requête système]  
FROM #TempIndexesInutilises ORDER BY [UserRequetesUpdates] DESC
--Supprimer les tables temporaires
DROP TABLE #LISTE_BASES
DROP TABLE #TempIndexesInutilises

Remarques : /!\ Pas de précipitation ! Avant de supprimer un index qui n’est pas utilisé, il faut d’abord bien se renseigné pour savoir à quel moment cet index pourrait être utile. Attention donc !

/*========================================================================================================================
– Titre : Mise en perspective de l’utilisation d’un index et son coût de maintenance
— Description : S’il est vrai que les index (les bons) participent à l’amélioration des performances d’une base de donnée il ne faut pas perdre de vue que leurs maintenances a un coût et donc peuvent pénaliser les performances. Alors comment trouver, au profit de la performance, le juste milieu ?
— Auteur : Etienne ZINZINDOHOUE
========================================================================================================================*/

DECLARE @Nom_BaseEnCoursTraitement VARCHAR(50),@ID_BaseEnCoursTraitement VARCHAR(50) , @SQL VARCHAR (max), @DEL VARCHAR (max)
--Créer la table #LISTE_BASES liste des bases
SELECT database_id,name
-- Alimenter la table liste des bases #LISTE_BASES
INTO #LISTE_BASES  
FROM sys.databases
WHERE name not in ('master','model','msdb','temp')
ORDER BY [database_id]
--Créer la table #TempIndexesUtilises des indexes non utilisés
SELECT TOP 1
(user_updates + system_updates) AS [Coût Maintenance]  
,(user_seeks + user_scans + user_lookups) AS [Fréquence Utilisation]
,DB_NAME() AS [Base]
,OBJECT_NAME(s.[object_id]) AS [Table]
,i.name AS [Index]
INTO #TempIndexesUtilises
FROM   sys.dm_db_index_usage_stats s  
  INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] AND s.index_id = i.index_id  
WHERE  s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMSShipped') = 0
AND (user_updates + system_updates) > 0  -- (Nombre de mises à jour par requête utilisateur + Nombre de mises à jour par requête système) >0
AND s.[object_id] = - 333  -- Juste pour recuperer la structure de la table.
;
-- Tant qu'il y a des bases à traitées  
WHILE EXISTS (SELECT database_id FROM #LISTE_BASES )  
BEGIN
--Se positionner sur la 1ère ligne de la table temporaire
SET @ID_BaseEnCoursTraitement  = CAST((SELECT TOP 1 database_id FROM #LISTE_BASES)AS int)  
SET @Nom_BaseEnCoursTraitement  = (SELECT TOP 1 [name] FROM #LISTE_BASES)
-- Insert les infos de la table en cours de traitement dans la table de sortie
SET @SQL = N'USE '+ @Nom_BaseEnCoursTraitement + '; INSERT INTO #TempIndexesUtilises  
SELECT TOP 20  
(user_updates + system_updates) AS [Coût Maintenance] -- (Nombre de mises à jour par requête utilisateur + Nombre de mises à jour par requête système) >0
,(user_seeks + user_scans + user_lookups) AS [Fréquence Utilisation]-- Nombre de recherches résultant de :  requêtes utilisateurs,requêtes d''analyses et de signets
,DB_NAME() AS [Base]
,OBJECT_NAME(s.[object_id]) AS [Table]
,i.name AS [Index]
FROM   sys.dm_db_index_usage_stats s  
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] AND s.index_id = i.index_id  
WHERE   s.database_id = ' + @ID_BaseEnCoursTraitement +'  
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 -- objet créé lors de l''installation de SQL Server -- 0 = FAUX et 1 = Vrai
AND (user_updates + system_updates) > 0  -- (Nombre de mises à jour par requête utilisateur + Nombre de mises à jour par requête système) >0
ORDER BY user_updates DESC ;'
 
EXEC (@SQL);  
-- Supprimer la base traitée de la table temporaire
SET @DEL = 'DELETE FROM #LISTE_BASES WHERE database_id = '+ @ID_BaseEnCoursTraitement +';'
EXEC (@DEL)
END
-- Mettre en perspective le coût de la maintenance et l'utilité  
SELECT TOP 20  DENSE_RANK() OVER(ORDER BY [Coût Maintenance]DESC) AS [Classement Coût Maintenance]
,DENSE_RANK() OVER(ORDER BY [Fréquence Utilisation] DESC) AS [Classement Fréquence Utilisation]
,[Coût Maintenance]
,[Fréquence Utilisation],[Base],[Table],[Index]
FROM  #TempIndexesUtilises  
ORDER BY [Coût Maintenance] DESC
--Supprimer les tables temporaires
DROP TABLE #LISTE_BASES
DROP TABLE #TempIndexesUtilises

Maintenance des index
===============================================

Pour la maintenance des index SQLPro a fait un travail génial à ce sujet

Merci à SQLPro