T-SQL Tuesday #025 – Share Your Tricks #TSQL2sDay

olap_1Another 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


DECLARE @IndexName sysname = 'PK_SalesOrderHeader_SalesOrderID';
SET @IndexName = QUOTENAME(@IndexName,'[');

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
AS (
    SELECT qp.query_plan
        ,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
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.

One thought on “T-SQL Tuesday #025 – Share Your Tricks #TSQL2sDay

Comments are closed.