Following a conversation with my junior DBA, I decided to show you what happens when we disable indexes and in particular when we disable the clustered index for a table in SQL Server.

If we go to Books Online, we can read what exactly happens, so I’m just going to focus on what I think they are the most important bits.
 
Create the playground

Let’s prepare a small demo to accompany these words. First let’s check one of our tables’ indexes to see what they look like, I will use the sample database [AdventureWorks2014], you can see how to get it in one of my previous posts

USE AdventureWorks2014
GO
SELECT  			
		QUOTENAME(DB_NAME()) AS DatabaseName
		, QUOTENAME(OBJECT_SCHEMA_NAME(ix.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ix.object_id)) AS TableName
		, ix.index_id AS index_id 
		, ix.name
		, ix.type_desc
		, CASE WHEN ix.is_primary_key = 1	THEN 'Yes' WHEN ix.is_primary_key = 0	THEN  'No' END AS is_primary_key
		, CASE WHEN ix.is_unique = 1		THEN 'Yes' WHEN ix.is_unique = 0		THEN  'No' END AS is_unique
		, CASE WHEN ix.is_disabled = 1		THEN 'Yes' WHEN ix.is_disabled = 0		THEN  'No' END AS is_disabled
		, ps.row_count
	FROM sys.indexes AS ix
		LEFT JOIN sys.dm_db_partition_stats AS ps
			ON ps.object_id = ix.object_id 
				AND ps.index_id = ix.index_id
	WHERE ix.OBJECT_ID = OBJECT_ID('HumanResources.Department')	
GO

 
So we have a couple of indexes on our [HumanResources].[Department] table, the clustered plus a non-clustered index.

The common behavior is basically that when one index is disabled, the query optimizer cannot use it and queries that specify the index in a query hint, would fail.

Before disabling the index, let’s see how the query plan for this query look like

SELECT * FROM [HumanResources].[Department] WHERE Name = 'Human Resources'

We can see the pattern Index Seek + Key Lookup, so we use both the non-clustered index to locate the row and the clustered to satisfy all those columns not included in the non-clustered.
 
Disabling a non-clustered index

Now if we disable the non-clustered index we can observe how things change

ALTER INDEX AK_Department_Name ON [HumanResources].[Department] DISABLE

SELECT * FROM [HumanResources].[Department] WHERE Name = 'Human Resources'

Since our non-clustered index is disabled, cannot be used, hence the Clustered Index Scan in our query plan.

That is not all folks! now let’s see what happens if we disable our clustered index, but let’s get the old non-clustered back in place

ALTER INDEX AK_Department_Name ON [HumanResources].[Department] REBUILD

 
Disabling a clustered index

Now let’s go see what if we disable our clustered index

ALTER INDEX PK_Department_DepartmentID ON [HumanResources].[Department] DISABLE

SELECT * FROM [HumanResources].[Department] 

BOOM! That wasn’t nice at all…

A disabled clustered index would leave our table completely useless. But, hold on a second, maybe if we don’t need the clustered index, we can get some data back. Let’s try get some data forcing the optimizer to use a different index.

SELECT Name FROM [HumanResources].[Department] WITH (INDEX = AK_Department_Name)

Still no luck, we can read in BOL that Disabling a clustered index on a table also disables all clustered and nonclustered indexes. So there you go.

We can run our original query to see how both indexes are indeed disabled.

SELECT  			
		QUOTENAME(DB_NAME()) AS DatabaseName
		, QUOTENAME(OBJECT_SCHEMA_NAME(ix.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ix.object_id)) AS TableName
		, ix.index_id AS index_id 
		, ix.name
		, ix.type_desc
		, CASE WHEN ix.is_primary_key = 1	THEN 'Yes' WHEN ix.is_primary_key = 0	THEN  'No' END AS is_primary_key
		, CASE WHEN ix.is_unique = 1		THEN 'Yes' WHEN ix.is_unique = 0		THEN  'No' END AS is_unique
		, CASE WHEN ix.is_disabled = 1		THEN 'Yes' WHEN ix.is_disabled = 0		THEN  'No' END AS is_disabled
		, ps.row_count
	FROM sys.indexes AS ix
		LEFT JOIN sys.dm_db_partition_stats AS ps
			ON ps.object_id = ix.object_id 
				AND ps.index_id = ix.index_id
	WHERE ix.OBJECT_ID = OBJECT_ID('HumanResources.Department')	
GO

Yes, both are disabled but have you also spotted something unusual in the number of rows for the non-clustered index? There are no rows, how come?

That I’d say is the biggest difference between clustered and non-clustered index, and it’s when BOL says The index definition of a disabled index remains in the system catalog with no underlying index data that only applies to non-clustered indexes, for a clustered index the data is there for sure, how else would you REBUILD it if the data is gone?

But you don’t have to believe me, see it with your eyes, first the clustered

DBCC IND('AdventureWorks2014', 'HumanResources.Department', 1)

A couple of pages, fair enough, there are only 12 rows, so fine. And now the non-clustered indexes

DBCC IND('AdventureWorks2014', 'HumanResources.Department', 2)

Nothing at all, because disabled non-clustered indexes just keep the metadata, which along with the clustered index data is sufficient to REBUILD the index and bring it back to business.

So this is a nice trick indeed, if you are not sure whether to delete that index or not, DISABLE it first, so the burden goes away, but in case you need to get it back, you only need to REBUILD it. Nice!

BONUS:

You shouldn’t, but if you DISABLE any of your clustered indexes, you might want to check your FOREIGN KEY constraints as they are DISABLED and have to be enabled specifically (same as other non-clustered indexes), it does not happen automatically when you REBUILD the index.

ALTER INDEX ALL ON [HumanResources].[Department] REBUILD
GO
SELECT  
		name
		, QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))		+ '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) AS [object_name]
		, QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id))	+ '.' + QUOTENAME(OBJECT_NAME(referenced_object_id)) AS [referenced_object_name]
		, is_disabled
		, is_not_trusted
	FROM sys.foreign_keys WHERE referenced_object_id = OBJECT_ID('HumanResources.Department')

So, we would need to make our constraint enabled and trusted.

ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] WITH CHECK CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID 

And That’s all folks!
 
 

Facebook

Twitter

Linkedin

Reddit