June 3, 2009 at 11:18 am
Avg disk sec read
0.00015
0.001916667
0.291133333
Avg Disk Sec Write
0.003127594
0.345726198
0.000120349
0.0001653
What i read is to look for this
Avg Disk Sec/ReadTime in second requests read of data from disk < 11-15ms
Avg Disk Sec/WriteTime in second request write of data to disk < 12 ms
Cannot figure out what 11-15 ms is in decimal to compare the columns above with.
is it 0.11 and 0.12.
June 3, 2009 at 1:46 pm
The values returned from perform are seconds/read. So to get to milliseconds/read, multiply the value by 1000.
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
June 3, 2009 at 9:15 pm
So take my results * 1000....why the 1000 sorry being dumb over here.
Avg Disk Sec Write
0.0031275943.127594
0.345726198345.726198
0.0001203490.120349
0.00016530.1653
Hmmmm the 345 seconds is a big red alarm if < 12 ms is something to measure against.
I have found this out
Raid 0 -- I/Os per disk = (reads + writes) / number of disks
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks
(You can use the above RAID calculations to determine the I/Os per disk. 120 reads + (4 * 150 writes) / 4 physical disks = 180 I/Os per disk. This is over the general rule of 125 I/Os per disk capacity. This system has a disk bottleneck.
Do i need to do the above SAN calculations / disks on the above also.
So if i got say DISK S attached on Server which is from SAN. Would i need to know how many disks make up the DISK S?
June 4, 2009 at 12:35 am
TRACEY (6/3/2009)
So take my results * 1000....why the 1000 sorry being dumb over here.
Because 1 second = 1000 milliseconds
Hmmmm the 345 seconds is a big red alarm if < 12 ms is something to measure against.
Not 345 seconds. 0.345 seconds (the original value). Multiply by 1000 to give you milliseconds means that the value is 345 milliseconds. Still far above the 12 millisecond recommendation
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
June 4, 2009 at 6:58 am
345 milliseconds.
So that is not < 12 ms so leading me to disk problem here. (Need to do this for a good week) and see if it was just some fluke spike.
Do i need to determine RAID and do (read+write)/no of disks or just accept the 345 ms.
June 4, 2009 at 7:30 am
TRACEY (6/4/2009)
345 milliseconds.So that is not < 12 ms so leading me to disk problem here.
Where did the less than 12 come from? With avg sec/read and avg sec/write, higher values are bad and lower are good. 345 is definitely greater than 12, so it may be something to worry about. A once-off spike, maybe not. Sustained, absolutely yes.
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
June 4, 2009 at 6:16 pm
The 12 i think i found on some thread what to check for
See little confused if i have to do these calcuations too? with the avg results.
Raid 0 -- I/Os per disk = (reads + writes) / number of disks
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks
(You can use the above RAID calculations to determine the I/Os per disk. 120 reads + (4 * 150 writes) / 4 physical disks = 180 I/Os per disk. This is over the general rule of 125 I/Os per disk capacity. This system has a disk bottleneck.
June 4, 2009 at 10:45 pm
TRACEY (6/4/2009)
The 12 i think i found on some thread what to check for
Yes, but the check is always for > 12, not <12
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
June 5, 2009 at 7:23 am
Thanks for your guidance i be away now measuring like crazy.
June 5, 2009 at 8:41 am
Gail is right - if 354 and 291 milliseconds are sustained values for writes/reads then you have a serious problem. If they occurred during a 2 minute period of very heavy activity and then dropped back down then no biggie at all.
Oh, and there are no other calculations required for this type of analysis - those metrics are how long it takes from SQL Server requesting the IO to when the OS responds "done". Number of spindles, raid type, SAN/no SAN, etc are all irrelevant here.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 5, 2009 at 7:31 pm
Got it ignore all this
Raid 0 -- I/Os per disk = (reads + writes) / number of disks
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks
---take my 345 seconds.
I will do a week statistics checking and see what lovely graph i get.
Hmmm SAN guy told me nothing wrong......hmmm perhaps i do my week statistics then ask for his.
Thank you for the clarification on the raid part because i was just about to add that in.
345 .... scary..!
June 11, 2009 at 10:26 am
Finally got to the statistics
AVG DISK SEC/READ --- This disk is my SQL Data Files
Took the results * 1000 to get the ms
So this is very bad these high amounts
0.197228077197.228077
0.118379674118.379674
0.151357274151.357274
0.199206632199.206632
0.198453621198.453621
0.194371209194.371209
0.194138768194.138768
0.184879679184.879679
0.117671634117.671634
0.175893172175.893172
0.181828998181.828998
0.182512148182.512148
0.155786098155.786098
0.21045836210.45836
0.186691106186.691106
0.188536632188.536632
0.185884311185.884311
0.190631175190.631175
0.202139103202.139103
0.187448923187.448923
This is a good test to find out about your disks....took me a while but now i run this when people say things are slow so i can determine if the SAN Disks are overloaded.
Last week the results were under 20 ms which is good so why they went up crazy to these figures is another question that remains to be answered.
I will be monitoring this for a while
Thanks all for your help
June 2, 2010 at 12:41 pm
I have been trying to understand this issue as well. Looking all over webdom I find anywhere from 12 milliseconds to 20 milliseconds is to measure against but what exactly do you do when it does consistently exceed this threshold? Is this just a hardware disk limitation or can you do something from the database side to "fix" it?
July 7, 2010 at 8:13 am
some things can be done to help.
one thing i look at is how many indexes there are and if they set to auto-update stats. I have found some systems that have some worthless indexes killing the system because a SQL update statement causes them all to start updating their stats.
of course, that type of troubleshooting is for the most patient of dba's 😛
July 7, 2010 at 8:27 am
Thanks, I hadn't thought of something like that. In my case it is not very regular and happens only infrequently but I was just trying to get a handle on what to do if it does become regular.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply