question

Hi @MichaelMacGregor-8223,

Welcome to Microsoft Q&A!

Please refer below query and check whether it is helpful to you.

 WITH idxnames
      AS (SELECT si.NAME,
            'declare namespace  qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";                         
     //qplan:RelOp[@LogicalOp="Index Scan"
                            or @LogicalOp="Clustered Index Scan"
                            or @LogicalOp="Index Seek"
                            or @LogicalOp="Clustered Index Seek"]/Object[@Index="'
                 + si.NAME + ']"]' AS filter
          FROM   sys.dm_db_index_usage_stats istat,
                 sys.sysindexes si
          WHERE  istat.object_id = si.id
                 AND istat.index_id = si.indid
                 AND user_updates > ( user_seeks + user_lookups + system_seeks
                                      + system_lookups ))
 SELECT *
 FROM   idxnames
        CROSS apply (SELECT qp.query_plan,
                            qt.text
                     FROM   sys.dm_exec_query_stats
                            CROSS apply sys.Dm_exec_sql_text(sql_handle) qt
                            CROSS apply sys.Dm_exec_query_plan(plan_handle) qp
                     WHERE
        qp.query_plan.exist('sql:column("idxNames.filter")') = 1)  idxPlan 

You could refer this article for more details.

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hi Melissa,

Thanks but unfortunately it doesn't provide the information I'm looking for. For the resultset I'd want something like this:

IndexName
IndexOperation (Index Scan, Clustered Index Scan, Index Seek, Clustered Index Seek)
query_plan (XML)
Count (number of times IndexName & IndexOperation occurred)

Regards,

Mike

To be honest, I don't think you can expect to get an answer about this in a forum. It is a little too advanced to that end.

You will need dig quite more than knee-deep in XML to wrestle out this data. (I have never done something like this myself.) Furthermore, if you want complete data, you will need to do it .NET or elsewhere outside SQL Server. To wit, the xml data type in SQL Server has a nesting limitation that some query plans may exceed - this is why the plans are stored as nvarchar(MAX).

Hi @MichaelMacGregor-8223,

I found one good article and you could refer below.

 CREATE TABLE #planops
 (
   o INT, 
   i INT, 
   h VARBINARY(64), 
   uc INT,
   Scan_Ops   INT, 
   Seek_Ops   INT, 
   Update_Ops INT
 );
     
 DECLARE @sql NVARCHAR(MAX) = N'';
     
 SELECT @sql += N'
     UNION ALL SELECT o,i,h,uc,Scan_Ops,Seek_Ops,Update_Ops
     FROM
     (
       SELECT o = ' + RTRIM([object_id]) + ', 
              i = ' + RTRIM(index_id) +',
              h = pl.plan_handle,
              uc = pl.usecounts, 
          Scan_Ops = p.query_plan.value(''count(//RelOp[@LogicalOp = ''''Index Scan'''''
                + ' or @LogicalOp = ''''Clustered Index Scan'''']/*/'
                + 'Object[@Index=''''' + QUOTENAME(name) + '''''])'', ''int''),
          Seek_Ops = p.query_plan.value(''count(//RelOp[@LogicalOp = ''''Index Seek'''''
                + ' or @LogicalOp = ''''Clustered Index Seek'''']/*/'
                + 'Object[@Index=''''' + QUOTENAME(name) + '''''])'', ''int''),
              Update_Ops = p.query_plan.value(''count(//Update/Object[@Index=''''' 
                + QUOTENAME(name) + '''''])'', ''int'')
       FROM sys.dm_exec_cached_plans AS pl
       CROSS APPLY sys.dm_exec_query_plan(pl.plan_handle) AS p
       WHERE p.dbid = DB_ID()
       AND p.query_plan IS NOT NULL
     ) AS x 
     WHERE Scan_Ops + Seek_Ops + Update_Ops > 0' 
   FROM sys.indexes AS i
   WHERE i.index_id > 0
   AND EXISTS (SELECT 1 FROM #candidates WHERE [object_id] = i.[object_id]);
     
 SET @sql = ';WITH xmlnamespaces (DEFAULT '
     + 'N''http://schemas.microsoft.com/sqlserver/2004/07/showplan'')
     ' + STUFF(@sql, 1, 16, '');
     
 INSERT #planops EXEC sp_executesql @sql;

Or refer the following two queries which, on a larger system, will perform much better than the XML / UNION query.

 -- alternative #1
     
 with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
 insert #planops
 select o,i,h,uc,Scan_Ops,Seek_Ops,Update_Ops
 from 
 (
   select o = i.object_id,
      i = i.index_id,
      h = pl.plan_handle,
      uc = pl.usecounts,
        Scan_Ops = p.query_plan.value('count(//RelOp[@LogicalOp 
          = ("Index Scan", "Clustered Index Scan")]/*/Object[@Index = sql:column("i2.name")])', 'int'),
        Seek_Ops = p.query_plan.value('count(//RelOp[@LogicalOp 
          = ("Index Seek", "Clustered Index Seek")]/*/Object[@Index = sql:column("i2.name")])', 'int'),
      Update_Ops = p.query_plan.value('count(//Update/Object[@Index = sql:column("i2.name")])', 'int')
   from sys.indexes as i
     cross apply (select quotename(i.name) as name) as i2
     cross apply sys.dm_exec_cached_plans as pl
     cross apply sys.dm_exec_query_plan(pl.plan_handle) AS p
   where exists (select 1 from #candidates as c where c.[object_id] = i.[object_id]) 
     and p.query_plan.exist('//Object[@Index = sql:column("i2.name")]') = 1 
     and p.[dbid] = db_id()
     and i.index_id > 0
     ) as T
 where Scan_Ops + Seek_Ops + Update_Ops > 0;
     
 -- alternative #2
     
 with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
 insert #planops
 select o = coalesce(T1.o, T2.o),
    i = coalesce(T1.i, T2.i),
    h = coalesce(T1.h, T2.h),
    uc = coalesce(T1.uc, T2.uc),
    Scan_Ops = isnull(T1.Scan_Ops, 0),
    Seek_Ops = isnull(T1.Seek_Ops, 0),
    Update_Ops = isnull(T2.Update_Ops, 0)
 from
   (
   select o = i.object_id,
      i = i.index_id,
      h = t.plan_handle,
      uc = t.usecounts,
      Scan_Ops = sum(case when t.LogicalOp in ('Index Scan', 'Clustered Index Scan') then 1 else 0 end),
      Seek_Ops = sum(case when t.LogicalOp in ('Index Seek', 'Clustered Index Seek') then 1 else 0 end)
   from (
      select 
        r.n.value('@LogicalOp', 'varchar(100)') as LogicalOp,
        o.n.value('@Index', 'sysname') as IndexName,
        pl.plan_handle,
        pl.usecounts
      from sys.dm_exec_cached_plans as pl
        cross apply sys.dm_exec_query_plan(pl.plan_handle) AS p
        cross apply p.query_plan.nodes('//RelOp') as r(n)
        cross apply r.n.nodes('*/Object') as o(n)
      where p.dbid = db_id()
      and p.query_plan is not null
    ) as t
   inner join sys.indexes as i
     on t.IndexName = quotename(i.name)
   where t.LogicalOp in ('Index Scan', 'Clustered Index Scan', 'Index Seek', 'Clustered Index Seek') 
   and exists (select 1 from #candidates as c where c.object_id = i.object_id)
   group by i.object_id,
        i.index_id,
        t.plan_handle,
        t.usecounts
   ) as T1
 full outer join
   (
   select o = i.object_id,
       i = i.index_id,
       h = t.plan_handle,
       uc = t.usecounts,
       Update_Ops = count(*)
   from (
       select 
     o.n.value('@Index', 'sysname') as IndexName,
     pl.plan_handle,
     pl.usecounts
       from sys.dm_exec_cached_plans as pl
     cross apply sys.dm_exec_query_plan(pl.plan_handle) AS p
     cross apply p.query_plan.nodes('//Update') as r(n)
     cross apply r.n.nodes('Object') as o(n)
       where p.dbid = db_id()
       and p.query_plan is not null
     ) as t
   inner join sys.indexes as i
     on t.IndexName = quotename(i.name)
   where exists 
   (
     select 1 from #candidates as c where c.[object_id] = i.[object_id]
   )
   and i.index_id > 0
   group by i.object_id,
     i.index_id,
     t.plan_handle,
     t.usecounts
   ) as T2
 on T1.o = T2.o and
    T1.i = T2.i and
    T1.h = T2.h and
    T1.uc = T2.uc;

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hi Melissa,

Yes I have used Aaron Bertrand's index suggestion analysis a lot, it's extremely useful however it is not what I am looking for. I'm looking to get counts of index usage based on the execution plans from the query store DMVs.

Regards,

Mike

Hi @ErlandSommarskog,

I think you are right. I may have to roll up my sleeves and dive deep into the XML and process it in stages to get what I need. Really dislike XML. <sigh>

Regards,

Mike