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 «««12345»»»

Slows system performance Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 8:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
DevDB (4/12/2013)
Use Batch insert/update/ delete instead of doing it in one big batch.


The OP is batching.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1441717
Posted Friday, April 12, 2013 8:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 835, Visits: 1,191
My mistake. Missed that part.
Post #1441755
Posted Monday, April 15, 2013 3:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
After addind checkpoint also its increading the log file size
Post #1442206
Posted Monday, April 15, 2013 3:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
Krishna1 (4/15/2013)
After addind checkpoint also its increading the log file size


Even with batching, simple recovery mode and checkpointing, there will be a minimum log space requirement for each batch. If your log size is less than this, then you should expect the file to grow. Have you checked the size and usage of the log file? Here's the simplest way:
DBCC SQLPERF ( LOGSPACE )


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1442222
Posted Monday, April 15, 2013 3:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
the script given gives me the size of the log file and used space. I would line to know why delete operation should increase the log file?
Post #1442227
Posted Monday, April 15, 2013 3:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
Krishna1 (4/15/2013)
the script given gives me the size of the log file and used space. I would line to know why delete operation should increase the log file?


Deletes are fully logged. If something occurs which prevents the transaction from completing then the whole transaction would be rolled back using information from the tran log.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1442228
Posted Monday, April 15, 2013 4:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
even if i am using simple recovery model it will log it and increase the .ldf file size?
Post #1442232
Posted Monday, April 15, 2013 4:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
If the log file has insufficient free space to handle the logging data from a batch of deletes, then yes the file will grow. Are you manually changing the size of the log file at any point?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1442234
Posted Monday, April 15, 2013 4:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
No am not manualy chaging. I have set log file size groth as 500MB and i am deleting almost 3 million rows. I has
i have just deleted 1.5 milion rows as it as grown by 500*11 times.

I have 5 indexes on this tables. Is this a cuse of log file size increase?
Post #1442236
Posted Monday, April 15, 2013 4:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
How big is the data file (and any indexes)?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1442237
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse