SQLServerCentral / Discuss Content Posted by Andy Warren / Article Discussions / Article Discussions by Author / Disabling Indexes / Latest Posts

RE: Disabling Indexes

Dave Mason — Wed, 26 May 2010 14:54:23 GMT

I guess you could say I'm a little late to the party. :-) But thanks, Andy! And thanks to those who have commented. You all helped me find a solution to [url=http://www.sqlservercentral.com/Forums/Topic928372-357-1.aspx][u]a problem I had[/u][/url] with bulk inserts, the bulk-logged recovery model, and minimal logging.

RE: Disabling Indexes

curious_sqldba — Thu, 18 Jun 2009 10:11:25 GMT

hello, the article is really interesting. We have log shipping set up on one of the dbs and there is also huge bulk inserts gng on, bcoz of that the size of log backups is increasing. We were following the process of dropping the indices, bulk-insert the data and then recreate the indices. if i go the other way around by disabling the indices, bulk-insert data and then re-enable, will this decrese the size of log backups? I think so yes bcoz then there is less activity than re-created the indices from scratch. Please suggest me . thanks in advance

RE: Disabling Indexes

christopher.dorch — Fri, 19 Sep 2008 09:01:49 GMT

I know this is an old article, but I figured I'd give a reason why you should disable indexes.Not all organizations have the ability to have dbs for applications and others for reporting. So, for EOM or EOY reporting, transactional indicies might cause these reports to take forever especially if there are rollups or aggregations. It's best practice to not have indexes that you don't need, but rather than drop and recreate for EOM or EOY reporting, you can disable. That way, the maintenance hit is not a constant.Yes, I know.. This is not best practice period . But sometimes, the customer has their limitations.

RE: Disabling Indexes

Nicole Bowman — Tue, 22 Jul 2008 15:58:22 GMT

Very interesting article and discussion. Thank you!

RE: Disabling Indexes

Kangana Beri — Tue, 22 Jul 2008 12:25:25 GMT

[quote][b]Andy Warren (7/22/2008)[/b][hr]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.[/quote]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!

RE: Disabling Indexes

hdillow — Tue, 22 Jul 2008 11:25:38 GMT

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.

RE: Disabling Indexes

Jeffrey Williams 3188 — Tue, 22 Jul 2008 10:49:00 GMT

[quote][b]Andy Warren (7/22/2008)[/b][hr]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.[/quote]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.

RE: Disabling Indexes

Andy Warren — Tue, 22 Jul 2008 08:58:19 GMT

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.

RE: Disabling Indexes

SuperDBA-207096 — Tue, 22 Jul 2008 07:29:27 GMT

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.

RE: Disabling Indexes

jim.powers — Tue, 22 Jul 2008 07:20:42 GMT

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?

RE: Disabling Indexes

Andy Warren — Tue, 22 Jul 2008 05:35:38 GMT

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.

RE: Disabling Indexes

SuperDBA-207096 — Tue, 22 Jul 2008 04:31:15 GMT

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...

RE: Disabling Indexes

leorajivm — Tue, 22 Jul 2008 02:45:29 GMT

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?

RE: Disabling Indexes

Chris Hamam — Tue, 22 Jul 2008 01:58:38 GMT

[quote][b]Jeffrey Williams (7/21/2008)[/b][hr]I have used this feature a lot in an ETL process where it is very useful...[/quote]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_IndexDisableRebuildGOCREATE PROC dbo.usp_IndexDisableRebuild( @pvchSchema VARCHAR(256) ,@pvchTable VARCHAR(256) ,@pvchTask VARCHAR(256) --'DISABLE' or 'REBUILD' ,@pbitDebug BIT = 0)AS/*Created By: Chris HamamCreated: 2007-07-27Purpose: Generic stored proc to disable/enable all non-clustered indexes on a table.Example: EXEC dbo.usp_IndexDisableRebuild 'dbo', 'Customers', 'REBUILD'*/SET NOCOUNT ONDECLARE @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_idWHERE i.index_id > 1 AND s.name = @pvchSchema AND o.name = @pvchTableIF @pbitDebug = 1 PRINT @sqlELSE EXEC (@sql)GO

RE: Disabling Indexes

Anipaul — Tue, 22 Jul 2008 00:56:50 GMT

Good article...:)

RE: Disabling Indexes

Jeffrey Williams 3188 — Mon, 21 Jul 2008 23:00:54 GMT

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 tables2) Disable non-clustered indexes3) Transform and load data4) Rebuild ALL indexesWorks very well...

RE: Disabling Indexes

Chris Stewart-397033 — Mon, 21 Jul 2008 22:46:56 GMT

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

Disabling Indexes

Andy Warren — Mon, 21 Jul 2008 22:41:36 GMT

Comments posted to this topic are about the item [B]Disabling Indexes[/B]