Hello i was asked to have better performance monitoring on sql server 2005. can any one please suggest with atleast 6 best steps to monitor the performance of sql server.

more ▼

asked Mar 13, 2012 at 01:50 PM in Default

avatar image

921 40 50 56

its not regarding the cpus ram disk network i want on database level please suggest on the Database level please

Mar 13, 2012 at 02:16 PM sand143eep
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

I totally agree with Greg. If you keep yourself narrowed down to database level, then you still may end up witn a non-optimal throughput. Always try to keep the whole picture in mind. Your database would be dependant upon each stated above. But the few I can think of are

 Top Queries By IO
 Top Queries By CPU
 Top Slow running queries
 Most executed queries
 Most Blocked/Blocking queries
 Top Queries By Lowest Plan Reuse
 Missing Indexes
 Index Defragmentation
 Out of date Statistics

more ▼

answered Mar 13, 2012 at 02:53 PM

avatar image

Sacred Jewel
1.7k 3 7 10

(comments are locked)
10|1200 characters needed characters left

That's a very open question.

You need to monitor your hardware resources:

  • CPUs

  • RAM

  • DISK

  • Network

You need to understand Waits and Queues as well as Execution Plans/Query Tuning.

There is no simple one size fits all kind of answer here but there are many good guidelines out there to follow.

The important takeaway is that you have to look at it from a holistic point of view. One perceived performance issue may actually be as a result of a different issue altogether.

more ▼

answered Mar 13, 2012 at 02:01 PM

avatar image

12.1k 30 36 42

(comments are locked)
10|1200 characters needed characters left

Things that you want to monitor inside your database from a performance perspective could cover things such as:

  • Indexes - missing ones and the actual and the usage of existing ones

  • Parallelism

  • Wait Types

  • Memory settings

  • Database file activity

  • and many more

This can only be relevant however if you are also monitoring all the things on the hardware too. Its no good only worrying about your oil temperature in your car if you dont also look after your brakes/lights etc. The same applies with SQL Server, you have to watch all aspects of your system

more ▼

answered Mar 13, 2012 at 05:10 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

I think you should try SQL server performance monitoring tools. For that I recommend you to use MindArray IPM's SQL server monitoring tools. It helps to monitor performance of your SQL server. It supports multiple SQL server like MS SQL server 2000, 2005, 2008, 2012. I think it's best for you.

more ▼

answered Dec 10, 2013 at 12:52 PM

avatar image

41 1

Hi @joeshestak , thanks for taking part in our forum, we are pleased that you read the questions and contribute answers but it would great if you could provide some answers that are not simply a recommendation for a tool?

Are you affiliated with the tool at all? If so then you should disclose your links to it so that readers will understand your viewpoint.

Future answers that simply recommend the tool with little or no reason why, or provide any suggestion of actually how to fix the problems posed in a question may well be deleted.

Dec 10, 2013 at 12:44 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 13, 2012 at 01:50 PM

Seen: 885 times

Last Updated: Dec 10, 2013 at 12:52 PM

Copyright 2018 Redgate Software. Privacy Policy