Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Disabling Indexes Expand / Collapse
Author
Message
Posted Monday, July 21, 2008 10:41 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 6:56 AM
Points: 6,804, Visits: 1,934
Comments posted to this topic are about the item Disabling Indexes

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #538139
Posted Monday, July 21, 2008 10:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 11:43 AM
Points: 1,683, Visits: 339
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
Posted Monday, July 21, 2008 11:00 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
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
Posted Tuesday, July 22, 2008 12:56 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,471, Visits: 1,402
Good article...:)


Post #538205
Posted Tuesday, July 22, 2008 1:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, December 7, 2014 10:54 PM
Points: 74, Visits: 522
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
Posted Tuesday, July 22, 2008 2:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, July 22, 2008 4:31 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 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
Posted Tuesday, July 22, 2008 5:35 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 6:56 AM
Points: 6,804, Visits: 1,934
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
Post #538341
Posted Tuesday, July 22, 2008 7:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:11 AM
Points: 1,330, Visits: 815
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
Posted Tuesday, July 22, 2008 7:29 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 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 »

Add to briefcase 12»»

Permissions Expand / Collapse