------------------------------------------------------------------------------------------------------------ -- Date: 27/05/14 -- Author: John Sansom -- Webstite: http://www.johnsansom.com -- Description: Test case for investigating how SQL Server Index DMV counters are affeceted -- when disabling a non-clustered index. -- -- Originally published on: -- http://www.johnsansom.com/how-does-disabling-an-index-affect-the-index-usage-dmv-counters/ -- -- Tested On: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4276.0 (X64) -- ------------------------------------------------------------------------------------------------------------ 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 --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 --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 --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 --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 --Extra Credit - What happens if you re-create the index? /* CREATE INDEX ncl_SomeData ON IndexUsageTesting(SomeData) WITH DROP_EXISTING; GO 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 */