Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Disabling Indexes


Disabling Indexes

Author
Message
Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8244 Visits: 2712
Comments posted to this topic are about the item Disabling Indexes

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Chris Stewart-397033
Chris Stewart-397033
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1932 Visits: 404
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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5386 Visits: 9860
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6587 Visits: 1407
Good article...Smile



Chris Hamam
Chris Hamam
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 552
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
(
@pvchSchema VARCHAR(256)
,@pvchTable VARCHAR(256)
,@pvchTask VARCHAR(256) --'DISABLE' or 'REBUILD'
,@pbitDebug BIT = 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)
leorajivm
leorajivm
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 13
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?
SuperDBA-207096
SuperDBA-207096
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 Visits: 711
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...
Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8244 Visits: 2712
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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
jim.powers
jim.powers
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1415 Visits: 852
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?
SuperDBA-207096
SuperDBA-207096
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 Visits: 711
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search