Article complet: Recherche d'indexes manquants sous SQL Server 2005

Une nouvelle fonctionnalité intéressante, introduite avec SQL Server 2005, est la recherche d’indexes manquants.
Elle permet, de façon très simple, de trouver les indexes manquants qui pourraient simplifier le travail
du moteur de base de données s’ils étaient posés sur des tables de base ou des vues indexées.
Néanmoins, cette fonctionnalité comporte quelques limitations, qui doivent être prise en compte avant qu’on
ait décidé de créer l’index conseillé par SQL Server.
Comme vous le verrez, plusieurs sujets sont connexes à cet article, mais nous ne les aborderons pas ici.
Ils seront l’objet de prochains articles.

[Suite:]

	1. Présentation

Le moteur de base de données comprend, en outre, un optimiseur de requêtes : c’est un des modules pilier d'un SGBD.
Celui-ci est chargé de trouver le meilleur moyen d’exécuter une requête en un minimum de temps et en consommant un minimum de ressources.
Il génère pour cela, à l’enregistrement d’une nouvelle procédure stockée par exemple, un plan de requête qu’il conserve dans
le cache de procédures. Ce plan de requête est généré en fonction des statistiques de colonnes.
Au fur et à mesure que la base de données vit, que les requêtes évoluent, il est possible que les indexes posés il y a quelques temps ne soient plus optimaux.
Dans un tel cas, le moteur de base de données génère un « mauvais » plan de requête.
Cette nouvelle fonctionnalité introduite par SQL Server permet de conserver ces « mauvaises » informations pour nous
faciliter la complexe tâche du paramétrage de base de données.
Il nous reste donc à décider de les implémenter ou non.

	2. Vues de gestion dynamiques impliquées dans la recherche d’indexes manquants

SQL Server 2005 met à notre disposition quatre vues de gestion dynamique.
Nous utiliserons trois de ces vues pour nous aider à la recherche d’indexes manquants :

	- sys.dm_db_missing_index_group_stats:

	Cette vue nous renseignera sur le gain de performances que l’on pourrait obtenir si nous implémentions l’index conseillé.
	Les colonnes qui nous intéressent sont :

		• user_seeks : Nombre de recherches pour lesquelles des requêtes auraient pu tirer profit de l’index 	recommandé.

		• user_scans : Nombre d’analyses d’index pour lesquelles des requêtes auraient pu tirer profit de l’index recommandé.

		• unique_compiles : Nombre de (re)compilations qui auraient pu tirer profit de l’index recommandé. Une recompilation est très coûteuse.

		• avg_total_user_cost : C’est ce que coûte, en moyenne, au moteur de base de données, d’exécuter une requête sans l’index recommandé.

		• avg_user_impact : C’est le gain, en pourcentage, que le moteur de base de données estime pouvoir gagner sur le coût de la requête
		(avg_total_user_cost).

		• last_user_seek : de type DATETIME, elle renseigne sur la date de la dernière occurrence de recherche qui aurait pu profiter
		de l’index conseillé.

		• last_user_scan : même commentaire que pour last_user_seek, excepté qu’il s’agit de la dernière recherche.

	- sys.dm_db_missing_index_groups: 

	Cette vue nous servira à faire le lien entre la vue précédente, qui nous renseigne sur les gains de performance possibles,
	et la vue suivante, qui va nous permettre de créer la commande CREATE INDEX …

	- sys.dm_db_missing_index_details: 

	Cette vue nous éclairera sur les colonnes des tables à indexer. Les colonnes qui nous intéressent sont :

		• equality_columns : liste des colonnes qui constituent un prédicat d’égalité
		 dans l’expression de la requête (WHERE maColonne = maValeur, JOIN maTableB ON maTableA.IDA = maTableB.IDB).

		• inequality_columns : liste des colonnes qui constituent un prédicat d’inégalité
		 (WHERE maColonne < maValeur, JOIN maTableB ON maTableA.IDA > maTableB.IDB).

		• included_columns : liste de colonnes qui permettront de couvrir la requête.

		• statement : nom qualifié de la table sur laquelle l’index doit être posé.

La quatrième vue de gestion dynamique est sys.dm_db_missing_index_columns.
Elle permet d’associer les colonnes de l’index conseillé par sys.dm_db_missing_index_groups et de montrer « en colonne » dans quelle clause de la commande de création des indexes la colonne en question doit se trouver.
C’est ce que propose sys.dm_db_missing_index_details par une liste de colonnes de table, séparées par des virgules.

	3. Une première approche

Relions donc toutes ces informations avec les jointures appropriées :

 
------------------------------ 
- Nicolas SOUQUET - 08/09/2008 
------------------------------ 
 
  SELECT MIGS.unique_compiles Comp, 
      MIGS.user_seeks, 
      MIGS.user_scans, 
      MIGS.avg_total_user_cost, 
      MIGS.avg_user_impact, 
      MIGS.last_user_seek, 
      MIGS.last_user_scan, 
      REPLACE(REPLACE(MID.Statement, '[', ''), ']', '') 'Database.Schema.Table', 
      REPLACE(REPLACE(MID.equality_columns, '[', ''), ']', '') Egalite, 
      REPLACE(REPLACE(MID.inequality_columns, '[', ''), ']', '') Inegalite, 
      REPLACE(REPLACE(MID.included_columns, '[', ''), ']', '') Incluse 
  FROM sys.dm_db_missing_index_group_stats MIGS 
  JOIN sys.dm_db_missing_index_groups MIG ON MIGS.GROUP_HANDLE = MIG.index_group_handle 
  JOIN sys.dm_db_missing_index_details MID ON MIG.INDEX_HANDLE = MID.index_handle 
  WHERE DB_NAME(MID.database_id) NOT IN ('master', 'msdb', 'model', 'ReportServer', 'ReportServerTempDB', 'Distribution', 'TempDB')

Nous avons maintenant toutes les informations pour créer les indexes qui manquent à notre base de données. Mais dans cette foule d’informations, lesquels sont vraiment nécessaires ?
J’ai le souvenir que cette requête m’ait parfois retourné plus de 300 lignes …
Pour trouver dans un premier temps les indexes les plus nécessaires à une base de données pauvrement indexée, il faudrait associer le coût de chaque requête au nombre de recherches et d’analyses d’indexes,
et de (re)compilations de plans de requête, et tenir compte de la « fraîcheur » des données retournées par ces vues de gestion dynamique.

Soit donc la requête suivante :

 
------------------------------ 
- Nicolas SOUQUET - 08/09/2008 
------------------------------ 
 
  SELECT ROUND((MIGS.user_seeks + MIGS.user_scans + MIGS.unique_compiles) * MIGS.avg_total_user_cost * MIGS.avg_user_impact, 0) Avantage, 
      MIGS.last_user_seek, 
      MIGS.last_user_scan, 
      REPLACE(REPLACE(MID.Statement, '[', ''), ']', '') 'Database.Schema.Table', 
      REPLACE(REPLACE(MID.equality_columns, '[', ''), ']', '') Egalite, 
      REPLACE(REPLACE(MID.inequality_columns, '[', ''), ']', '') Inegalite, 
      REPLACE(REPLACE(MID.included_columns, '[', ''), ']', '') Incluse 
  FROM sys.dm_db_missing_index_group_stats MIGS 
  JOIN sys.dm_db_missing_index_groups MIG ON MIGS.GROUP_HANDLE = MIG.index_group_handle 
  JOIN sys.dm_db_missing_index_details MID ON MIG.INDEX_HANDLE = MID.index_handle 
  WHERE CONVERT(CHAR(10), MIGS.LAST_USER_SEEK, 103) = CONVERT(CHAR(10), GETDATE(), 103) 
  AND DB_NAME(MID.database_id) NOT IN ('master', 'msdb', 'model', 'ReportServer', 'ReportServerTempDB', 'Distribution', 'TempDB') 
  ORDER BY Avantage DESC

Maintenant, nous avons les indexes manquants qui ont coûté le plus cher à notre serveur, et qui auraient même été nécessaires aujourd’hui.
Si l’on ré-exécute cette requête quelques secondes après l’avoir exécutée une première fois sur une base de données OLTP,
ou sur un serveur OLAP très utilisé, et que l’on constate que les premières lignes (c'est-à-dire celles qui nous renseignent
sur les indexes les plus nécessaires) ont un «avantage » bien supérieur à celui de la première exécution, c’est très
certainement que si votre serveur était doué de parole, il vous remercierait de l’avoir créé.
Mais alors, comment créer ces indexes ?

	4.Création des indexes nécessaires

Vous aurez certainement dénoté la simplicité avec laquelle SQL Server 2005 nous expose les indexes qu’il serait nécessaire de créer. Prenons la syntaxe de la commande CREATE INDEX, sans ses options :

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name  
  ON <object> ( column [ ASC | DESC ] [ ,...n ] )  
  [ INCLUDE ( column_name [ ,...n ] ) ]

En supposant que nous avons au moins l’index de clé primaire, cluster, sur les tables montrées par les premières lignes de la requête précédente, nous créerons simplement notre commande en remplaçant :

- column par les colonnes de table listées dans les colonnes Egalite et Inegalite de la précédente requête,
- column_name dans la clause INCLUDE par les colonnes de table listées dans la colonne Incluse de la précédente requête

Il y a une dernière petite subtilité : nous devons placer les colonnes d’égalité avant celles d’inégalité dans la clause ON, mais également veiller à ordonner les colonnes d’égalité dans l’ordre décroissant de leur sélectivité.

En effet, l’optimiseur de requête de SQL Server 2005 (et d’autres SGBD) est basé sur les coûts, c'est-à-dire la quantité de ressources qui vont être nécessaires à l’exécution d’une requête.
Il se base sur la sélectivité des colonnes, c'est-à-dire le nombre de valeurs différentes que contiennent chacune des colonnes de nos tables, pour estimer ce coût.
Ces statistiques sont conservées soit au niveau colonne (node « Statistiques » des tables dans SQL Server Management Studio), soit dans les indexes.
Par opposition à une colonne de type BIT qui est très peu sélective, une colonne auto-incrémentée est très sélective.

Pour rechercher les données qui sont la réponse à notre requête, l’optimiseur de requêtes va commencer par rechercher nos
critères (clause WHERE de la commande SELECT, par exemple) dans les colonnes ayant la sélectivité la plus élevée, de sorte
qu’il aura ensuite un jeu de données le plus petit possible pour vérifier les autres contraintes de la requête.
Nous veillerons donc à vérifier l’ordre dans lequel nous allons ordonner nos colonnes d’ « égalité » : les vues de gestion dynamique les retournent souvent dans le bon ordre, mais une requête :

SELECT COUNT(DISTINCT maColonne) FROM maTable

nous ôtera les doutes.

Comme le moteur de bases de données ne peut pas maintenir des statistiques sur des données qui ne sont pas
contenues dans les colonnes, il découle que les colonnes d’inégalité révélées par notre dernière requête devront se
trouver à la suite des colonnes répertoriées comme colonnes d’ « égalité ».

Qu’en est-il des colonnes répertoriées « Incluse » ? Ces colonnes, qui sont répertoriées dans le SELECT de nos requêtes,
à l’exception des colonnes participant à un calcul agrégé (SUM, MIN, MAX, …) permettent de couvrir nos requêtes en évitant
les bookmarks lookups, eux-aussi très coûteux.
Les colonnes incluses sont également une nouvelle fonctionnalité introduite avec SQL Server 2005.

Finalement, la trame de notre requête d’index sera :

CREATE INDEX IX_maTable_mesColonnes 
ON monSchema.maTable (mesColonnesEgalite, mesColonnesInegalite) 
INCLUDE (mesColonnesIncluse) 
WITH (mesOptions)

	5.Limitations de la fonctionnalité d’indexes manquants

Cette fonctionnalité a cependant quelques revers : 

	- Comme nous l’avons vu, elle retourne une liste de colonnes mais ne précise pas dans quel ordre elles doivent constituer la clé de l’index,

	- Elle ne constitue pas une aide à la configuration d’un index (options FILLFACTOR et PAD_INDEX),

	- Dans certains cas, elle ne renseigne que sur les colonnes incluses. Il faudra donc chercher quelle(s) est la
	 requête à l’origine  de ce groupe d’index manquant, puis optimiser la requête en conséquence

	- Elle ne renseigne pas sur la « fraîcheur » des données : la requête que je vous propose peut tout à fait montrer un
	 « avantage » énorme, qui a été généré par une requête que nous avons modifiée il y a quelques jours. 

	En effet, ces informations sont purgées :
		• Lors du redémarrage du serveur ou du service SQL Server,
		• Lors de la création d’un index sur une table pour laquelle la fonctionnalité nous conseille la création de plusieurs indexes.

	6.Conclusion

Comme nous venons de le voir, cette fonctionnalité permet d’indexer très rapidement une base de données qui ne l’est que très pauvrement, mais elle permet aussi de suivre la vie de la base de données.

Même si cette fonctionnalité ne constitue pas un outil précis de paramétrage de base de données (en existe-t-il ?), elle nous aidera à vérifier, en plus des options d’instruction (SET STATISTICS)
et du plan de requête (accessible par CTRL+L sous SQL Server Management Studio) si, lorsque l’on doit optimiser une requête, on doit se tourner vers la création d’un index approprié,
ou bien vers une autre expression de la même requête (parfois ce sera les deux)

ElSuket