【indexing-5】Disabling Clustered Indexes in SQL Server – a really good idea?

Last week I taught an internal SQL Server Performance Tuning course to a customer in Switzerland. As usual we had a lot of fun, and at some point an attendee(n. （会议等的） 出席者;) asked me one of the best questions of my life: “Can you disable a Clustered Index?”

At first I was really scared about that question, because I had never ever thought about disabling a Clustered Index, because the Clustered Index represents the table data. My short answer to this very interesting question was “I think so, but…”

So let’s talk now about the “but”. I really had no idea how SQL Server reacts when you disable the Clustered Index, so let’s try it.

-- Let's disable a Clustered Index
ALTER INDEX PK_Person_BusinessEntityID ON Person.Person DISABLE
GO

The statement simply works! Wow! But you get a lot of different warnings that Foreign-Key Constraints are also disabled. Ok, so we have disabled the Clustered Index of a table, but we have effectively disabled the table itself. Let’s run a simple SELECT statement against the disabled Clustered Index.

-- Let's run a SELECT statement against the disabled Clustered Index
SELECT * FROM Person.Person
GO

Ouch!!! The Query Optimizer can no longer produce an Execution Plan, and we have broken our SQL statement!

Msg 8655, Level 16, State 1, Line 23
The query processor is unable to produce a plan because the index ‘PK_Person_BusinessEntityID’ on table or view ‘Person’ is disabled.

Let’s think about that for a second. Would it be possible to access the table data through a Non-Clustered Index? In theory yes, as long as the chosen Non-Clustered Index is a Covering Non-Clustered Index. If you also have to request some additional columns through a Bookmark Lookup from the Clustered Index, you again have a huge problem, because the Clustered Index is currently disabled. So let’s check the status of the Non-Clustered Indexes through a quick look into sys.indexes:

 

 

Wow! ALL our Non-Clustered Indexes are also DISABLED! There now in no way at all to access our table data. Again, this makes perfect sense, because otherwise we would introduce a huge amount of problems with necessary Bookmark Lookups. We have now effectively taken our table offline by disabling the Clustered Index!

How do we get our table back online? Simple: we have to rebuild our Clustered Index:

-- Let's rebuild the Clustered Index
ALTER INDEX PK_Person_BusinessEntityID ON Person.Person REBUILD
GO

Quite simple, isn’t it? But when we look again at sys.indexes, we will see that our Non-Clustered Indexes are *STILL DISABLED*! Therefore it is very important that we afterwards *ALSO* rebuild all our Non-Clustered Indexes to get them back online.

The moral of the story? Never ever disable the Clustered Index of a table