Avg.Disk Queue length

Question

Avg.Disk Queue length

rambilla4

SSCrazy Eights

Points: 8715
More actions
April 6, 2010 at 1:12 am

#139329

Hi,
We have SQL Server 2005 EE x64 with SP3 and have the storage on SAN with RAID 10 configuration
and have the below drives:
C- OS
D- Data
E- Log
T -TempDB
Z- Backup
Total disks = 5
and from the Perfmon, I'm getting the the Avg Disk Queue length as 18 .
So is calculating actual Avg.Disk Queue length as below correct?
Actual Avg.Disk Queue length = Avg.Disk Queue length/no.of disks
i.e 18/5 = 3.6
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

rambilla4 (4/6/2010)
Hi,
We have SQL Server 2005 EE x64 with SP3 and have the storage on SAN with RAID 10 configuration
and have the below drives:
C- OS
D- Data
E- Log
T -TempDB
Z- Backup
Total disks = 5
and from the Perfmon, I'm getting the the Avg Disk Queue length as 18 .
So is calculating actual Avg.Disk Queue length as below correct?
Actual Avg.Disk Queue length = Avg.Disk Queue length/no.of disks
i.e 18/5 = 3.6
Thanks

On a san, assuming it's properly configured, what you're seeing as "disks" in your OS and SQL Server should be, not a single monolithic disk, but a whole bunch of disks. So, while you have the correct concept, that your queue length needs to be distributed across the disks that you have, you need to know, not the disks that are listed, but the underlying number of disks to understand whether or not that queue length is a problem.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 2

On a san, assuming it's properly configured, what you're seeing as "disks" in your OS and SQL Server should be, not a single monolithic disk, but a whole bunch of disks. So, while you have the correct concept, that your queue length needs to be distributed across the disks that you have, you need to know, not the disks that are listed, but the underlying number of disks to understand whether or not that queue length is a problem

thanks Grant,

Could you please tell me what exact information should I get from our SAN Admin? so that I can calculate correct thresholds

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

rambilla4 (4/6/2010)
On a san, assuming it's properly configured, what you're seeing as "disks" in your OS and SQL Server should be, not a single monolithic disk, but a whole bunch of disks. So, while you have the correct concept, that your queue length needs to be distributed across the disks that you have, you need to know, not the disks that are listed, but the underlying number of disks to understand whether or not that queue length is a problem
thanks Grant,
Could you please tell me what exact information should I get from our SAN Admin? so that I can calculate correct thresholds

Generally, if you're worried about it, you just need to get an understanding of how many spindles are supporting each "disk" that you're using. That's about it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mangeshd Right there with Babe Points: 744 More actions · Answer 4

The disks that you are seeing in Windows are just volumes. The disk subsystem can have single or multiple disks to present a volume to Windows. So, if there are 2 disks in a volume and your Avg Disk Queue Length is 4 then, it means that at each disk there are 2 requests (one will be being serviced by the disk while other will be waiting).

If you know the number of disks for a volume then can get a pretty good estimation of how many IO requests have to wait on an avg in your system.

Don't limit yourself only with disk Q length; other parameters like Disk sec/read and Disk sec/write are equally important in benchmarking the disk performance.

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 5

Thank you mangeshd,

Each disk has 150 spindles in it. So what ever the value I gathered from Perfmon for Avg Disk Queue length should be divided by 150 right?

and is there any calculations required for Avg.sec/read & Avg.Sec/write counter values if the storage is SAN? or they just straight forward values & requires NO calculation (except multiply by 1000)

please clarify me

thanks

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

rambilla4 (4/6/2010)
Thank you mangeshd,
Each disk has 150 spindles in it. So what ever the value I gathered from Perfmon for Avg Disk Queue length should be divided by 150 right?
and is there any calculations required for Avg.sec/read & Avg.Sec/write counter values if the storage is SAN? or they just straight forward values & requires NO calculation (except multiply by 1000)
please clarify me
thanks

Pretty much. I don't think it works on a completely one for one basis, but it'll get you close enough to where you need to go.

I'm not aware of any other special measures needed for the san. There are a whole bunch of things you do have to watch for on san's, but you've already gotten past the first one. I've heard of volumes being created on single disks instead of across multiple disks, as it should be. I'm also aware that san's have a tendency to cache data, reporting back to the OS that the data has been saved, when it fact it's still in memory. I don't think this is something you can measure, but it is something to be aware of in a DR situation. That's all I can remember at the moment, but I'm sure there are other gotcha's to watch for.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

see my other post on a similar thread as this. ADQL is useless, use avg disk sec/read and /write. Or the sys.dm_io_virtual_file_stats DMV - even better.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

Queuing isn't the best indicator, but Avg Disk Sec/Read and Avg Disk Sec/Write could be used.

Usual recommendations for those counters are 10-50ms.

MJ

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

MANU-J. (4/8/2010)
Queuing isn't the best indicator, but Avg Disk Sec/Read and Avg Disk Sec/Write could be used.
Usual recommendations for those counters are 10-50ms.
MJ

I posted that 6 hours ago. 🙂

I don't think you can make a blanket statement (even one so wide as you do) about the 'recommended' IO stall numbers. The recommended numbers actually vary pretty widely depending on the type of file on the LUN as well as the type of IO (OLAP or OLTP or tempdb or tlog).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service