What will be good/accepted threshold values for Average Disk Seconds/Read, Average Disk Seconds/write

Question

Post reply

What will be good/accepted threshold values for Average Disk Seconds/Read, Average Disk Seconds/write

gary1

SSC-Insane

Points: 21152
More actions
March 3, 2010 at 5:08 pm

#143381

Hi,
We have SQL Server 2005 EE x64 with SP3 and all the drives are from SAN & all the drives are RAID 10. I would like to calculate Average Disk Seconds/Read & Average Disk Seconds/write to know disk performance.
What will be good/accepted threshold values for:
Average Disk Seconds/Read
Average Disk Seconds/write
Is there any special method to calculate the above values if the drives are from SAN & RAID 10?
thanks

Viewing 15 posts - 1 through 14 (of 14 total)

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Check out this article for some tips

http://msdn.microsoft.com/en-us/library/cc966412.aspx

Each environment will be different. You should test your system, baseline it, and then trend it over time.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

For sec/read and sec/write, under 10ms is good. 10-20ms is average. Over 50ms is bad.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gary1 SSC-Insane Points: 21152 More actions · Answer 3

Thanks,

I have multiplied the PhysicalDisk(1 D:)\Avg. Disk sec/Read & Avg. Disk sec/Write by 1000 to get the value in ms and most of the values are with in 15 ms -

:-):-)

but what should the accepted value for Avg. Disk Queue Length?

what are the units for Avg. Disk Queue Length

do I need to multiply Avg. Disk Queue Length value with 1000 similar to Disk sec/Read & Disk sec/Write?

I'm getting the below values for Avg. Disk Queue Length for the Backup drive. So do I need to multiply these valuse by 1000? or I need to take the values as it is from perfmon counters?

0.213329237

0.206662699

0.179996544

23.79954305

0.166663467

0.173330005

0.179996544

0.186663083

0.166663467

please advice

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

gmamata7 (3/4/2010)
but what should the accepted value for Avg. Disk Queue Length?

No way to say. Depends completely on your IO subsystem and, if you've got a SAN, that counter is near-impossible to interpret.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gary1 SSC-Insane Points: 21152 More actions · Answer 5

thanks Gail,

No way to say. Depends completely on your IO subsystem and, if you've got a SAN, that counter is near-impossible to interpret

Is there a way to find the correct values for Avg.Disk Queue length from SAN side for a particular disk (in my case, for the Z drive (backup drive))?

thanks

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

There's too much between the OS and the disks to get a meaningful threshold for queue length when there's a SAN. It's one of those counter where a value far above average for your server is bad, but that's all that can really be said.

The podcast mentioned in this post may be of interest

http://sqlinthewild.co.za/index.php/2009/02/14/do-you-know-how-to-use-perfmon/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gary1 SSC-Insane Points: 21152 More actions · Answer 7

thank you,

If the storage is from SAN, the readings from Perfmon for the below counters will be the correct values unlike Avg Disk Queue length?

Average Disk Seconds/Read

Average Disk Seconds/write

thanks

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

I never said the avg disk queue length was incorrect. It's not. All perfmon counters report accurate, correct values.

The avg queue length is near-impossible to interpret on a SAN. There's no value that's good or bad, you have to compare with what's normal for that server and drive. You cannot, with a SAN, look at a single value of queue length and say if it's good or bad.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SD1999 Hall of Fame Points: 3140 More actions · Answer 9

I think, as per microsoft, Disk Queue length should not exceed 0.02 seconds

gary1 SSC-Insane Points: 21152 More actions · Answer 10

thank you,

I understand that perfmon gives accurate values but when we have SAN it's near impossible to interpret correct values for Avg.Disk Queue length.

Question:

Is this applicable to Avg. Disk sec/Read & Avg. Disk sec/Write counters too. i.e near impossible to interpret correct values from SAN?

thanks

SD1999 Hall of Fame Points: 3140 More actions · Answer 11

we also have all our sqls running on SANs....

I've been using perfmon, quest Perf Analysis, Spotlight and most importantly - HP SE (SANs monitoring\management soft)

they are all reporting the same perf values for the counters you spcified.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

SD1999 (3/5/2010)
I think, as per microsoft, Disk Queue length should not exceed 0.02 seconds

Queue measures the number of things waiting. Hence it's an integer value and it's a count of things, not a time. You're thinking of the sec/read or sec/write that should not exceed 0.02 seconds.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

gmamata7 (3/5/2010)
Is this applicable to Avg. Disk sec/Read & Avg. Disk sec/Write counters too. i.e near impossible to interpret correct values from SAN?

If it was, do you think I would have given you the values that I gave in my first post in this thread?

I'm guessing that you haven't listened to the podcast that I recommended. I do advice that you do so.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SD1999 Hall of Fame Points: 3140 More actions · Answer 14

Queue measures the number of things waiting. Hence it's an integer value and it's a count of things, not a time. You're thinking of the sec/read or sec/write that should not exceed 0.02 seconds.[/quote]

Oops, you are absolutely right. I was talking about latency for sec\read , sec\write