July 16, 2018 at 9:15 am
We're about to add databases to a SQL database server. Decided to run perfmon, make sure there were no underlying issues. Noticed the disk queue length was high on a regular basis ('74' to '299'). Dialing down a bit also noticed that “avg disk sec / read”, “avg disk sec / write” + “avg disk sec / transfer” – 2 of the 3 counters are high. We target 15 milliseconds (.015) to 20 milliseconds. Instead we see a much higher value - sometimes as high as 2,000 milliseconds (2). No complaints from end users, applications are fine (as far as I know).
Ran again this morning:
Avg. Disk sec/Read 0.008 0 0.092
Avg. Disk sec/Transfer 0.003 0 0.087
Avg. Disk sec/Write 0.003 0 0.038
Mean numbers have been fine, maximum has me concerned. Spoke to the storage administrator, asked him to review system logs. Any thoughts on this? Is this something we can ignore? Comments are appreciated...
July 16, 2018 at 12:45 pm
disk Que length should not be more than 2 per spindle.. may be its time to add disk to SAN aggregate(s)
Current Disk Queue Length
This counter will tell us how far behind the disk currently is running. i.e. how many outstanding IO requests are queued up and awaiting service from the disk. A good rule of thumb here is that you don't want to see any more than 2 outstanding requests per spindle. (Note that disk partitions can be physically made up of more than one spindle. So we aren't talking about 2 requests per drive but rather spindles. If there are are 4 underlying spindles which comprise the logical drive G$, then we should see no more than 8 outstanding requests.)
https://www.sqltuners.net/blog/13-05-16/Measuring_Disk_IO_performance_for_SQL_Servers.aspx
August 17, 2018 at 8:13 am
Posted last month about this.
Ran Perfmon yesterday at 10 AM. Duration 3600 seconds // interval 1 second (virtual machine)
Disk Queue Length: 99 - asked the ESX Administrator to confirm the number of spindles, he indicated there are 3 disk arrays, 23 platters each. 69. Queue length has been higher. Note preference is to separate ldf, mdf and tempdb, they are not. That's not my call...
Subsequently noted the following in the "Physical Disk Average Second Counters"
Avg Disk sec/Read c: .04 -- c: drive is for the OS
Avg Disk sec/Transfer .063
Avg Disk sec/Write . 063
Avg Disk sec/Read e: .125 -- e: drive is used for SQL
Avg Disk sec/Transfer .055
Avg Disk sec/Write .055
Avg Disk sec/Read f: .005 -- f: drive is used for full and transaction log backups
Avg Disk sec/Transfer .014
Avg Disk sec/Write .015
These are the actual Maximum counts. Does the number of spindles apply with them? If so we're fine. If not we have issues. Comments / URLs are appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply