Checkpoint – posting 16 pages at one time? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Checkpoint – posting 16 pages at one time?

On my SCSI based systems I run into bottlenecks based on MAX IOPS, before I get to a limitation of data load. One of the servers I manage has 4 external arrays configured in RAID10 with 15 drives each. Each drive in the array can handle about 106 Random IOPS and 250Seq IOPS. So for the array as a total it can handle about 1,500 IOPS. I am using Ultra 320 SCSI so it should handle 320MBs. Below is a sample of data when I have heavy queuing. The queue also coincides with the checkpoint process. I used fn_virtualfilestats to track the IO at the file level. Logical IOPS – 1281.90
LogicalBytesPerSecond – 10663000
LogicalDataPerSecond – 10.17MB
AVGIoSize – 9.3kb Why is my Average size per IO so low? Using performance monitoring I can see that the checkpoint process is generating the queuing. My understanding of the checkpoint process is that it attempts to post 16 pages in a single IO http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx Any ides of why I am not seeing this? Bert
it is also necessary to record the avg queue depth and avg disk sec/transfer during the above test and note the size of the file relative to the entire amount of space on the array(assuming one array for the entire set of disks),
otherwise, it is possible to generate a wide range of IOPS
the value above are reasonable for queue depth 15
The avg queue depth average is around 100 during the spike. I am not using performanc emontir to gater the IO stats but fn_virtualfilestats. I run during a ten minute period taking a measurement every ten seconds. So for a ten second period I can see number of
READS
WRITEs
BytesWritten
BytesRead The numbers I gave about are from one for one of the 10 second intervals. It is the sum of all files on the drive I am looking at. So for the ten seconds above I was able to calculate the Avg Disk Second transfer. Could you explain the following statement
"note the size of the file relative to the entire amount of space on the array" I have 6 SQL files of varying sizes that account for about 906GB of space. If you are interested in the code that I am using to capture these stats let me know. I have found it very helpful in tracking down my most active files. bert
]]>