SQL Server Performance

Checkpoint - posting 16 pages at one time?

Discussion in 'Performance Tuning for Hardware Configurations' started by bertcord, Jun 9, 2005.

  1. bertcord New Member

    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
  2. joechang New Member

    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
  3. bertcord New Member

    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

Share This Page