Making Sense of Sysperfinfo

In SQL Server 7.0 and later, you can access SQL Server—related performance counter values through the system table sysperfinfo in the master database. Sysperfinfo is a tabular view of the performance counters. Like sysprocesses and syslockinfo, sysperfinfo is a pseudo table, which SQL Server constructs on the fly only when you access it. For example, when you set up a SQL Server performance condition alert by using sp_add_alert or Enterprise Manager, SQL Server accesses the appropriate performance counter value through this table to determine whether the specified condition is met.

Even though Microsoft advises against directly querying system tables, it's convenient to be able to retrieve performance counter values directly by using a T-SQL script. You might think the values you get would be the same as the values you get from System Monitor charts—but they aren't. Sometimes looking at the values in the charts isn't enough if you want to write queries that depend on the specific value of a performance counter. You can't link the System Monitor chart values to your T-SQL scripts, but you can put the sysperfinfo values into scripts you're using for analysis.

For example, you might want to write a T-SQL script that monitors the performance of your SQL Server buffer manager and alerts you when the buffer cache hit ratio is below 90 percent. Or you might want to collect performance counter values in SQL Server tables for later analysis. You want the values to be meaningful to the person doing the analysis. However, if you select directly from sysperfinfo, some of the values you find in the cntr_value column might not make much sense, especially if you compare them with the performance counter values you get from System Monitor. That's because sysperfinfo doesn't give you completely calculated performance counter values, as the System Monitor does. For example, say you're querying the sysperfinfo table for information about the Buffer Manager's buffer cache hit ratio. The value you get from sysperfinfo is 635391413; this value isn't meaningful for someone who's using it in analysis. But when the values are completely calculated, you get a value of 99.99 percent, which an analyst would find meaningful.

Here's why sysperfinfo doesn't return the calculated value you see in System Monitor. Sysperfinfo's first three columns—object_name, counter_name, and instance_name—correspond directly to the name of the Windows performance object, the name of the performance counter within the object, and the named instance of the counter, respectively. You can use the value of the fifth column, cntr_type, to help calculate the counter values in the cntr_value column, which is the fourth column. The cntr_type column identifies the type of counter in that row and dictates how the counter is calculated. Sysperfinfo's cntr_type column has four distinct values: 65536, 272696320, 537003008, and 1073939459.

According to Windows performance counter documentation (which is available in the Microsoft Windows 2000 Resource Kit), type 65536 is named PERF_COUNTER_RAWCOUNT. A counter of this type (e.g., the Buffer Manager's Free pages counter) shows the last value measured from the source. Type 272696320 is named PERF_COUNTER_COUNTER. A counter of this type (e.g., the Buffer Manager's Page reads/sec counter) shows the average number of operations completed per second during the sample interval. SQL Server already calculates the values of these two counter types for you.

Type 537003008 (called PERF_RAW_FRACTION) and type 1073939459 (called PERF_RAW_BASE) work together to give you performance counter values that are expressed as a ratio. An example of a type 537003008 counter is the Buffer Manager counter Buffer cache hit ratio; the Buffer Manager counter Buffer cache hit ratio base is an example of a type 1073939459 counter. You must divide the value of the former type by the value of the latter type to get the value of the buffer cache hit ratio, which is the same value that System Monitor would give you.

The T-SQL query in Listing 1 performs the necessary calculations to return meaningful performance counter values. SQL Server uses essentially the same query internally to produce calculated performance counter values. But using this code will let you directly retrieve the values from the sysperfinfo table so that you can use them in your T-SQL script.

—Linchi Shea
[email protected]