How Does Disabling an Index Affect the Index Usage DMV Counters

A question was posted to #SQLHelp on Twitter asking, if disabling an Index would clear the index usage counters stored in the SQL Server Dynamic Management Views(DMVs)?
Great question! Not one that is straightforward to answer via the Twitter medium though, so I wanted to share some more detail here.
Consider that the action of disabling a SQL Server Index will not clear the usage counters in sys.dm_db_index_usage_stats. SQL Server will preserve the collected data for a disabled index until such time that:

• the index is either dropped or re-created (CREATE … WITH  DROP_EXISTING) thereby removing the index entry in the DMV.
• the index is rebuilt, effectively resuming the collection of DMV metrics.

Our Twitter chum will be left with an index they cannot use once it is disabled, so we can assume that they will most likely either decide to DROP it or make use of it once again (REBUILD).
The short answer shared to Twitter then was “No – the DMV metrics will not be cleared”, in the knowledge that the poster would be rendering their existing index unusable and be required to perform a subsequent action that would ultimately influence the answer I had provided, one way or the other.

Keeping it Simple on Twitter

I’m of the opinion that answering SQL Server questions on Twitter requires being conservative with the truth on occasion. It’s our responsibility to share knowledge and advice with care, endeavoring to serve the best interests of the community.
It’s the classic design conundrum of “just because you can” does not mean that “you should”.
An example that illustrates this greater responsibility is a recent question on Twitter that asked, if nested Linked Server calls are possible (ServerA.proc -> ServerB.proc -> Server C.proc) ?
Sure this can be achieved using nested stored procedure calls with linked server references but should you do it? Probably not.

Strictly speaking I was answering on Twitter with a bit of fib, albeit with good intentions and so I wanted to post an accurate proof of concept here for completeness.
We’ll begin our test by creating a simple table with two indexes, one Clustered(implicit on the Primary Key) and one Non-Clustered (explicitly created), and insert a single record.

USE tempdb;

GO

-- Create a table for the test case.

IF EXISTS(SELECT NAME FROM sys.sysobjects WHERE name = 'IndexUsageTesting')

DROP TABLE IndexUsageTesting;

CREATE TABLE IndexUsageTesting

(

ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

SomeData VARCHAR(20) NOT NULL

)

GO

--Create a Non-Clustered Index on the table.

CREATE NONCLUSTERED INDEX ncl_SomeData ON IndexUsageTesting(SomeData);

GO

--Insert a record into the table

INSERT INTO IndexUsageTesting(SomeData) VALUES ('blah');

GO

Now let’s review the contents of the DMV sys.dm_db_index_usage to see what data is recorded for the Non-Clustered Index ncl_SomeData.

--Review the index usage metrics for the Non-Clustered index.

Select

OBJECT_NAME(A.object_id) as TableName,

A.index_id,

B.name as IndexName,

A.user_scans,

A.user_seeks,

A.user_updates,

A.user_lookups

from sys.dm_db_index_usage_stats A

inner join sys.indexes B on

A.index_id = B.index_id and A.object_id = B.object_id

where A.database_id = DB_ID(N'tempdb')

and A.object_id = object_id(N'dbo.IndexUsageTesting')

and B.name = 'ncl_SomeData'

ORDER BY A.index_id;

GO

Executing the code above returns the output below, that shows the index ncl_SomeData has been updated once as expected (the result of the INSERT statement).

Just for kicks, let’s now UPDATE the table record to see how it affects the DMV data for the index.

--Update SomeData

UPDATE IndexUsageTesting SET SomeData = 'blah blah blah' where SomeData = (SELECT SomeData FROM IndexUsageTesting)

GO

--Review how this affects the metrics

Select

OBJECT_NAME(A.object_id) as TableName,

A.index_id,

B.name as IndexName,

A.user_scans,

A.user_seeks,

A.user_updates,

A.user_lookups

from sys.dm_db_index_usage_stats A

inner join sys.indexes B on

A.index_id = B.index_id and A.object_id = B.object_id

where A.database_id = DB_ID(N'tempdb')

and A.object_id = object_id(N'dbo.IndexUsageTesting')

and B.name = 'ncl_SomeData'

ORDER BY A.index_id;

GO

As you can see, executing the UPDATE statement modified the DMV entry to now include a scan, a seek and a second update entry.

Let’s now go ahead and DISABLE the Non-Clustered index and execute the very same UPDATE statement as before.

--Disable the Non-Clustered index

ALTER INDEX ncl_SomeData ON IndexUsageTesting DISABLE;

--Update SomeData

UPDATE IndexUsageTesting SET SomeData = 'blah blah blah' where SomeData = (SELECT SomeData FROM IndexUsageTesting);

--Review the DMV

Select

OBJECT_NAME(A.object_id) as TableName,

A.index_id,

B.name as IndexName,

A.user_scans,

A.user_seeks,

A.user_updates,

A.user_lookups

from sys.dm_db_index_usage_stats A

inner join sys.indexes B on

A.index_id = B.index_id and A.object_id = B.object_id

where A.database_id = DB_ID(N'tempdb')

and A.object_id = object_id(N'dbo.IndexUsageTesting')

--and B.name = 'ncl_SomeData'

ORDER BY A.index_id;

GO

Inspecting the result set below, we can see that with the Non-Clustered Index being in a disabled state the update operation did not change the DMV entry but the metrics for the index do persist.
Note: I’ve included the DMV data for the Clustered Index below in the result set for completeness. It highlights the fact that the update operation incremented the DMV record but was omitted from display in previous result sets for clarity.

Now let’s proceed to REBUILD the non-clustered index and execute an UPDATE operation.

--Rebuild the non-clustered index

ALTER INDEX ncl_SomeData ON IndexUsageTesting REBUILD;

GO

--Update SomeData

UPDATE IndexUsageTesting SET SomeData = 'blah blah blah' where SomeData = (SELECT SomeData FROM IndexUsageTesting);

GO

--Review the DMV

Select

OBJECT_NAME(A.object_id) as TableName,

A.index_id,

B.name as IndexName,

A.user_scans,

A.user_seeks,

A.user_updates,

A.user_lookups

from sys.dm_db_index_usage_stats A

inner join sys.indexes B on

A.index_id = B.index_id and A.object_id = B.object_id

where A.database_id = DB_ID(N'tempdb')

and A.object_id = object_id(N'dbo.IndexUsageTesting')

--and B.name = 'ncl_SomeData'

ORDER BY A.index_id;

GO

As you can see, metric collection for the non-clustered index has now resumed, with each value incremented by one as expected.

Closing Thoughts & Your Homework

When you think about it, the persistence of these metrics across REBUILD operations is desirable for practices such as Index Maintenance. I must admit however, that I had initially expected that a DISABLED index that is subsequently re-enabled via a REBUILD operation, would have resulted in the DMV metrics clearing. This walk-through proves otherwise, confirming that disabling a SQL Server Index does not clear the index usage DMV counters. Dependant on what subsequent action is taken on the index however, can result in the data being cleared.
Extra-Credit
What happens if you re-create the ncl index using CREATE…WITH DROP_EXISTING ?
You can download the full source code for this post here: IndexUsageStatsDMVTest.txt
Update 25/06/14: Tests were performed on SQL Server 2008 R2 SP1. Observed behavior is different in SQL Server 2014.

Source  : http://www.johnsansom.com/