Which queries wanted the missing index?

• iwaldner

  SSC Eights!

  Points: 805

  More actions

  January 3, 2008 at 12:44 pm

  #181728

  Is there some way to determine which queries required a particular index reported e.g. in the MissingIndex report of the Performance Dashboard? I'm looking at one example, where the missing index is suggested as on the columns X,Y,Z but there is already an index on X,Z,Y which was not used in this case. If I could identify the queries that use these indexes, it might make more sense to rewrite the query rather than add the index.. How could I find this type of information?

  Thanks,

  Ilmar

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 3, 2008 at 1:25 pm

  #764559

  I don't think that's stored anywhere in the index DMVs. I have seen missing index information included in the xml showplan, so if you dig through the plan cache you should be able to find some info there.

  Won't be a quick task though, unless you write some complex piece of xpath to read the xml docs returned by the sys.dm_exec_query_plan dmv and only return those that have missing index info in them

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  January 3, 2008 at 3:25 pm

  #764600

  tricky! but along side your missing indexes are also worst queries, you should be able to marry up worst queries to missing indexes - you'll know the table(s) at least. Don't forget dmv's are usually cumulative, you might want to clear them just in case it's old data.

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/

• Big Data Man

  SSCrazy Eights

  Points: 8536

  More actions

  January 4, 2008 at 4:30 am

  #764757

  Hi,

  here's a script I have in my tool-kit, which should help you.

  Thanks,

  Phillip Cox

  -- Potentially Useful Indexes

  select d.*

  , s.avg_total_user_cost

  , s.avg_user_impact

  , s.last_user_seek

  ,s.unique_compiles

  from sys.dm_db_missing_index_group_stats s

  ,sys.dm_db_missing_index_groups g

  ,sys.dm_db_missing_index_details d

  where s.group_handle = g.index_group_handle

  and d.index_handle = g.index_handle

  order by s.avg_user_impact desc

  go

  --- suggested index columns and usage

  declare @handle int

  select @handle = d.index_handle

  from sys.dm_db_missing_index_group_stats s

  ,sys.dm_db_missing_index_groups g

  ,sys.dm_db_missing_index_details d

  where s.group_handle = g.index_group_handle

  and d.index_handle = g.index_handle

  select *

  from sys.dm_db_missing_index_columns(@handle)

  order by column_id

  --Largest IO queries.

  --The underlying purpose of an index suggested by sys.dm_db_missing_index_columns, is to avoid doing large amounts of IO for the query in question. Therefore, you can expect such queries to rank among the highest IO queries. To find the highest IO queries, you can use the following sample code:

  --- top 50 statements by IO

  SELECT TOP 50

  (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],

  substring (qt.text,qs.statement_start_offset/2,

  (case when qs.statement_end_offset = -1

  then len(convert(nvarchar(max), qt.text)) * 2

  else qs.statement_end_offset end - qs.statement_start_offset)/2)

  as query_text,

  qt.dbid,

  qt.objectid

  FROM sys.dm_exec_query_stats qs

  cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt

  ORDER BY [Avg IO] DESC

• iwaldner

  SSC Eights!

  Points: 805

  More actions

  January 10, 2008 at 1:43 pm

  #766856

  Thanks.. looks like this will help..