Disabling Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Disabling Indexes

18 posts, Page 1 of 2

1 2 »»

Disabling Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Monday, July 21, 2008 10:41 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Comments posted to this topic are about the item Disabling Indexes

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #538139

Chris Stewart-397033

Posted Monday, July 21, 2008 10:46 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 10:23 AM
Points: 1,263, Visits: 234

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

Post #538141

Jeffrey Williams 3188

Posted Monday, July 21, 2008 11:00 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

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 opportunites brilliantly disguised as insurmountable obstacles.

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

Post #538148

Anipaul

Posted Tuesday, July 22, 2008 12:56 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 4,786, Visits: 1,335

Good article...:)

Post #538205

Chris Hamam

Posted Tuesday, July 22, 2008 1:58 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 3:29 PM
Points: 73, Visits: 520

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)

Post #538226

leorajivm

Posted Tuesday, July 22, 2008 2:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 16, 2009 7:30 AM
Points: 5, 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?

Post #538249

SuperDBA-207096

Posted Tuesday, July 22, 2008 4:31 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, 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...

Post #538309

Andy Warren

Posted Tuesday, July 22, 2008 5:35 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #538341

jim.powers

Posted Tuesday, July 22, 2008 7:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, February 04, 2013 10:00 AM
Points: 1,329, Visits: 803

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?

Post #538453

SuperDBA-207096

Posted Tuesday, July 22, 2008 7:29 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, 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.

Post #538460

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions