How To Get Index Operation From Query Store DMVs?

Michael MacGregor 86 Reputation points
2021-03-19T16:46:06.52+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,274 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,181 Reputation points
    2021-03-22T06:46:29.72+00:00

    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.

    0 comments No comments

  2. Michael MacGregor 86 Reputation points
    2021-03-22T13:30:30.527+00:00

    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

    0 comments No comments

  3. Erland Sommarskog 106K Reputation points MVP
    2021-03-22T22:28:28.87+00:00

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

    0 comments No comments

  4. MelissaMa-MSFT 24,181 Reputation points
    2021-03-23T06:51:45.39+00:00

    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.

    0 comments No comments

  5. Michael MacGregor 86 Reputation points
    2021-03-23T13:27:28.783+00:00

    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