Discussion :

[Nico.Bizz]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

[aieeeuuuuu]

Bonjour,

Vous pouvez passer directement par les vues de management :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Nico.Bizz]

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.

[SQLpro]

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 :


A +

[Nico.Bizz]

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 ?

[SQLpro]

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 +