Choosing the Right Performance Monitor Counters

Related: What perfmon Counters Can I Trust When Using SAN Disks?

I need to create a performance baseline for my company's SQL Server based on Performance Monitor counters. What counters should I measure, and which ones are most important?

We could devote an entire issue of SQL Server Magazine to a discussion of which SQL Server counters are important under what circumstances. Instead, let's look at a few counters that I think are most important and, sometimes, least understood.

When you talk about performance, you're usually concerned with the amount of business-related work a server can process—in other words, the numbers of queries and transactions you're processing. After all, who cares if CPU utilization is consistently near 100 percent if response time is always great and the application handles all your users' requests? Of course, a server at 100-percent CPU utilization has no headroom to handle processing peaks, and you wouldn't likely get adequate response time for queries if the server was consistently running at 100 percent. However, the amount of real work the database performs is ultimately the only measure that matters to end users, administrators, and their bosses.

How can you use Performance Monitor to track the amount of work that SQL Server performs? Two Performance Monitor counters specifically let you track SQL Server's work: the Transaction/sec counter in the SQL Server Databases object and the Batch Requests/sec counter in the SQL Server General Statistics object. Both counters are useful, but many people incorrectly interpret the values that they show. For an example of the kind of information these counters provide, perform the following tests.

Open an instance of Performance Monitor, select the System Monitor pane, and add the Transaction/sec and Batch Requests/sec counters I mentioned above. The Transactions/sec counter tracks information at a database level, so you need to select a database—I use tempdb for this example. Create a test table in tempdb by running the script that Listing 2 shows.

Now execute the following simple batch repeatedly as fast as possible:

SELECT * FROM worktestGO

The easiest way to execute the batch repeatedly is to select the batch in Query Analyzer, press Ctrl+E to execute the batch, then keep Ctrl+E depressed. The query will execute over and over again as long as you keep the keys depressed. Run the batch in this manner continuously for at least 5 seconds, then toggle back to Performance Monitor.

Related: Performance Counters

What do you see in Performance Monitor? You should see the Batch Requests/sec counter spike (it went to about 15 on my laptop) while the Transactions/sec counter remains flat at 0. When you have multiple counters, sometimes picking out the line in the Performance Monitor chart that contains the counter information you want is hard. To make your counters easier to read, use the highlighting option: Select the icon that looks like a light bulb to highlight the currently selected counter in a wide bold line.

Now run the following batch repeatedly, using the technique I described above, and observe the activity in Performance Monitor:

BEGIN TRANSELECT * FROM worktestCOMMIT TRAN

In Performance Monitor, you should see identical values for both Batch Requests/sec and Transactions/sec. What does this result explain about the nature of each counter?

Transaction/sec doesn't measure activity unless it's inside a transaction. Batch Requests/sec measures all batches you send to the server even if they don't participate in a transaction. SQL Server Books Online (BOL) specifically says that Batch Requests/sec is a good measure of throughput (i.e., work). However, Transactions/sec is the counter that many people use in the field. Unfortunately, this counter gives you a number skewed to the low side if your application workload includes a reasonably large number of batches that don't participate in a transaction—which is common. What's a large number? If 20 percent of your workload includes simple SELECT statements that don't participate in a transaction, the Transactions/sec counter will be off by about 20 percent—a significant amount in my mind. Numbers as low as 5 percent could be significant to you, depending on how detailed your analysis needs to be. Batch Requests/sec is a better indicator of throughput in such cases.

To more fully understand Transactions/sec, run the following final test:

UPDATE Worktest set Col1 = 1 GO

You should see the same behavior that you saw in the previous test: Transactions/sec and Batch Requests/sec are identical. Thus, you don't need to issue an explicit pair of BEGIN/COMMIT statements to make SQL Server report the batch as having started a transaction. Of course, all UPDATE, INSERT, and DELETE statements are transactionally consistent and operate within the scope of a transaction.