Checkpoints and writes that takes minutes

Question

Checkpoints and writes that takes minutes

Viewing 7 posts - 16 through 21 (of 21 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

By the way, I have my doubts that checkpointing is the "Cause" of this. It may be controlling when it happens, but even if you had no write-caching, I belieeve that this would still be happening, probably every time your Delete proc runs.

Disk write times of 500ms are unworkable in the extreme and it is very unlikely that you can provide an adequate production evironment under such conditions.

Based on these Disk writes write times, I doubt that your Delete proc should be deleting more than 100 records a minute, and probably in chunks no bigger than 4 at a time. (!!!) If you post the code ofr it, we can help you to write it so that it can run like that, but it seems like fixing the disk write performance should be your real goal.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 2

The performance issue may just be that your log started out at an initial size of 1MB-2MB, then grew from there. That can be expensive because each time it autogrows it has to do a write operation to zero-out those pages in addition to allocating that space. If the autogrow is happening then the .LDF file can become fragmented at the file system level, further decreasing performance. Also, the logfile is managed internally in units called VLF's. If you're log autogrew from 1MB to 7GB then it's likely you have hundreds or thousands of VLF's, which is more overheard. Also, an autogrow will cause other transactions to wait until it's finished.

So, it might just be a matter of shrinking your log file back down, then resizing it to an appropriate value for your situation. That would remove the file system level fragmentation, reduce the number of VLF's and prevent autogrowth operations from impacting performance.

DBCC LOGINFO will show one record for each VLF.

Reference:

Considerations for the "autogrow" and "autoshrink" settings in SQL Server

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Do you have anything else running on this server? Any antivirus or other IO filter drivers? The sec/write is way too high, but the writes/sec aren't. Doesn't make sense.

Could you also please take a look at the lazywrites/sec and the checkpoint pages/sec counters? They're both under SQL Server: Buffer manager

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Timmer-213982 SSC Rookie Points: 48 More actions · Answer 4

There is antivirus but I verified that MDF and LDF and db* are excluded network wide.

Checkpoint pages are 370-410 / sec during the "peak" periods.

Disk Writes/Sec are about the same 360-390.

Timmer-213982 SSC Rookie Points: 48 More actions · Answer 5

Lazy Writes are non existent. Max of 1.6 almost always 0.

I do allow autogrow but it's set to 1GB increments. After I moved it to C and shrunk the log file there are 70 VLFs so says DBCC LOGINFO.

Also the big MDF was set to autogrow at 10GB increments.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

Could you post the code you're running to do the deletes? How much memory does the server have and what is the system paging rate?

And then again, I might be wrong ...
David Webb

Timmer-213982 SSC Rookie Points: 48 More actions · Answer 7

The server has 8GB total and 6.5GB for SQL Server. The average pages per second is 10 over the last 24 hours. Currently 1.6.

This is Visual Foxpro. There is another NON-SQL table on another server called _tempinv and it contains the invoicenum field that is used in the delete. If I haven't issued a checkpoint in the last 60 seconds then execute one. It seems to help. There is really not much here.

And of course invoicenum is a key field in inv_detail_rtest.

DO WHILE .NOT. EOF()

talready = talready + 1

=q("DELETE FROM inv_detail_rtest WHERE invoicenum = " + STR( _tempinv.invoicenum) )

@ 14, 01 SAY " DONE DELETE"

IF DATETIME() - prevcheck > 60

@ 15,01 SAY "Checkpoint........"

=q("CHECKPOINT")

?? "Done checkpoint"

prevcheck = DATETIME()

ENDIF

IF totaldele = 0

starttime = DATETIME()

ENDIF

totaldele = totaldele + sqlrtest.calls

SKIP

@ 16, 01 SAY "****** Delete speed:" + STR( totaldele / (DATETIME() - starttime) * 60, 8) + "*******"

ENDDO