Da ich beruflich öfters mit Auswertungen im Performance-Bereich des SQL Servers zu tun habe, bin ich auch naturgemäß mit den DMV für die fehlenden Indexe ( sys.dm_db_missing_index...) in Berührung gekommen. Was mir immer dabei gefehlt hat, war ein Link zu den dazugehörigen Ausführungsplänen.
Ich empfinde diese Information als wesentlich um die Situation zu verstehen, warum das SQL System genau diesen Vorschlag gemacht hat.
Das folgende Skript habe ich mir geschrieben, um mir diese fehlende Information zukommen zu lassen. Dabei frage ich nur die gespeicherten Ausführungspläne ab und extrahiere die gewünschten Informationen aus den XML-Dateien.
-- Missing Index with Execution Plan
-- Created by Maic Beher
-- Version 1.0
-- Date: 19.10.2016
With xmlnamespaces (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), Missing_Index_CTE (Usecounts,Index_Desription,ExecutionPlan)
as
(
select
usecounts,
MissingIndex.query('<MissingIndexes> { for $MissingIndexes in * return $MissingIndexes } </MissingIndexes>'),
MissingIndex.query('//ShowPlanXML')
from sys.dm_exec_cached_plans as cp
cross apply sys.dm_exec_query_plan (cp.plan_handle) as qp
cross apply query_plan.nodes('//MissingIndexes') as t(MissingIndex)
where
query_plan is not null
and
MissingIndex.exist('//MissingIndex') = 1
)
,
Missing_Index_2_CTE (ExecutionPlan,Index_Desription,Usecounts)
as
(
select
ExecutionPlan,
c.query('.'),
Usecounts
from Missing_Index_CTE
cross apply Index_Desription.nodes ('//MissingIndexes') as t(c)
)
,
Missing_Index_3_CTE (DBName, SchemaName, TableName, UseCounts, Impact, Equality, Inequality, Includes, ExecutionPlan)
as
(
select
c1.value('(./MissingIndex/@Database)[1]', 'sysname') As [DB Name],
c1.value('(./MissingIndex/@Schema)[1]', 'sysname') As [Schema Name],
c1.value('(./MissingIndex/@Table)[1]', 'sysname') As [Table Name],
Usecounts,
c1.value('./@Impact[1]', 'float') As [Impact],
c1.query('./MissingIndex/ColumnGroup[@Usage="EQUALITY"]') As [EQUALITY],
c1.query('./MissingIndex/ColumnGroup[@Usage="INEQUALITY"]') As [INEQUALITY],
c1.query('./MissingIndex/ColumnGroup[@Usage="INCLUDE"]') As [INCLUDE],
ExecutionPlan
from Missing_Index_2_CTE
cross apply Index_Desription.nodes ('//MissingIndexGroup') as t1(c1)
)
,
Missing_Index_4_CTE (DBName, SchemaName, TableName, UseCounts, Impact, Equality, Inequality, Includes, ExecutionPlan)
as
(
Select
DBName,
SchemaName,
TableName,
UseCounts,
Impact,
Equality.query ('for $Column in /ColumnGroup/Column return concat ($Column/@Name, ",")').value('.', 'nvarchar(max)') as Equality,
Inequality.query ('for $Column in /ColumnGroup/Column return concat ($Column/@Name, ",")').value('.', 'nvarchar(max)') as Inequality,
Includes.query ('for $Column in /ColumnGroup/Column return concat ($Column/@Name, ",")').value('.', 'nvarchar(max)') as Includes,
ExecutionPlan
from Missing_Index_3_CTE
)
Select
DBName,
SchemaName,
TableName,
UseCounts,
Impact,
case when len (Equality) = 0
then Equality
else SUBSTRING (Equality , 1, len (Equality) -1)
end Equality,
case when len (Inequality) = 0
then Inequality
else SUBSTRING (Inequality , 1, len (Inequality) -1)
end Inequality,
case when len (Includes) = 0
then Includes
else SUBSTRING (Includes , 1, len (Includes) -1)
end Includes,
ExecutionPlan
from Missing_Index_4_CTE
order by
DBName, SchemaName,TableName, Equality, Inequality, Includes
Viel Spass beim eigenen ausprobieren.
Maic
P.S. : Mir ist durchaus klar, das die letzte CTE nur gemacht wurde, da ich die XQuery nicht überreden konnte, das abschließende Komma innerhalb der XQuery zu unterlassen. Wer da eine bessere Idee hat, darf mir gern seine Lösung für die letzte XQuery mitteilen
