July 21, 2008 at 10:41 pm
Comments posted to this topic are about the item Disabling Indexes
July 21, 2008 at 10:46 pm
Hi,
Actually, disabling a nonclustered index deletes the physical index; only the definition is retained. Per BOL:
"The index definition of a disabled index remains in the system catalog with no underlying index data."
As for specific usages, we had a case where there were unused indexes on a production environment taking up 10% of the database (~450GB). There is still legacy code external to the database which contains index hints. While we checked the code, we couldn't guarantee there were any references missed. Disabling the indexes allowed us to force an error that the index was disabled as opposed to an index not existing. With this method in the case of this error we wouldn't have to worry about finding the correct script to recreate the index - we could just rebuild it without knowing the definition.
Chris
July 21, 2008 at 11:00 pm
I have used this feature a lot in an ETL process where it is very useful. The process is:
1) Extract data from OLTP into staging tables
2) Disable non-clustered indexes
3) Transform and load data
4) Rebuild ALL indexes
Works very well...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2008 at 12:56 am
Good article...:)
July 22, 2008 at 1:58 am
Jeffrey Williams (7/21/2008)
I have used this feature a lot in an ETL process where it is very useful...
I agree and also use this technique to speed up table load times, but since indexes are generally maintained independently from ETL processes, I created this stored procedure that does it automatically for a given schema, table and DISABLE/ENABLE command:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.usp_IndexDisableRebuild') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.usp_IndexDisableRebuild
GO
CREATE PROC dbo.usp_IndexDisableRebuild
(
@pvchSchemaVARCHAR(256)
,@pvchTableVARCHAR(256)
,@pvchTaskVARCHAR(256)--'DISABLE' or 'REBUILD'
,@pbitDebugBIT = 0
)
AS
/*
Created By: Chris Hamam
Created: 2007-07-27
Purpose: Generic stored proc to disable/enable all non-clustered indexes on a table.
Example: EXEC dbo.usp_IndexDisableRebuild 'dbo', 'Customers', 'REBUILD'
*/
SET NOCOUNT ON
DECLARE @sql VARCHAR(max)
SET @sql = ''
SELECT @sql = @sql + 'ALTER INDEX ' + i.name + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' ' + @pvchTask + '
'
--SELECT object_name(i.object_id) tablename,*
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.index_id > 1
AND s.name = @pvchSchema
AND o.name = @pvchTable
IF @pbitDebug = 1
PRINT @sql
ELSE
EXEC (@sql)
GO
--Chris Hamam
Life's a beach, then you DIE (Do It Eternally)
July 22, 2008 at 2:45 am
Would think that if there are going to be a huge number of inserts/updates, disabling a non clustered index would greatly help perfomance - no?
July 22, 2008 at 4:31 am
It's a good article... first thing that comes to mind is disabling for an ETL process, but since you'd have to rebuild anyway, you could get the same result from dropping and recreating the indices...
July 22, 2008 at 5:35 am
Chris, thanks for the correction on the NC index, don't know how I missed that!
ETL still seems like the best scenario, only because there's sort of less work to do managing the scripts to re-create compared to just iterating through and rebuilding existing indexes.
July 22, 2008 at 7:20 am
ETL may be the best and only scenario but thinking of a particular table we have in our environment, I'm not sure that there would be any justification to disable and then rebuild the indexes. This particular table has nearly 9 million rows with 14 different indexes. This is a vendor app and they use hints all over their code so I can't disable (or remove) any of the indexes even though some of them appear to be redundant.
Side note: my boss asked me to see why the database seems to be growing more rapidly than it used to. When I looked, I found this table consumes the largest amount of space in the DB at 12+GB. Almost 8.5GB of that is indexes alone. I suggested we look at what indexes can be removed and changed and he said to leave them as they are since it would likely break the vendor app. Interestingly enough, we have another table with 23.5 million rows that only consumes 7GB of the DB.
In this case, it seems it would be better to leave them enabled during an ETL process so that they are maintained rather than having to wait an hour or two after the ETL for the index to rebuild. It seems that there must be some sort of tradeoff point where it is more efficient to leave the indexes enabled?
July 22, 2008 at 7:29 am
It's been my experience with larger tables it makes sense to drop and rebuild... the inserts go alot faster without having to deal with the indices. Guess it depends on how many rows are being inserted and weather the table is cleared out first.
Either way it could take a looong time.... probably worthwhile testing and seeing which way is faster.
July 22, 2008 at 8:58 am
Have to find the tipping point. Drop/create may or may not be faster, for example if you are adding 1 million rows to a 100 million row table. The other portion of the trade off is fragmentation, by rebuilding at the end you correct any fragmentation created during load. Either way you should update column based stats (those that dont match to an index) post load.
July 22, 2008 at 10:49 am
Andy Warren (7/22/2008)
Have to find the tipping point. Drop/create may or may not be faster, for example if you are adding 1 million rows to a 100 million row table. The other portion of the trade off is fragmentation, by rebuilding at the end you correct any fragmentation created during load. Either way you should update column based stats (those that dont match to an index) post load.
Definitely have to find the tipping point - and determine what is best for your process. However, dropping and recreating the indexes is actually a lot more work than disabling/rebuilding.
As another poster pointed out - it is very easy to build a procedure that loops through all non-clustered indexes and disable them. This allows for adding new indexes, dropping existing indexes, etc... as you need them without having to modify your code at all.
To rebuild the indexes at the end of the process, you issue a single command for the table:
ALTER INDEX ALL ON table REBUILD;
This rebuilds all indexes for the table, including the clustered index. I have a table on my report server with well over 120 million rows and multiple indexes. The rebuild of all indexes on this one table takes no more than 30 minutes, which is far less time than the increased processing time if I leave the indexes enabled.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2008 at 11:25 am
I have tested and used this feature a lot. Some advantages to me were, I did not have to script out every index on a table before dropping it. This made database maintenance much easier. If during monitoring missing indexes, I deemed it necessary to add a new index, It would not be lost because index creation were stored in other processes. In testing, I found that disabling all non clustered indexes and leaving the cluster index enabled, then bulk loading data (100's of millions of rows), then re-enabling to rebuild the index, took approx. the same about of time to script and save all indexes, drop all indexes, bulk load data, reapply the indexes. So, for me, this allowed for reduced scripts to maintain.
I also created a stored procedure that would accept a table name and an option to enable\disable indexes. I also added an option to truncate\delete rows.
July 22, 2008 at 12:25 pm
Andy Warren (7/22/2008)
Have to find the tipping point. Drop/create may or may not be faster, for example if you are adding 1 million rows to a 100 million row table. The other portion of the trade off is fragmentation, by rebuilding at the end you correct any fragmentation created during load. Either way you should update column based stats (those that dont match to an index) post load.
I agree, you have to find tipping point for your system.
What I have seen in our Data Warehouse is that the Fact tables with few indexes and thus having index space smaller than data space load faster with drop/recreate during the ETL.
On the other hand for Fact tables with lot of indexes and thus having index space greater than data space, ETL is faster if indexes are not dropped. We do rebuild these indexes monthly though to take care of fragmentation.
So sometimes you have to use both strategies in your system; drop/recreate and leaving the indexes alone during ETL. Keeping track of it does adds to maintenance list though!
July 22, 2008 at 3:58 pm
Very interesting article and discussion. Thank you!
Nicole Bowman
Nothing is forever.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply