Version 16

    In this article I have just included a quick performance check for SQL disks . What can be quickly check to get an idea and the logic behind considering the configured environment  .

    As technology is growing in the area of high end storage and now a days it’s almost impossible / difficult  to find out an accurate metric and calculation if this is really good or bad configuration .

     

    You may have a range of disk configurations as each company has their own configuration standards they might heave for existing running SQL server with their own  philosophy on serving storage to the production servers where they just placed the Orion DB .

     

    Customer may have direct attached, PCIe, iSCSI and Fiber channel to a wide range of SAN equipment. May have various disk types (SCSI, SSD, SATA) as well as RAID applications and you have quite a complex set of information to work with in rooting out performance issues.  So if i will start covering everything here this  topic will never end when encompassing all of those varying configurations .

     

    As an example  customer DBA monitor perfmon and found the disk queue length is 10 on drive E:  certainly this is a high number,  Now If the E: is one physical disk, yes, you have a problem.

    If the E: is a logical disk composed of 8 physical disks, then it’s not an issue  Why?  8 disks x 2 queue length = 16 >> DBA sees 10 so 10 is less than 16 matching our threshold  therefor this is not an issue.

     

    Problem with queue length Orion user will be not be  able to provide and understand disk map how the SAN is configured to see and understand all the logical volumes. How many disks are participating in with vendor configuration example as EMC hyper volume.

     

    Such as an example what a mapping look like.  Each of the “73” are 73G disk drives. Multiple drives configured for the LUN this is RAID 0+1  see 2 73G drives configured up a 59.39G formatted capacity):

    Therefor Without a map disk queue length is meaningless and unable to help you finding the root cause .

     

    RG0, R1, 60.39 GB

    RG1, R1, 60.39 GB

    RG2, R1, 60.39 GB

    RG3, R1, 66.61 GB

    73

    73

    73

    73

    73

    73

    73

    73

    00_00

    00_01

    00_02

    00_03

    00_04

    00_05

    00_06

    00_07

    Private System Partition

     

    LUN 30 (1 GB), Unallocated (65.61 GB)

    LUN 0 (1 GB), Unallocated (59.39 GB)

    LUN 10 (1 GB), Unallocated (59.39 GB)

    LUN 20 (1 GB), Unallocated (59.39 GB)

     

    Yes to check bad performance you can check the queue length if its grater than 2 for any disk . But if  you have a SAN  you will not have any idea how many are being used for your drive where you have the SQL files stored .

     

    So i be ideally have a quick look on the actual system with some basic steps and get an idea what's is going on to the disks averagely to monitor the numbers and compare with the chart as below.

    1. Start > run>   perfmon.
    2. Expand counter “Logical Disk
    3. Select “Avg Disk/Sec”  choose Read, Write or Transfer depending on what you want to monitor. I normally choose Transfer.
    4. Select the drive where you have the SQL files placed
    5. Hit Add>> OK
    6. Let it run for 10 min and check

     

     

     

    Please Note:  .010  =  10 milliseconds

     

    Lets now check what value we have according to the table 

     

    10 ms

     

    Good

    10 ms

    20 ms

    Reasonable

    20 ms

    50 ms

    Busy

    50 ms +

     

    Bad

     

     

    Please Note:

    Based on few high end  Solarwinds customers feedback

    If you have SAN Storage configured and have 1 ms response times is a not a good indication must be incorrectly configured SAN storage.

    Contact your Storage team they might have to check the HBA or other factor involved , They will have there own performance software provided by the vendor where you can see more in depth details what's actually happening.

     

    1 ms

     

    Bad

     

    I would certainly agree there are many other factors need to consider however it would be a very long discussion could have on this however i would just keep my  focus on the basic check and understanding on this as there are plenty  of space available for  discussion  and arguments  for  SQL and Storage experts.

     

    Please Note again checking the disk performance is out of Solarwinds support and for more details please do consult with your SQL and Storage team .

    *I have complied the above information from multiple referenced posts