April 21, 2008 at 6:45 am
Dear all,
We are about to get much more users through our web based applications and we think we can't get away anymore without load testing...
Unfortunately, we are all new to the science of Load Testing.
As the DBA, I am tempted to collect various performance data into specific tables for monitoring the tests.
I can think of collecting theses:
- various response times specific to our application
- sql queries taking more than X milliseconds (through a server side trace initially)
Is there obvious stuff I should/could also collect?
Are there particular (Sql Server) counters that could tell me when the system is getting stretched?
Thanks
Eric
😀
April 21, 2008 at 7:17 am
The main ones you would want to monitor are the following
SQL server buffer manager: Buffer cache hit ratio
Physical disk: Average queue length (do a seperate counter for each disk)
processor: processor usage
memory: memory usage
memory: pages/sec
buffer cache should be above 90
disk queue length should be below 2 per physical disk, so if you have a 3 disk raid5 and the counter says 9 then the counter per disk is 3.
processor should be below 70
memory usage should not be too high
pages/sec counter should be below 20.
take the results before, during and after your load testing.
April 21, 2008 at 1:55 pm
I've some info on my blog here
http://sqlblogcasts.com/blogs/grumpyolddba/archive/tags/Trending+and+Statistics/default.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 21, 2008 at 2:00 pm
sorry, I hate to dispute other posts but:- Disk Queue doesn't apply when connected to a SAN or NAS - use io completion time - a much better counter.
Pages per sec is a pointless counter, if you have a page file windows will use it.
memory usage should be as much memory as you can allocate to SQL Server, you might want to look at available pages.
Load testing is tricky and you should consider building a stress test environment and buy specialised tools and expertise.
Loading should include scale out and scale up
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 21, 2008 at 2:47 pm
Thanks colin. Could you let me know where i would find the io completions counter, under which object? and also, what results should you be expecting on a well managed server?
I have always learnt, from MS exams/books mainly, that excessive paging (MS always use > 20/sec) could mean that your memory is not up to speed, and therefore the system is using the disk to compensate. I know from what experience i have that the MS guidelines are not always brilliant, but its the first time ive heard this counter should be ignored.
Thanks
John
April 22, 2008 at 2:44 am
Although simplistic, I usually like to look at
- Physical Disk / % Disk Time (on each drive if relevant, not for SAN)
- Processor / % Processor Time (on each processor)
They give me a pretty good idea how my machine is "feeling".
I am almost surprised nobody mentions them...
Also, I suspect I can't collect these counters straight from within Sql Server. I think I have to do it through the Windows Performance MMC. Please let me know if I am wrong.
What about SQLDiag?
I don't have experience of that tool but it seems powerful/popular.
Would it be an approriate tool?
Thanks for your comments. Much appreciated.
Eric 🙂
April 22, 2008 at 12:56 pm
think I covered this on my blog , but the disk counters I use, from perfmon, are :-
avg disk sec/transfer, avg disk sec/write, avg disk sec/read, disk idle time %, and disk transfres/sec ( reads/sec and writes/sec if reqd.
the first set time how long it takes the avergae io to complete, ms say under 20 ms for a write and under 10 ms for a read ( I think ) On good storage systems you should see under 1 ms for reads and under 6 ms for writes. A raid 5 will tend to be worse than a raid 10/1/0 for writes. These counters still work on a SAN. Disk idle time should be taken from 100% to show how busy a disk/array is. I find this counter also works on a SAN and is usually a better guide than disk time.
io rate is useful (ish ) but mainly as a measure to compare rather than an absolute.
I normally look at cpu but if the server is working hard cpu is going to high, likewise context switches so these don't really help you other than to confirm your system is under stress unlike io completion time which can show a bottleneck. you might want to follow this link http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/24/perfmon-counters-thresholds.aspx
and
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/08/10/what-s-in-a-counter.aspx
sql performance counters are availble inside sql server via master..sysperfinfo or sys.dm_os_performance_counters you can read these counters into a table and query the results. Likewise you can log perfmon into a database table and query the results real time ( ish ) - it's not perfect using perfmon into a table but it does work.
You could also buy Idera SQL Diagnostic Manager - this will do most of this for you.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply