Another month and it’s another T-SQL Tuesday.  This month we are being hosted by Allen White (Blog | @SQLRunr) and the topic is to Share Your Tricks.

One of my favorite things to do in SQL Server is to run queries against the plan cache.  Within there, you have the ability to see what SQL Server is doing and take a look at operations that you may not have been been aware of previously.

In the case of a trick today, I want to share a simple query that can be used to find all of the execution plans that are using a specific index.  To find a specific index in one of your databases, just change the database and index that is used in the script.

USE AdventureWorks
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DECLARE @IndexName sysname = 'PK_SalesOrderHeader_SalesOrderID';
SET @IndexName = QUOTENAME(@IndexName,'[');
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,IndexSearch
AS (
    SELECT qp.query_plan
        ,cp.usecounts
        ,ix.query('.') AS StmtSimple
    FROM sys.dm_exec_cached_plans cp
        OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
        CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS p(ix)
    WHERE query_plan.exist('//Object[@Index = sql:variable("@IndexName")]') = 1
)
SELECT StmtSimple.value('StmtSimple[1]/@StatementText', 'VARCHAR(4000)') AS sql_text
    ,obj.value('@Database','sysname') AS database_name
    ,obj.value('@Schema','sysname') AS schema_name
    ,obj.value('@Table','sysname') AS table_name
    ,obj.value('@Index','sysname') AS index_name
    ,ixs.query_plan
FROM IndexSearch ixs
    CROSS APPLY StmtSimple.nodes('//Object') AS o(obj)
WHERE obj.exist('//Object[@Index = sql:variable("@IndexName")]') = 1 

There are numerous reasons you may want to find plans that are using an index.  For instance, you may be planning to change an index to add or remove an included column on an index.  Or maybe you are planning to drop an index and want to know what would be impacted.  Another time might be when there are scans on an index and you want to determine exactly the plans the are generating those scans.

Hope this can be as useful to use as it has been for me.  As a word of caution, use care when running this on your servers.  In some systems, querying the plan cache can impact the server.

Related posts:

1. TSQL Tuesday #005 – Wait Stat Report – #TSQL2sDay
2. Can You Dig It? – Researching Index Scans
3. Aggregating With Correlated Sub-Queries #tsql2sday