Monitoring SQL Server Health

See "Troubleshooting Performance Problems in SQL Server 2005" and "Tips for Using SQL Server Performance Monitor Counters"). The key to running traces in the most efficient manner is to run the traces locally on the machine running SQL Server and pipe the output to a local file.

In addition to capturing and analyzing performance counters and profiler information you may want to look into identifying where the engine is spending its time waiting, which tells you if you are I/O constrained, CPU constrained, memory constrained, and so on. For more on this see "How do you measure CPU pressure?".

You should also consider implementing Microsoft® Operations Manager (MOM) 2005 as a part of your SQL Server 2005 monitoring plans. The SQL Server 2005 Management Pack is quite useful. Even without it you can get a good understanding of what's going on with your servers just by using the MOM operator console and warehouse and associated reports that expose counters like CPU, Memory, Disk Queuing, and so on.

From Predicates, Stored Procedures, and More by Nancy Michell