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

Disabling Indexes - SQL School Video Expand / Collapse
Author
Message
Posted Tuesday, November 25, 2008 10:03 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 7:03 AM
Points: 6,779, Visits: 1,866
Comments posted to this topic are about the item Disabling Indexes - SQL School Video

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #608842
Posted Tuesday, January 13, 2009 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 13, 2009 4:07 PM
Points: 1, Visits: 2
EXCELLENT video presentation! The speed of the presenter, both in speech, and in demonstration was tremendous! Especially the parts where he would give simple reminders, of what he's covered, to keep everyone up to speed. Good job!!
Post #635776
Posted Tuesday, January 13, 2009 3:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 11, 2010 12:52 PM
Points: 2, Visits: 9
Great video!

Does anyone know if disabling the indexes and rebuilding them after the bulk insert is a more efficient method than dropping the indexes and recreating them after the insert?

I'm loading several million rows and with the drop/create method it takes up to 5 hours to recreate the indexes.

Thank you!
Post #635874
Posted Tuesday, January 13, 2009 3:42 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
I don't think there would be much difference, as behind the scenes you essentially have a lock, logged page deallocation, and index build + logging in both cases.

Best way is to try it and see. At the very least you'll be happy to lose all that DDL code you've been using in your existing process. :D



~BOT




Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #635891
Posted Wednesday, January 14, 2009 8:47 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
Great Video...:)


Post #636315
Posted Wednesday, January 14, 2009 9:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 42,469, Visits: 35,540
plreeter (1/13/2009)
Does anyone know if disabling the indexes and rebuilding them after the bulk insert is a more efficient method than dropping the indexes and recreating them after the insert?


It'll be the same. The index b-tree still needs to be built in both cases. The only difference between disabled and dropped is that disabled doesn't remove the metadata



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #636338
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse