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.
- Start > run> perfmon.
- Expand counter “Logical Disk”
- Select “Avg Disk/Sec” choose Read, Write or Transfer depending on what you want to monitor. I normally choose Transfer.
- Select the drive where you have the SQL files placed
- Hit Add>> OK
- 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.
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
