How to determine load on SQL Server instance?

Question

How to determine load on SQL Server instance?

Michelle Cook

SSCrazy

Points: 2119
More actions
March 28, 2006 at 8:01 am

#112536

I support a SQL Server instance that has been performing adequately. I was recently informed that there is a plan to increase the number of application users, by approx. double of current users. The application team wants to know if the SQL Server instance can handle the increase of users and still maintain the current level of performance. Is there a way to report on what the current database usage is, and the amount of resources being used on an average day. I know that the windows performance monitor can be used to track server resources, but what about the database usage. Any suggestions how I can provide a report of what the current average daily database use is?

Viewing 6 posts - 1 through 5 (of 5 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

You need to have a baseline from which you can work, otherwise it's just a guess. The ms book sql server 2000 tuning is a good place to start. isbn 0-7356-1270-6

Idera Diagnostic Manager is a good product to buy if you want to store server trend statistics and such.

There have been various articles about gathering stats in sql mag and this web site.

You evaluate performance by stress testing on a dedicated server using scale up and scale out. There are various tools and methods you can use, including profiler scripts and ms utilities.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Michelle Cook SSCrazy Points: 2119 More actions · Answer 2

Colin,

Thanks for the information. Actually, the application support team requested that I just track a single day this week and provide a report of database utilization for the day. I thought of just running a profiler trace and then use that information to create a report, but I'm not sure what to trace or how to report on it. Any ideas on that?

Thanks,

Michelle

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Michelle

Have a look here - this will give you some ideas on what sort of thing to monitor. You can then add extra counters if you think they will be useful, or discard any that you don't think you need.

John

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

Profiler won't really give you a load. Follow John's advice and track some counters. A single day isn't really that good a test, more like a whole week or 4-5 random days during the month to get different loads.

Michelle Cook SSCrazy Points: 2119 More actions · Answer 5

Thank you for your replies. I agree with both of you, and was thinking that the windows administrator could run some reports for the application group on server utilization if he is not already doing that. I am going to setup the performance monitor with the counters you suggested and work with that. Thanks again.