[xQuery] récupérations des index manquants via le plan d'exécution XML

Bonjour,

Mon but est de faire un script permettant de générer les commandes de création des index manquants à partir des plans d’exécutions présent dans le cache.
Je souhaite donc récupérer les différentes informations utiles à la création d'un index dans le fichier xml du plan d’exécution.

Exemple :

A partir du xml suivant :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

... <MissingIndexes> <MissingIndexGroup Impact="99.8122"> <MissingIndex Database="[msdb]" Schema="[dbo]" Table="[sysssislog]"> <ColumnGroup Usage="EQUALITY"> <Column Name="[executionid]" ColumnId="7" /> </ColumnGroup> <ColumnGroup Usage="INCLUDE"> <Column Name="[event]" ColumnId="2" /> <Column Name="[endtime]" ColumnId="9" /> <Column Name="[message]" ColumnId="12" /> </ColumnGroup> </MissingIndex> </MissingIndexGroup> </MissingIndexes> ...

---

J'aimerais obtenir un tableau du genre :

Impacte	Base	Schéma	Table	Colonnes d'égalité/d'inégalité	Colonnes Incluse

Ou bien

Impacte	Base	Schéma	Table	Colonne	Type de colonne (Incluse, d'égalité,d'inégalité)

Je me fou d'avoir une ligne par colonne, cela sera retraiter plus tard :)

N'étant pas à l'aise avec cette méthode, en m'appuyant sur différents articles j'ai écrit ce bout de code qui me permet de récupérer les premières informations :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT Top 10 mi.value(N'(MissingIndexGroup/@Impact)[1]', N'varchar(128)') AS "impacte" ,mi.value(N'(MissingIndexGroup/MissingIndex/@Database)[1]', N'varchar(128)') AS "base" ,mi.value(N'(MissingIndexGroup/MissingIndex/@Schema)[1]', N'varchar(128)') As "schéma" ,mi.value(N'(MissingIndexGroup/MissingIndex/@Table)[1]', N'varchar(128)') As "table" ,mi.query('MissingIndexGroup/MissingIndex/ColumnGroup') As "Colonnes" FROM sys.dm_exec_cached_plans As cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) As qp CROSS APPLY qp.query_plan.nodes(N'//MissingIndexes') As MissingIndex(mi)

---

Ce qui me permet d'obtenir l'impacte, la base, le schéma, et la table de manière précise ainsi qu'un nouveau xml avec la liste des colonnes :

Code:

---

1 2 3 4 5 6 7 8 9

<p1:ColumnGroup xmlns:p1="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Usage="EQUALITY"> <p1:Column Name="[executionid]" ColumnId="7" /> </p1:ColumnGroup> <p2:ColumnGroup xmlns:p2="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Usage="INCLUDE"> <p2:Column Name="[event]" ColumnId="2" /> <p2:Column Name="[endtime]" ColumnId="9" /> <p2:Column Name="[message]" ColumnId="12" /> </p2:ColumnGroup>

---

Mais je ne parviens pas à extraire le détail de ces colonnes ... :(

Quelqu'un aurait-il pitié de moi svp ?

Bonjour,

Vous pouvez passer directement par les vues de management :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT ddmigs.avg_user_impact AS Impacte , DB_NAME(database_id) AS Base , PARSENAME(statement, 2) AS [schema] , PARSENAME(statement, 1) AS [Table] , ddmid.equality_columns , ddmid.inequality_columns , ddmid.included_columns FROM sys.dm_db_missing_index_details AS ddmid INNER JOIN sys.dm_db_missing_index_groups AS ddmig ON ddmid.index_handle = ddmig.index_handle INNER JOIN sys.dm_db_missing_index_group_stats AS ddmigs ON ddmigs.group_handle = ddmig.index_group_handle

---

Ou bien

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT ddmigs.avg_user_impact AS Impacte , DB_NAME(database_id) AS Base , PARSENAME(statement, 2) AS [schema] , PARSENAME(statement, 1) AS [Table] , ddmic.column_name , ddmic.column_usage FROM sys.dm_db_missing_index_details AS ddmid INNER JOIN sys.dm_db_missing_index_groups AS ddmig ON ddmid.index_handle = ddmig.index_handle INNER JOIN sys.dm_db_missing_index_group_stats AS ddmigs ON ddmigs.group_handle = ddmig.index_group_handle CROSS APPLY sys.dm_db_missing_index_columns(ddmid.index_handle) AS ddmic

---

Merci pour cette réponse.
Il me semble cependant que les résultats obtenues par les vues dms et par le xml du plan d’exécution ne sont pas forcément les mêmes, d'où la volonté de passer par le plan d’exécution.

Ce sont exactement les mêmes.... Cependant du fait des effets de cache vous pouvez avoir plus d'informations dans l'un ou l'autre en fonction de la pression mémoire et du vieillissement des données en cache.

Utiliser une requête XQuery sur un serveur en prod pour récupérer ces informations des plans de requêtes est assez stupide car cela va générer une charge CPU très importante au détriment de la production, alors qu'interroger les DMV qui sont faites pour cela est très léger, nettement plus simples et ultra rapide...

Formez vous à SQL Server... Notre livre peut vous y aider :
Pièce jointe 389906

A +

L'occupation CPU n'est pas un problème au vu de l'infrastructure (on tourne en moyenne autour de 10% la journée). Cependant nous avons abandonné notre idée de départ pour partir effectivement sur l'utilisation des dmvs avec une capture plus régulière.

J'ai survolé votre livre il y a quelques mois et je l'ai trouvé super au point de le faire acheter par la société, mais je n'ai malheureusement pas eu le temps d’approfondir.
Pensez-vous en ressortir un autre sur les versions futures ?

Citation:

---

Envoyé par Nico.Bizz

J'ai survolé votre livre il y a quelques mois et je l'ai trouvé super au point de le faire acheter par la société, mais je n'ai malheureusement pas eu le temps d’approfondir.
Pensez-vous en ressortir un autre sur les versions futures ?

---

Hélas non, les éditeurs ne gagent plus d'argent avec des ivres techniques du fait de la concurrence d'Internet…

A +