Monitoring

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

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.

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