SQL SERVER – Disabled Index and Update Statistics
- by pinaldave
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.
Have you ever come across the situation where a conversation never gets over and it continues even though 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 a 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 statistics. Is it true that even though indexes are disabled, they continue updating the statistics?“
The answer is very interesting:
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
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
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.
*/
When we try to update the statistics it throws an error as it 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
We can clearly see that even though the nonclustered index is disabled it is also updated.
If you do not need a nonclustered 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 system all the statistics for disabled indexesare 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 (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL Index, SQL Optimization, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: SQL Statistics
