SQL Server - PERFMON counters and Resource Governor - what you can do in a performance issue situation?

PERFMON and Resource Governor - two great tools in hand for a DBA to monitor/resolve the performance problems, agreee!

Back in olden days you may remember about SQLDiag and PSSDiag, the utility used as a general purpose diagnostics collection utility that can be run as a console application or as a service. Both of these Diag tools are helpful to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers. SQLdiag is intended to expedite and simplify diagnostic information gathering for Microsoft CSS.

Ok, most of the web resources out there talk pages and pages together about features/utilities you can use, but not how you could make use of these tools (freely available from Microsoft) to do 'a definitive'  type of analysis near the beginning of troubleshooting; for example, if you don't know whether the primary bottleneck on the system is disk or CPU, how do you know whether to focus on Query A (the most expensive query in terms of CPU) or Query B (the most expensive in terms if I/O)? Hence from a getting started to performance monitoring the tool to look forward is the good old PerfMon Tool.

So when going about current used version for instance SQL Server 2005 instance, loads of counters are available that can be related to Database Engine (such as Access Methods, Buffer Cache ratio, Cursor, Locks and Transactions etc.). Talking about this version it is good to refer about Service Broker related and replication (if any involved) related too. Why Service Broker, as it is a set of performance counters that provide information on its activities. Service Broker also logs serious errors to the SQL Server error log and the Windows application event log, a reference from Books Online :

First, use the WAITFOR clause. Messages seldom arrive at predictable intervals. Even in a service where messages arrive at roughly the same rate that the stored procedure processes the messages, there may be times when no messages are available. Therefore, the procedure should use a WAITFOR clause with a RECEIVE statement or with a GET CONVERSATION GROUP statement. Without WAITFOR, these statements return immediately when there are no available messages on the queue. Depending on the implementation of the stored procedure, the procedure may then loop back through the statement, consuming resources needlessly, or the procedure may exit only to be reactivated shortly thereafter, consuming more resources than simply continuing to run.

You allow for the unpredictability in timing by using the WAITFOR clause with the RECEIVE or GET CONVERSATION GROUP statement. If your application runs continuously as a background service, you do not specify a time-out in the WAITFOR statement. If your application is activated by Service Broker, or runs as a scheduled job, you specify a short time-out, for example, 500 milliseconds. An application that uses the WAITFOR statement gracefully handles unpredictable intervals between messages. Likewise, an activated application that exits after a short time-out does not consume resources when there are no messages to process.

So then moving onto SQL Server 2008 specific there are some new counters that get introduced with certain features like Resource Governor (RG). These features are exciting and open new options to developers and administrators when working with SQL Server. RG introduces a whole new concept of Resource Pools  and Workload groups and we have monitors for the same under separate counters set.

Mixing together with PERFMON (SYSMON) counters with Resource Governor will give you much leverage on the performance monitoring concept, lets take a look at the following counters that will help to identify where is the problem:

Above all lets not forget the Dynamic Management Views (DMVs), another mix and match tool with Resource Governor to get more information on the health of SQL instance *& problem resolution.