------------------------------------------------------------------------------------------------------------
-- 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
*/