DISABLE / REBUILD Index on Large Ingest

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

DISABLE / REBUILD Index on Large Ingest

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-01-25 : 13:21:15

Greetings,

I will be ingesting a sum of billions of rows into multiple tables from xml.

To speed up the ingesting process I would like to disable nonclustered indexes before ingest and rebuild them after ingest.

I plan to use the following query to build my script that will get things ready for me. Anyone ever used such an approach. If so I would appreciate some input pro con regarding this approach.

Thanks!


SELECT  'ALTER INDEX [' +  sys.indexes.name + '] ON [' + sys.objects.name + '] DISABLE;' +CHAR(13)+CHAR(10) as EXECUTE_STATEMENT
 FROM    sys.indexes         JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id 
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'     
  AND sys.objects.type_desc = 'USER_TABLE' 
  AND sys.objects.name NOT LIKE 'sys%'



SELECT 'ALTER INDEX [' +  sys.indexes.name + '] ON ' + sys.objects.name + ' REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF ) ;' +CHAR(13)+CHAR(10) as EXECUTE_STATEMENT

FROM    sys.indexes         JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id 
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'   
AND sys.objects.type_desc = 'USER_TABLE' 
AND sys.objects.name NOT LIKE 'sys%'

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 13:36:43

quote:
Originally posted by yosiasz

Anyone ever used such an approach.

I normally DROP non-clustered indexes before large Imports, and the re-CREATE afterwards. Not tried Disable / Rebuild before ...

Aren't you making a bit of an assumption that ALL your indexes will be "PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF" ?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-01-25 : 13:45:43

I could try the drop and recreate approach as well. I thought that would take a extended period of time for huge tables. Could you please comment further on my assumption.

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 13:50:10

quote:
Originally posted by yosiasz
I thought that would take a extended period of time for huge tables

If the index is dropped, or disabled, I think the "act" of reviving it is the same

If you REBUILD the index then perhaps??? SQL will log the removal of all the pages in the index, and the creating of all the new pages, whereas perhaps??? a DROP INDEX is more minimally logged?

So I think there might be a difference in Log Space Used, but in terms of CPU I doubt there is any significant difference.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-01-25 : 14:06:08

ok I see what you mean. My database is in SIMPLE mode I assume that means minimal logging

BOL


No log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

Thanks Kristen!

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 14:10:09

"My database is in SIMPLE mode I assume that means minimal logging"

Sort of!

Each transaction will be logged. Its possible that a DROP index and then (later) a CREATE INDEX will use less log space than the "Rebuild" (after a Disable)

That will ONLY matter if the Log Space for that single transaction is bigger than any previous transaction you have ever had - as it would cause the Log File to be extended.

I doubt that that is relevant, so go with whatever seems the most suitable for you

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-25 : 15:07:34

quote:
Originally posted by Kristen

Its possible that a DROP index and then (later) a CREATE INDEX will use less log space than the "Rebuild" (after a Disable)

No. It would likely use more, but we're only talking a row or two of logging (drop has to remove the metadata and log the removal of the metadata, disable does not)

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources