SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stress Testing SQL Server


Stress Testing SQL Server

Author
Message
Anthony Bressi
Anthony Bressi
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 1
Comments posted here are about the content posted at temp
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4627 Visits: 715

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.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
BJ Hermsen
BJ Hermsen
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 880

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.


colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4627 Visits: 715

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.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Anthony Bressi
Anthony Bressi
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 1
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.
esaleh_2008
esaleh_2008
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
Anthony, is there a commercial software available to stress test SQL Server 2005 ?
cbressi
cbressi
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 6
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
cbressi@aspect-consulting.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61945 Visits: 19100
Chris,

My condolences and our thoughts and prayers are with you and your family.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
cbressi
cbressi
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 6
Thanks Steve...All the best...

Chris
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search