Which query is scanning that index?

SQL 2005's db_dm_index_usage_stats DMV allows DBAs to analyse how often indexes are being used (or not used), including individual counts for seeks, scans & lookups on each index in your database. I was thrilled when I first saw this DMV as there was previously no way to determine whether indexes weren't being used & could be safely removed from a table without risk of queries suddenly full scanning the table & bringing a system's performance to its knees.

Another useful metric reported by db_dm_index_usage_stats is its user_scans column. This reports how many times each index has been fully scanned without a filter predicate (not range scanned). Full scans are usually bad from a performance perspective so it is useful to know whether you have any indexes that are being fully scanned.

Even more useful would be to know which QUERIES are scanning those indexes so you could do something about tuning them. Unfortunately, db_dm_index_usage_stats doesn't quite go this far but I recently decided to try & develop a script to work this out. After scratching around the various performance DMVs & not getting very far, I decided to post a question to the SQL MVP discussion forum.

Thankfully, an ex SQL MVP who now works for Microsoft on the SQL dev team - Umachandar Jayachandran - came up with this innovative approach:

with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as s)

select top 50 st.text, qp.query_plan, qs.ec as exec_count, qs.tlr as total_reads

from (

  select s.sql_handle, s.plan_handle, max(s.execution_count) as ec, max(s.total_logical_reads) as tlr

  from sys.dm_exec_query_stats as s

  where s.max_logical_reads > 100

  group by s.sql_handle, s.plan_handle) as qs

  cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp

  cross apply sys.dm_exec_sql_text(qs.sql_handle) as st

  cross apply (select distinct relop.value('@Index','nvarchar(130)') as IndexRef

  from qp.query_plan.nodes(

     N'//s:Batch/s:Statements/s:StmtSimple/s:QueryPlan[1]//

     s:RelOp[@PhysicalOp = ("Index Scan")]/*[local-name() = ("IndexScan")]/

     s:Object[@Database = ("[DBNameHere]")

     and @Table = ("[TableNameHere]")

     and @Index = ("[IndexNameHere]")]'

     ) as ro(relop)

  ) as r

Notice that this query leverages three performance DMVs:

* dm_exec_query_stats - identifies base query performance stats

* dm_exec_query_plan - cross joined to aggregated view of dm_query_stats to pick up the XML query plan for each query.

* dm_exec_sql_text - also cross joined to pick up each sql statement.

The innovative part of this query is the way it uses XQuery syntax to interrogate the XML typed query_plan column from dm_exec_query_plan, traversing the XML Showplan structure of the query_plan column's nodes collection. It starts at the Batch element, navigating through Statements/StmtSimple/QueryPlan before searching through RelOp elements for one with PhysicalOp attribute = "Index Scan" and an IndexScan element with a child Object element containing the required DBname, TableName & IndexName. This sounds like quite a mouthful, but if you compare the above sentence with some XML output from a query plan which scans an index you should be able to follow the XML traversal fairly easily.

Behind the scenes, this query is actually trawling the procedure cache, performing the above parse operation on every plan in the cache. On systems with large caches (10s of Gb) this might take minutes, but this shouldn't result in a significant performance degradation as the scan is non-blocking & although it works a single CPU hard, it shouldn't consume multi CPUs (it hasn't in my testing so far at least). The filter on max_logical_reads attempts to limit the number of plans parsed. Whilst I have set the filter to 100 reads here, you might experiment with this based on the size of index scans you're evaluating. There's no point parsing query plans that don't have a maximum logical reads count at least the same size as the index scan. Setting this filter to a size close to the number of pages your scanned index is using for storage will usually eliminate the majority of smaller plans within the cache from being XML parsed. 

Keep in mind that this query only works with execution plans currently cached in the procedure cache. This is a consequence of using these DMVs which are limited to accessing contents of the current cache. Because the procedure cache is fairly transient, results may vary if the system is under memory pressure or if you restart SQL Server regularly, or manually clear procedure cache (eg by using dbcc freeproccache).

Another limitation of this query is that it only shows stored procedures, without showing the specific statements within stored procedures that are scanning the index. This shouldn't be too hard to work out by looking at the stored proc code, but other DMVs can help zero in on the actual statements. I'll try & improve this aspect of the code when time permit