Slows system performance

Question

Slows system performance

Viewing 15 posts - 16 through 30 (of 41 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Two key points - don't shrink after load, it will only cost you next time around; and checkpoint after each COMMIT to clear completed transactions from the log.

Grant Fritchey has an excellent blog entry [/url]which may help.

^{“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

Krishna1 Hall of Fame Points: 3666 More actions · Answer 2

thanks. I will check the perfromace and the size of log after adding the checkpoint

How do u write it,

Begin tran

Commit tran

checkpoint

if i have 2 begin tran / commot tran then i have to put chekcpoint 2 times?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Krishna1 (4/10/2013)
thanks. I will check the perfromace and the size of log after adding the checkpoint
How do u write it,
Begin tran
Commit tran
checkpoint
if i have 2 begin tran / commot tran then i have to put chekcpoint 2 times?

Yes, that's correct. Successful transactions in the tran log are of no further interest to you in simple recovery mode. SQL Server will get around to checkpointing in its own good time but you don't want to wait, you want the log space to be reusable for the next batch.

^{“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

richardmgreen1 SSChampion Points: 10352 More actions · Answer 4

Just to add in my 2p-worth (after running into similar issues), if you're doing the insert/update/delete as separate transactions, have you considered using a MERGE statement to do everything in one go?

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 5

Use Batch insert/update/ delete instead of doing it in one big batch.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 7

Neeraj Dwivedi

SSCertifiable

Points: 6768

April 12, 2013 at 8:54 am

#1606011

My mistake. Missed that part.

Krishna1 Hall of Fame Points: 3666 More actions · Answer 8

After addind checkpoint also its increading the log file size

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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

Krishna1 Hall of Fame Points: 3666 More actions · Answer 10

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?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

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

Krishna1 Hall of Fame Points: 3666 More actions · Answer 12

even if i am using simple recovery model it will log it and increase the .ldf file size?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

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

Krishna1 Hall of Fame Points: 3666 More actions · Answer 14

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?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

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