[dba-SQLServer] SQL Log file usage

Michael Maddison michael at ddisolutions.com.au
Thu Nov 1 18:42:37 CDT 2012


Guys,

IIRC you can disable indexes before insert and enable after.
I don't recall the command but it's there somewhere :-)

Cheers

Michael M

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Friday, 2 November 2012 6:30 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] SQL Log file usage

As far as I know, it is always faster, a lot faster, to insert data into
tables without indexes or keys. (Knew a system that took a week to
insert all the data but removing the keys first and inserting the data
took less than 3 hours) 

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, November 01, 2012 11:22 AM
To: Access Developers discussion and problem solving; Sqlserver-Dba
Subject: [dba-SQLServer] SQL Log file usage

I attempted to insert 150 million records into a table, from one
database to another.  The destination table was empty but had a bunch of
indexes on it.  I ran out of room on the log file disk and the process
hung.

I ended up stripping off all of the indexes except the PK clustered
index and the insert used less than 1/2 of the previous log file size
for the insert.  Furthermore the build of the indexes as a second step
is essentially not using the log file at all.

So the whole argument about "which is faster" also needs to include a
component about log file usage.  I have no idea which would have been
faster - with or without the indexes already in place - but in this case
at least stripping off the indexes and building them after the fact
seems to make the whole process work without a (log file size) hitch.

--
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list