{"id":213,"date":"2016-05-18T11:30:23","date_gmt":"2016-05-18T10:30:23","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=213"},"modified":"2016-05-31T20:30:34","modified_gmt":"2016-05-31T19:30:34","slug":"index-fun-disabling-the-clustered-index","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/05\/18\/index-fun-disabling-the-clustered-index\/","title":{"rendered":"Index fun, disabling the Clustered Index"},"content":{"rendered":"

Disabling an index is always fun, but disabling the clustered index is even funnier, but probably not for your users, let me show you what can happen. 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.<\/p>\n

If we go to Books Online<\/a>, we can read what exactly happens, so I’m just going to focus on what I think they are the most important bits.
\n 
\nCreate the playground<\/strong><\/p>\n

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<\/a><\/p>\n

\r\nUSE AdventureWorks2014\r\nGO\r\nSELECT  \t\t\t\r\n\t\tQUOTENAME(DB_NAME()) AS DatabaseName\r\n\t\t, QUOTENAME(OBJECT_SCHEMA_NAME(ix.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ix.object_id)) AS TableName\r\n\t\t, ix.index_id AS index_id \r\n\t\t, ix.name\r\n\t\t, ix.type_desc\r\n\t\t, CASE WHEN ix.is_primary_key = 1\tTHEN 'Yes' WHEN ix.is_primary_key = 0\tTHEN  'No' END AS is_primary_key\r\n\t\t, CASE WHEN ix.is_unique = 1\t\tTHEN 'Yes' WHEN ix.is_unique = 0\t\tTHEN  'No' END AS is_unique\r\n\t\t, CASE WHEN ix.is_disabled = 1\t\tTHEN 'Yes' WHEN ix.is_disabled = 0\t\tTHEN  'No' END AS is_disabled\r\n\t\t, ps.row_count\r\n\tFROM sys.indexes AS ix\r\n\t\tLEFT JOIN sys.dm_db_partition_stats AS ps\r\n\t\t\tON ps.object_id = ix.object_id \r\n\t\t\t\tAND ps.index_id = ix.index_id\r\n\tWHERE ix.OBJECT_ID = OBJECT_ID('HumanResources.Department')\t\r\nGO\r\n<\/pre>\n
\"01_indexes_before_disabling\"<\/a>
\n 
\nSo we have a couple of indexes on our [HumanResources].[Department] table, the clustered plus a non-clustered index.<\/p>\n
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. <\/p>\n
Before disabling the index, let’s see how the query plan for this query look like<\/p>\n
\r\nSELECT * FROM [HumanResources].[Department] WHERE Name = 'Human Resources'\r\n<\/pre>\n
\"02_Queryplan_before_disabling\"<\/a><\/p>\n
We can see the pattern Index Seek + Key Lookup<\/em><\/strong>, 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.
\n 
\nDisabling a non-clustered index<\/strong><\/p>\n
Now if we disable the non-clustered index we can observe how things change<\/p>\n
\r\nALTER INDEX AK_Department_Name ON [HumanResources].[Department] DISABLE\r\n\r\nSELECT * FROM [HumanResources].[Department] WHERE Name = 'Human Resources'\r\n<\/pre>\n
\"03_Queryplan_after_disabling\"<\/a><\/p>\n
Since our non-clustered index is disabled, cannot be used, hence the Clustered Index Scan<\/em><\/strong> in our query plan.<\/p>\n
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<\/p>\n
\r\nALTER INDEX AK_Department_Name ON [HumanResources].[Department] REBUILD\r\n<\/pre>\n
 
\nDisabling a clustered index<\/strong><\/p>\n
Now let’s go see what if we disable our clustered index<\/p>\n
\r\nALTER INDEX PK_Department_DepartmentID ON [HumanResources].[Department] DISABLE\r\n\r\nSELECT * FROM [HumanResources].[Department] \r\n<\/pre>\n
\"04_error_after_disabling_clustered\"<\/a><\/p>\n
BOOM! That wasn’t nice at all… <\/p>\n
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.<\/p>\n
\r\nSELECT Name FROM [HumanResources].[Department] WITH (INDEX = AK_Department_Name)\r\n<\/pre>\n
\"05_error_query_hint\"<\/a><\/p>\n
Still no luck, we can read in BOL<\/a> that Disabling a clustered index on a table also disables all clustered and nonclustered indexes<\/em><\/strong>. So there you go.<\/p>\n
We can run our original query to see how both indexes are indeed disabled.<\/p>\n
\r\nSELECT  \t\t\t\r\n\t\tQUOTENAME(DB_NAME()) AS DatabaseName\r\n\t\t, QUOTENAME(OBJECT_SCHEMA_NAME(ix.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ix.object_id)) AS TableName\r\n\t\t, ix.index_id AS index_id \r\n\t\t, ix.name\r\n\t\t, ix.type_desc\r\n\t\t, CASE WHEN ix.is_primary_key = 1\tTHEN 'Yes' WHEN ix.is_primary_key = 0\tTHEN  'No' END AS is_primary_key\r\n\t\t, CASE WHEN ix.is_unique = 1\t\tTHEN 'Yes' WHEN ix.is_unique = 0\t\tTHEN  'No' END AS is_unique\r\n\t\t, CASE WHEN ix.is_disabled = 1\t\tTHEN 'Yes' WHEN ix.is_disabled = 0\t\tTHEN  'No' END AS is_disabled\r\n\t\t, ps.row_count\r\n\tFROM sys.indexes AS ix\r\n\t\tLEFT JOIN sys.dm_db_partition_stats AS ps\r\n\t\t\tON ps.object_id = ix.object_id \r\n\t\t\t\tAND ps.index_id = ix.index_id\r\n\tWHERE ix.OBJECT_ID = OBJECT_ID('HumanResources.Department')\t\r\nGO\r\n<\/pre>\n
\"06_both_index_disabled_query_result\"<\/a><\/p>\n
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?<\/p>\n
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<\/em><\/strong> 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?<\/p>\n
But you don’t have to believe me, see it with your eyes, first the clustered<\/p>\n
DBCC IND('AdventureWorks2014', 'HumanResources.Department', 1)<\/pre>\n
\"08_DBCC_INC_clustered\"<\/a><\/p>\n
A couple of pages, fair enough, there are only 12 rows, so fine. And now the non-clustered indexes<\/p>\n
DBCC IND('AdventureWorks2014', 'HumanResources.Department', 2)<\/pre>\n
\"09_DBCC_INC_nonclustered\"<\/a><\/p>\n
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.<\/p>\n
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!<\/p>\n
BONUS:<\/strong><\/p>\n
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.<\/p>\n
\r\nALTER INDEX ALL ON [HumanResources].[Department] REBUILD\r\nGO\r\nSELECT  \r\n\t\tname\r\n\t\t, QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))\t\t+ '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) AS [object_name]\r\n\t\t, QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id))\t+ '.' + QUOTENAME(OBJECT_NAME(referenced_object_id)) AS [referenced_object_name]\r\n\t\t, is_disabled\r\n\t\t, is_not_trusted\r\n\tFROM sys.foreign_keys WHERE referenced_object_id = OBJECT_ID('HumanResources.Department')\r\n<\/pre>\n
\"10_Foreign_keys_disabled\"<\/a><\/p>\n
So, we would need to make our constraint enabled and trusted.<\/p>\n
\r\nALTER TABLE [HumanResources].[EmployeeDepartmentHistory] WITH CHECK CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID \r\n<\/pre>\n
And That’s all folks!
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Disabling an index is always fun, but disabling the clustered index is even funnier, but probably not for your users, let me show you what can happen. Following a conversation with my junior…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[35,5,12],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/213"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=213"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/213\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=213"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}