question

MichaelMacGregor-8223 avatar image
0 Votes"
MichaelMacGregor-8223 asked MelissaMa-msft commented

How To Get Index Operation From Query Store DMVs?

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelMacGregor-8223 avatar image
0 Votes"
MichaelMacGregor-8223 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelMacGregor-8223 avatar image
0 Votes"
MichaelMacGregor-8223 answered MelissaMa-msft commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MichaelMacGregor-8223,

Thanks for your update.

You could have a try to dive deep into the XML and proceed with next steps or post this issue in XML or .net related forums.

Please also remember to post your answer here if you find what you are looking for finally and accept that as answer or accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
MichaelMacGregor-8223 avatar image
0 Votes"
MichaelMacGregor-8223 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.