Which one is Preferable [ INDEX REBUILD or DROP ]
Introduction
Indexing is the key factor of the performance. Design and architecture of the Index and table structure has changed depends on modification of application on time to time basis. We have to create new Indexes or modify the existing one to improve the performance. Some time we have to remove the index to see the performance effects and sometime we have to update the data also.
In MS SQL Server 2000 and earlier version we have only one option available is DROP Index and RE-CREATE it. But from MS SQL Server 2005 and later version have another option is called DISABLE Index. So question is in mind that, which one is preferable. In this article we are trying to discuss about the DROP INDEX and DISABLE INDEX feature and which one we choose depends on case scenario.
What Happened When Disable Index Occurs
Before understanding what happened when we Disable a index we must understand how to disable Index.
The Syntax of Disable Index is
ALTER ALTER INDEX <Index_Name>
ON <Schema_Name>.<Table_Name> DISABLE;
Now we have to understand the sys.dm_db_index_usage_stats DMV.
MS SQL Server 2005 onwards the most common way to monitor unused indexes is to use sys.dm_db_index_usage_stats DMV. As the name suggests this DMV returns the information that is tracked about index usage from SQL Server cache.
Disabling Non Clustered Index:
When we Disable a Non Clustered Index the Index page is Deleted and page is freed in the database.
Effect of Disable Non Clustered Index is mentioned bellow.
1. The Query Optimizer cannot use the Index.
2. If we have a Index hint in the SELECT statement.
For example:
SELECT *
FROM <Table_Name> WITH(INDEX(Index_Name));
is going to be Failed.
is going to be Failed.
Disabling Clustered Index:
When we Disable a Clustered Index the Data in the Table remain Exists but not accessible. Only Drop and Rebuild option can access the data.
Effect of Disable Non Clustered Index is mentioned bellow.
1. All the Non Clustered Index and Views are not available.
2. We cannot use any DML action on the Table where Clustered Index is Disable.
To Re-Enable the Index
ALTER INDEX <Index_Name>
ON <Schema_Name>.<Table_Name> REBUID;
What Happened When DROP Index Occurs
To DROP an Index we used
DROP INDEX <Index_Name>
ON <Schema_Name>.<Table_Name> REBUID;
Dropping Index is really a bad idea. When we DROP an Index, the metadata, statistics, and index pages are removed. If we drop a Clustered Index, the table will become a heap.
Effect of Dropping Index is mentioned bellow.
1. Dropping a Clustered Index make the table a Heap.
2. After Drop an Index we cannot use REBUILD options. We must recreate the Index again. So we need the Script of Index definition to Re Create it again.
So Which one to select DROP or DISABLE
First of all DROP a Clustered Index is really a bad idea. It generate Heap. By Clustered Index disable caused DML action is not working on Table.
As per my suggestion Clustered Index is created when the table is designed and do not disturb a clustered index is a good idea.
So we have to think about Non Clustered Index. Whether we DROP it or DISABLE it.
DROP a non clustered Index, need Script or Index definition to recreate it. Therefore, rather than having to script out the drop and create statements, we just disable the relevant indexes, and then issue an ALTER INDEX REBUILD for those indexes when we finished with our modifications. Not only is this a great time saver, but I get the added benefit of still saving disk space.
When an index is rebuilt we need enough disk space to store the old and new copy of the index. When an index is dropped and then recreated, the creation can use the disk space originally used for the index. The only additional space needed is for the sorting, which is about 20% of the disk size, and we can avoid using my data file for the sort if I turn on the SORT_IN_TEMPDB option.
CREATE NONCLUSTERED INDEX <Index_Name>
ON <Schema_Name>.<Table_Name>
(
<Col_Name>
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = ON,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
);
Hope you like it.
Index Rebuild Vs Index Recostructs
There are 2 options
1. Index Rebuild : This process drops the existing Index and Recreates the index.
2. Index Reorganize : This process physically reorganizes the leaf nodes of the index.
Index should be rebuild when index fragmentation is great than 40%.
Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.
