x

Monitoring

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 '12 at 01:50 PM in Default

sand143eep gravatar image

sand143eep
771 20 32 39

its not regarding the cpus ram disk network i want on database level please suggest on the Database level please
Mar 13 '12 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

Compilations/sec
Recompilations/sec
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 '12 at 02:53 PM

Sacred Jewel gravatar image

Sacred Jewel
1.6k 2 4 5

(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 '12 at 02:01 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.7k 28 30 35

(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 '12 at 05:10 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(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 '13 at 12:52 PM

joeshestak gravatar image

joeshestak
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 '13 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1933

asked: Mar 13 '12 at 01:50 PM

Seen: 544 times

Last Updated: Dec 10 '13 at 12:52 PM