I'm looking indexes with 0 reads with SP_blitzindex, from there I am able to grab the indexes names that are probably not helping my system.

Using the top query posted by Kendra:

    SUBSTRING(sqltext.text, (querystats.statement_start_offset / 2) + 1, 
                (CASE querystats.statement_end_offset 
                    WHEN -1 THEN DATALENGTH(sqltext.text) 
                    ELSE querystats.statement_end_offset 
                END - querystats.statement_start_offset) / 2 + 1) AS sqltext, 
    CAST(query_plan AS xml) as plan_xml
FROM sys.dm_exec_query_stats as querystats
CROSS APPLY sys.dm_exec_text_query_plan
    (querystats.plan_handle, querystats.statement_start_offset, querystats.statement_end_offset) 
    as textplan
CROSS APPLY sys.dm_exec_sql_text(querystats.sql_handle) AS sqltext 
    textplan.query_plan like '%PK_Sales_Invoices%'
ORDER BY querystats.last_execution_time DESC

I see all queries that use my index from the cache. Closer inspection shows they are used because we have to maintain the index.

How do I only view queries that use my indexes for reads regardless of writes? If 7 / 7 queries use my index because they are updating columns that force the server to update my index and it never reads the index, I want to be able to see those separately from queries that read my index or have a flag indicating such.

I have this query I found and have tried:

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
'INDEX NAME'    as IndexName,
getdate() as TimeStart,
   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=''[INDEX NAME]''])') AS [ScanCount],
   E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']/*/Object[@Index=''[INDEX NAME]''])') AS [SeekCount],
   E.query_plan.query('count(//Update/Object[@Index=''[INDEX NAME]''])') 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
   E.query_plan.exist('//*[@Index=''[INDEX NAME]'']') = 1

My concerns with that query is:

a) Accuracy, is it for sure going to tell me if I had scans or seeks (reads) on my index in question?

b) How do I know about inserts into my indexes with that query? It looks like it shows me the updates to the index but not the inserts. (Clustered index insert impacting non-clustered index)

  • So you don't want to inspect individual plans or specific indexes then, what you are looking for is a list of all indexes where the only read activity is being used to support writes, or where all of the writes actually didn't require a read of the same index? Is there a difference really? Seems in both cases the index is not useful. For b) I know that sometimes a plan for the exact same insert statement can be implemented as a ScalarInsert rather than an Update, so only looking at //Update/ will not catch all insert types (never mind deletes). Apr 13, 2018 at 16:42
  • @AaronBertrand, that is essentially what I'm looking for. I also wanted to find what queries do use my indexes for reads, just in the event I have a low read to high write ratio index that is used for a very important query that requires performance. I want to try and estimate the impact I'm making on the process running the important query in that instance so I can use that as a baseline to tune the query, modify another existing index, or adjust the current index. The 2nd query in my post seems to give me that information, but obviously there are flaws as you pointed out. Apr 13, 2018 at 17:05


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.