SQL SERVER – Disabled Index and Update Statistics

When we try to update the statistics, it throws an error as if the clustered index is disabled. Now let us enable the clustered index only and attempt to update the statistics of the table right after that. Let us learn about Disabled Index and Update Statistics.

Have you ever come across the situation where a conversation never gets over and it continues even though the original point of discussion has passed. I am facing the same situation in the case of Disabled Index. Here is the link to original conversations.

SQL SERVER – Disable Clustered Index and Data Insert – Reader had an issue here with Disabled Index

SQL SERVER – Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index – Reader asked the effect of Rebuilding Indexes

The same reader asked me today – “I understood what the disabled indexes do; what is their effect on the statistics. Is it true that even though the indexes are disabled, they continue updating the statistics?

The answer is very interesting:

Solarwinds

If you have disabled clustered index, you will be not able to update the statistics at all for any index.
If you have enabled clustered index and disabled non clustered index when you update the statistics of the table, it automatically updates the statistics of the ALL (disabled and enabled – both) the indexes on the table.

If you are not satisfied with the answer, let us go over a simple example. I have written necessary comments in the code itself to have a clear idea.

USE tempdb
GO
-- Drop Table if Exists
IF EXISTS 
(SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[TableName]') 
AND type IN (N'U'))
DROP TABLE [dbo].[TableName]
GO
-- Create Table
CREATE TABLE [dbo].[TableName](
[ID] [int] NOT NULL,
[FirstCol] [varchar](50) NULL
)
GO
-- Insert Some data
INSERT INTO TableName
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third'
UNION ALL
SELECT 4, 'Fourth'
UNION ALL
SELECT 5, 'Five'
GO
-- Create Clustered Index
ALTER TABLE [TableName] 
ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
([ID] ASC)
GO
-- Create Nonclustered Index
CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_TableName] ON [dbo].[TableName]
([FirstCol] ASC)
GO
-- Check that all the indexes are enabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO

Now let us update the statistics of the table and check the statistics update date.

-- Update the stats of table
UPDATE STATISTICS TableName
WITH FULLSCAN
GO
-- Check Statistics Last Updated Datetime
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('TableName')
GO

SQL SERVER - Disabled Index and Update Statistics disclu1

Now let us disable the indexes and check if they are disabled using sys.indexes.

-- Disable Indexes
-- Disable Nonclustered Index
ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE
GO
-- Disable Clustered Index
ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE
GO
-- Check that all the indexes are disabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO

SQL SERVER - Disabled Index and Update Statistics disclu2

Let us try to update the statistics of the table.

-- Update the stats of table
UPDATE STATISTICS TableName
WITH FULLSCAN
GO
/*
-- Above operation should thrown following error
Msg 1974, ;Level 16, ;State 1, Line 1
Cannot perform the specified operation on table 'TableName' because its clustered index 'PK_TableName' is disabled.
*/

SQL SERVER - Disabled Index and Update Statistics disclu3

When we try to update the statistics it throws an error as its clustered index is disabled.

Now let us enable the clustered index only and attempt to update the statistics of the table right after that.

-- Now let us rebuild clustered index only
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD
GO
-- Check that all the indexes status
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO
-- Check Statistics Last Updated Datetime
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('TableName')
GO
-- Update the stats of table
UPDATE STATISTICS TableName
WITH FULLSCAN
GO
-- Check Statistics Last Updated Datetime
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('TableName')
GO

SQL SERVER - Disabled Index and Update Statistics disclu4

We can clearly see that even though the non-clustered index is disabled, it is also updated.

If you do not need a non-clustered index, I suggest you to drop it as keeping them disabled is an overhead on your system. This is because every time the statistics are updated for the system all the statistics for disabled indexes are also updated.

-- Clean up
DROP TABLE [TableName]
GO

The complete script is given below for easy reference.

USE tempdb
GO
-- Drop Table if Exists
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[TableName]') AND type IN (N'U'))
DROP TABLE [dbo].[TableName]
GO
-- Create Table
CREATE TABLE [dbo].[TableName](
[ID] [int] NOT NULL,
[FirstCol] [varchar](50) NULL
)
GO
-- Insert Some data
INSERT INTO TableName
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third'
UNION ALL
SELECT 4, 'Fourth'
UNION ALL
SELECT 5, 'Five'
GO
-- Create Clustered Index
ALTER TABLE [TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
([ID] ASC)
GO
-- Create Nonclustered Index
CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_TableName] ON [dbo].[TableName]
([FirstCol] ASC)
GO
-- Check that all the indexes are enabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO
-- Update the stats of table
UPDATE STATISTICS TableName
WITH FULLSCAN
GO
-- Check Statistics Last Updated Datetime
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('TableName')
GO
-- Disable Indexes
-- Disable Nonclustered Index
ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE
GO
-- Disable Clustered Index
ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE
GO
-- Check that all the indexes are disabled
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO
-- Update the stats of table
UPDATE STATISTICS TableName
WITH FULLSCAN
GO
/*
-- Above operation should thrown following error
Msg 1974,Level 16, State 1, Line 1
Cannot perform the specified operation on table 'TableName' because its clustered index 'PK_TableName' is disabled.
*/
-- Now let us rebuild clustered index only
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD
GO
-- Check that all the indexes status
SELECT OBJECT_NAME(OBJECT_ID), Name, type_desc, is_disabled
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'
GO
-- Check Statistics Last Updated Datetime
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('TableName')
GO
-- Update the stats of table
UPDATE STATISTICS TableName
WITH FULLSCAN
GO
-- Check Statistics Last Updated Datetime
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('TableName')
GO
-- Clean up
DROP TABLE [TableName]
GO

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions
Next Post
SQL SERVER – Convert IN to EXISTS – Performance Talk

Related Posts

3 Comments. Leave new

  • André van de Graaf
    May 28, 2010 4:21 pm

    Pinal,

    What is the use of updating a disabled index? The disabled index is not up to date after a while. So what is the use to update statistics of an outdated index. After you enable the index the index will be rebuilded and so you get up to date statistics.

    Gr. André

    Reply
  • Pinal,

    Why is SQL Server updating the Disabled Indexes? Any thoughts?

    Thanks,
    Vishal

    Reply
  • Girijesh Pandey
    October 3, 2012 12:27 am

    Well explained, pretty clear.

    Regards,
    Girijesh

    Reply

Leave a Reply

Menu