How To Get Index Operation From Query Store DMVs?

Question

I'd like to query the query store DMVs to get the index operations (scan/seek) from the execution plan XML that is in query_store_plan. I found the following query that will get information about a specific index, but I'd like to adapt this query to get each index used, and the count of whether it does an index seek or index scan. Thing is I find manipulating XML awkward so any help is very much appreciated.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
DB_NAME(E.dbid) AS [DBName],
object_name(E.objectid, dbid) AS [ObjectName],
P.cacheobjtype AS [CacheObjType],
P.objtype AS [ObjType],
E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Scan'' or @LogicalOp = ''Clustered Index Scan'']//Object[@index =''[MyIndex]''])') AS [ScanCount],
E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']//Object[@index =''[MyIndex]''])') AS [SeekCount],
E.query_plan.query('count(//Update/Object[@index =''[MyIndex]''])') AS [UpdateCount],
P.refcounts AS [RefCounts],
P.usecounts AS [UseCounts],
E.query_plan AS [QueryPlan]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E
WHERE
E.dbid = DB_ID('MyDatabase') AND
E.query_plan.exist('//*[@index =''[MyIndex]'']') = 1

Answer 1

Hi @Michael MacGregor ,

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.

Answer 2

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

Answer 3

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).

Answer 4

Hi @Michael MacGregor ,

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.

Answer 5

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