October 10, 2006 at 1:44 am
I'd just observe that with the adoption of SANs ( and NAS - although I've not used this ) that disk queue and disk time are not very useful.
The only real counter(s) of value when using SAN disks ( and I think DAS too ) are the i/o completion time counters and the transfers counters. I've used the i/o completion time counters to succesfully problem shoot a SAN and if you do your maths you'll know how many i/o's your array(s) / disk(s) will support. I usually find disk idle time is better than disk time too.
I've never had much luck with network bandwidth counters, I was advised that measurements of the card stats are rarely useful as they won't show other contention or usage on the overall network - I'm not so strong on this area - , typically in n'tier environments there are often other factors at play.
ps don't forget "Sql server 2000 performance tuning - technical reference" ms press , a worthy read.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 10, 2006 at 6:57 am
I beg to differ with you Colin. However remember that it is a max of 3 per disk in a diskset (per MS.) I would also advise the use of 'Current Disk Queue' not average. Obviously if your Avg is through the roof then you have identified an issue but Current will give you the point in time view of what is going on. You can correlate a lot of things that way. If your database drive Current Disk queue is 125 on a R5 (4 disks) you are at 10 times the acceptable level. If during this time all of your queries are running at 0 ms then it is not appearing to be a related issue. If however your queries are all running several minutes or more then this could correlate. Another thing to keep in mind is that there are more factors than disk queue and query performance. Is all of your work happening at one time that could be spread out? Is there blocking? Does the machine have enough memory for the current used dataset?
Performance monitoring can be lengthy and provide you with many headaches, however it can also provide you with the data needed to rationalize a large investment or to prove to your Network Ops team that the bottleneck in the users percieved performance problems is not on your database server but somewhere beyond it.
October 10, 2006 at 9:11 am
It depends if you're run perfmon against a SAN disk subsystem or not? My experiences have been that the disk queue counters do not provide any meaningful data from a SAN under stress, whereas the i/o completion time is consistant in reporting regardless of SAN or DAS - however SAN's are another subject area. It is possible to have extended i/o completion time without disk queuing so if you only monitor queue you'd never know anything was wrong.
I'm purely considering disk sub system performance and monitoring within a SAN.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 12, 2006 at 9:25 am
Colin - yes the book "Sql server 2000 performance tuning - technical reference" is a good reference. I'll include it in the reference area if we re-print the article anywhere.
May 14, 2009 at 8:24 am
Anthony, is there a commercial software available to stress test SQL Server 2005 ?
May 19, 2009 at 9:53 am
Hello all,
The author of this article (my brother) passed away from Brain cancer in May 2009.
He was a brilliant, dynamic and warm person who will be VERY missed...
God Speed...
Chris Bressi
May 19, 2009 at 10:03 am
Chris,
My condolences and our thoughts and prayers are with you and your family.
July 13, 2009 at 1:20 pm
Thanks Steve...All the best...
Chris
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply