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/@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.