Queries, Plans, and Indexes


The reason I wrote this script was to help locate the queries using indexes. Sql Server 2005 allows you to see exactly how your indexes are being used, and sometimes you spot one that has only been used a few times, or maybe one that only gets scanned, with no seeks. You want to find where the indexes are being used so you can fix the queries somehow.

Note that the query ignores indexes in the sys schema, and it also ignores any index references it finds directly under "Update" nodes, so you can focus on where the indexes are actually being used.

This can take a long time to run. On my system ( a few hundred tables, 30Gb data size), it took almost 30 minutes and returned over 100,000 rows. You should select the contents into a temp table and go from there with your analysis.


This query gets all queries, their query plans, and all indexes included in those plans.

Authors:            Eric Z. Beard, eric@loopfx.com, ericzbeard@yahoo.com
                        (borrowed some stuff from http://mohammedu.spaces.live.com/Blog/cns!6699CF8ADD3D4F67!325.entry, 
                            Greg Lindwood? Uma Chandran?)
Date of Origin:        July, 2008


use master;

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

,    qp.query_plan
,    qp_objects.qp_obj_schema
,    qp_objects.qp_obj_table
,    qp_objects.qp_obj_index
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
 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 ( 
            obj_nodes.value('@Schema', 'nvarchar(130)') as qp_obj_schema 
        ,    obj_nodes.value('@Table', 'nvarchar(130)') as qp_obj_table 
        ,    obj_nodes.value('@Index', 'nvarchar(130)') as qp_obj_index 
    from qp.query_plan.nodes('//s:Object[@Schema != "[sys]" and @Index and local-name(parent::node()) != "Update"]') as qry_plan(obj_nodes) 
) as qp_objects