She Can Dig It!

A while back I was asked, if you can use the plan cache to determine which plans are using specific indexes.  The answer is, of course, yes, you can.  And it’s important that people, especially DBAs, understand how to do this.  Usually during index analysis, we concern ourselves with whether an index is being used.  If it isn’t used, it becomes a candidate for removal.  After a few months of monitoring index usage, you are often safe to remove indexes that never get used.

But what about the indexes that are used infrequently?  What if an index is only used once a day or once a week.  Is it worth the resources for maintaining the index and the storage space dedicated to the index?  Frequency of use is only one measurement of the value of an index.  To get the whole picture, you need to know what the index is doing when it is being used.  In other words, what business process is the index supporting and how will that process function without the index in place.

Since we know the tables in our databases, we often have a good idea how tables are used.  But we don’t really know how the indexes are being used.  To allow us to continue to be able to drop indexes, we need to be able to demonstrate where infrequently used indexes are being used and detail what dropping the index will affect.  While this information is not stored as a set of statistics in dynamic management objects (DMOs), it can be retrieved by querying the SHOWPLAN XML available in sys.dm_exec_query_plan.

Index Usage Setup

Before we can begin with demonstrating this query, we’ll first setup a scenario that uses the index PK_SalesOrderHeader_SalesOrderID in a few queries.  The first two queries select individual orders from the Sales.SalesOrderHeader table and the last returns all records from the table.  The purpose of the variations of SELECT statements is to also demonstration how eve similar queries can generate different plans.

USE AdventureWorks2012
GO
SELECT * FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 10000
SELECT * FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 51531
SELECT * FROM Sales.SalesOrderHeader

Index Usage Query

When querying sys.dm_exec_query_plan, the query_plan column provides an XML representation of the execution plan in the SHOWPLAN XML format.  As noted in previous posts, in this series, there is a lot of great information in execution plans that can be used to performance tune and troubleshoot your servers.

In this example, we are interested in plans that are using PK_SalesOrderHeader_SalesOrderID.  This can be found in the Object element under the StmtSimple XML element.  There are a number of attributes in the Object element.  The one we are interested in today is the Index attribute.  This attribute is quite literally the index that the execution plan is using to satisfy the query request.  By leveraging the exist XQuery function, we can quickly identify any execution plans that are using this particular index.

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
,ixs.usecounts
,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

As you can can see in the results, the output returns all of the queries that used the index from the demonstration script.  Each appeared twice, since I ran the demo script twice as well.  One bit of interest, the first two queries, while identical except for the value used in the predicate, resulted in two separate plans.  The first plan uses the variable that is a smallint and the second uses an int.  This is a situation where parameterizing the plans may prevent plan cache bloating.

Index Usage Wrap-Up

As was mentioned in the introduction, it is important to know the true value of an index before you make plans to drop it.  Just because the index is infrequently used doesn’t mean that the index isn’t used in critical processes.  I recall past situations where the elimination of an index used once a day increased import times from minutes to hours.  When you need to use this in your environment, simply change the value for the parameter in the script and dig into you plan cache.

Do you see a use for doing this in your environment?  Or do you remember a time that you could have used a query like this?

Related posts:

1. Can You Dig It? – Plan Cache Series
2. Can You Dig It? – The Plan Cache
3. Can You Dig It? – Parallelism in the Plan Cache