May
18
2016

Index fun, disabling the Clustered Index

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

01_indexes_before_disabling
 
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'

02_Queryplan_before_disabling

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'

03_Queryplan_after_disabling

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] 

04_error_after_disabling_clustered

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)

05_error_query_hint

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

06_both_index_disabled_query_result

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)

08_DBCC_INC_clustered

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)

09_DBCC_INC_nonclustered

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')

10_Foreign_keys_disabled

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!
 
 

8 comments
  1. Sergey says:

    Hmm, nice one. Never disabled clustered index before but didn’t know that this will make table unused. Same happened for inserts too?

    • Raul says:

      Yes, it’s disabled disabled, nothing that affects that table can happen. 🙂

    • Patrick says:

      A clustered index is not a normal index, the data in the table is stored in/as a clustered index. Table=clustered index. So it should be no surprise the table gets unusable after disabling the clustered index. The non-clustered indexes are not working anymore because it references the clustered index. I didn’t know it is possible to disable a clustered index.

      • Raul says:

        Thanks for your comment, you’re right the clustered index is the table, but the non clustered indexes can be queried alone, they do not “need” the clustered index if they can cover the query (covering indexes).

        The reason they stop working is because when we disable the clustered, that implies disabling the non clustered, and you’ve seen how that will remove all data and leave just the metadata.

        Cheers!

  2. Kevin Fries says:

    Klaus Aschenbrenner did something similar about a month ago on his blog.
    It’s just as much fun.
    http://www.sqlpassion.at/archive/2016/04/11/disabling-clustered-indexes-in-sql-server-a-really-good-idea/

    • Raul says:

      Thanks for your comment! Yes, that indeed was what I was discussing about with my colleague when I decided to write this post. I also wanted to show how things are internally, e.g. the behavior of clustered vs non-clustered and foreign keys.

Leave a Reply

Your email address will not be published. Required fields are marked *