July 28, 2008 at 11:21 am
I have read some other threads but no success. Quad 3.6Mhz, 6.5GB for SQL, 4 x 146GB 15k, RAID 5 array dedicated to SQL ONLY. SQL Enterprise Edition and Windows 2003 Server. AWE and memory configuration is correct. Everything has been DBCC'd and ALL indexes have been rebuilt in the last week.
I'm deleting millions of rows of history from a table in a large mission critical database. I started with 110 million to delete and I'm down to 68 million to delete. I tried copying out 100,000 rows at a time to a new table then I would just drop the original table but the writes into the new table cause the same problem. Current Disk Queue Length spikes and won't service other writes for minutes at a time.
I have broken the delete down to customer invoice by customer invoice so it deletes 30 to 5,000 invoice rows at a time (depends on the size of the customer). I have had to put a homegrown performance counter in the delete application and when the deletes per minute get too high I pause the application for a 10 seconds then continue until the counter get's back down to 1,000 rows deleted per minute.
If I let it run non-stop, other applications have to wait minutes to get serviced. The Current Disk Queue length hits 280-300 for 2 or 3 MINUTES at a time and in a 10 minute period the Queue will spike 2 or 3 times. During the spikes nothing else gets serviced. There is no transaction logging on this database.
There is another automated 24 hour per day process that loads new invoice rows every 30 minutes or so and I have seen times when that application will sit for 4 or 5 hours before a 4,000 row insert completes if I let the delete application run non-stop.
I have verified that it's checkpointing. Is there a way to flag this database or application to not cache writes? Or to increase the checkpointing frequency so that the Queue would be smaller and write much quicker?
The issue with not being able to service other small queries is UNACCEPTABLE. If the performance of the other applications slow down from a 1 second response to a 2 second response that's fine, but 1 second to 3 MINUTES is another story. These are real time customer service related applications and I won't accept a customer service rep saying "the computers are REALLY slow today" to a customer of mine.
This is acting like a single user server. If one user can create so much disk writing to stop other applications from functioning then this is very concerning.
Any ideas? I've been googling, testing and monitoring this for days.
July 28, 2008 at 1:27 pm
Whats the file and disk configuration, including TempDB? Sounds like you may have your log files on the same drives as the data files. Also, raid 5 is not a good performer for SQL Server, especially the log files.
[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]
July 28, 2008 at 1:58 pm
What do the following perfmon counters look like?
Physical disk Avg sec/read (break down for each disk)
Physical disk Avg sec/write (break down for each disk)
Physical disk reads/sec (break down for each disk)
Physical disk writes/sec (break down for each disk)
Physical disk % idle time (break down for each disk)
What does your disk config look like (how many arrays, how many disks/array, what's on each array)?
Check the RAID logs, make sure none of the array are degraded.
What's the schema of the table that you're deleting from? How many indexes, how many foreign keys?
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
July 28, 2008 at 3:52 pm
There are no log files. It is set to simple recovery. The table has NO foreign keys, 6 indexes though. A 10 character primary key, and the other indexes are all single field, 2 char, 10 char, 8 char, bigint, 5 char.
tempDB is on C, which is a mirrored 70GB 15K drive. There is ONE array, it's RAID 5 is only SQL data. It's 4 x 146GB 15k Ultra320 drives. There is nothing degraded in the box. Hardware is fine.
Disk Writes/Sec peak out at 416 when it's checkpointing otherwise it's 0
Disk Reads/Sec are 5 during peak but averages 70
Avg. Disk Sec/write 0.5 during peak and 0.005 otherwise
Avg. Disk Sec/read 0.013 during peak and 0.009 otherwise
July 28, 2008 at 5:20 pm
Timmer (7/28/2008)
There are no log files. It is set to simple recovery.
Even in Simple mode, you must have them, they are not optional.
tempDB is on C, which is a mirrored 70GB 15K drive. There is ONE array, it's RAID 5 is only SQL data. It's 4 x 146GB 15k Ultra320 drives.
The question still remains: Where are your database log files? If they are on the raid with your database MDF's, then that will be a problem and can certainly lead to situations like the one that you are currently in.
[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]
July 28, 2008 at 6:05 pm
It's logging everything. Simple mode just means that the log automatically truncates at each checkpoint, when it can be sure the dirty data pages have been written to disk. Ideally, you'd have the data, the logs, and the indexes on different spindles to minimize contention. As it sits now, all your raid disks need to be touched multiple times for each atomic change. The other thing that might slow things down would be the presence of a trigger on any of the tables where data is being updated or deleted.
July 28, 2008 at 6:21 pm
Thanks for the clear explanation on the log files and the checkpoints. That makes a ton of sense.
Sorry, I thought I was clear about the disks. C: is a 76GB 15k disk by itself, tempDB is there. It is mirrored. E: is the RAID 5 array that has the MDF's. The log files on are E: also.
Should they be moved to C: and how big do they need to be? I don't want to run out of space on C:
Also, there are no triggers anywhere.
July 28, 2008 at 6:32 pm
Timmer (7/28/2008)
Sorry, I thought I was clear about the disks. C: is a 76GB 15k disk by itself, tempDB is there. It is mirrored. E: is the RAID 5 array that has the MDF's. The log files on are E: also.Should they be moved to C: and how big do they need to be? I don't want to run out of space on C:
Yes, they should be moved to C:. I do not have nearly enough information to tell you how big they should be, but probably the same size that they already are.
[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]
July 28, 2008 at 6:33 pm
The LDF is 7 GB. The MDF is 350GB. Does that mean 7GB is the size it needs to be or is 7GB the biggest it has EVER needed? Can I truncate / shrink that down and let it grow 1GB at a time to see if that's the current proper size?
My point is if I can get it down to 4GB or so then I don't have a problem moving it to C: until a I upgrade the C: drives to bigger disks.
July 28, 2008 at 6:35 pm
Let me amend that: the log files should be moved to C:, if you cannot get another mirrored disk set to put them on. That would be highly preferrable.
[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]
July 28, 2008 at 6:41 pm
Timmer (7/28/2008)
The LDF is 7 GB. The MDF is 350GB. Does that mean 7GB is the size it needs to be or is 7GB the biggest it has EVER needed?
Sorry, I cannot tell from here. It really depends: it is normally assumed that that is the size that it needs to be, but it is possible that this is the size that it got pushed to for some big load operation that was a one-time event.
Can I truncate / shrink that down and let it grow 1GB at a time to see if that's the current proper size?
You can try it, but there is some risk. If it needs to grow beyond what you can give it on C:, then you will have to have a plan to deal with that. Adding a second log file back on your data disk can get you out of a tight jam, but they can be a real pain to get rid of later on.
[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]
July 29, 2008 at 3:56 am
At this point I would strongly suggest that you look at getting a second array for the log files. 2 drives mirrored should be adequate (though RAID 10 is the ideal)
sec/read of 0.5 is far, far above recommended. It should be 10-50 ms.
Also, while it's not a trivial operation, moving the RAID 5 array to RAID 10 will probably see an improvement in disk throughput. Raid 5 has about the slowest writes of any of the common raid levels.
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
July 29, 2008 at 9:57 am
I was able to shrink the log file down to 1.5GB and moved it to C: Just to be clear C: is where I put tempDB and the LDF files. E: has the MDF files.
There is NO difference. I started the delete application again and it runs the same way.
I checked % idle on C: and it's 98% idle at it's worst. E: is still peaking out during checkpoints and the checkpoints still stop other small request from completing right away.
Is there a way to make the checkpoints write more often but in smaller amounts? As a general concept, caching writes only works if the writes are small and there is idle time to complete the writes. I'm trying to delete at the speed of the disk and still have the interactive processes get serviced when needed.
If there was NO queue at all then the interactive requests wouldn't have to wait 2 minutes for the queue to empty and get serviced.
July 29, 2008 at 10:58 am
There is a command to force a checkpoint: CHECKPOINT
CHECKPOINT permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles, and are not transferable.
Worth a try
July 29, 2008 at 11:05 am
Checkpointing is controlled by the Recovery Interval setting which is usually 60 seconds. What is yours set to? Microsoft does not recommend raisning this value because it can lead to very "stop-and-start" type performance. If it has been raised, this may be the cause of your problems.
[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]
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply