Like This Blog 0

Added by Jeff Jones November 8, 2006

The SQL Server 2000 statistics are available in a pseudo-table in Master called sysperfinfo. This table returns the statistics in a table format. There are some statistics that represent ratios (i.e., Buffer Cache Hit Ratio). These statitics must combine two rows. One row provides the data value and the second row provides a divisor. Therefore to be able to read ratio statistics, you must bring these two rows together to compute the final result.

Included in this post is a SELECT statement that displays all the statistics as well as perform the necessary calculation to display ratio statistics correctly.

This query can come in handy if you need to view SQL Server statistics when you don't have access to the server's performance monitor program. It can also allow you to collect SQL Server statistics for storage in a SQL table for subsequent analysis. Just add a column with a GUID or DateTime stamp to group each statistics snapshot.

I just found that all the statistics that are time adjusted like Logins/sec, Batches/sec, Transactions/sec, etc. are not displayed properly through a query of Master..Sysperfinfo. It will display a monotonically increasing number since the start of SQL Server. There is no way to compute the per second value. This also means that you cannot set SQL Server Alerts that use a per second threshold from these statistics to fire the alert. I noticed this behavior when doing more testing on my query and then found article 555064 in the Microsoft Knowledge Base confirming what I saw. This reduces the usefulness of this query. But you can still get access to most of the statistics.

SELECT p1.object_name
, p1.counter_name
, p1.instance_name
, CASE p1.cntr_type
WHEN 537003008 — Count is a ratio
THEN CONVERT(FLOAT, p1.cntr_value) /
CASE p2.cntr_value
WHEN 0 THEN 1 — Make sure we don't divide by zero
ELSE p2.cntr_value
END
ELSE p1.cntr_value — No calculation necessary, display value
END AS Value
FROM master..sysperfinfo p1
LEFT OUTER JOIN master..sysperfinfo p2
ON (SUBSTRING(p1.counter_name , 1, –Deal with ratios that has (ms) in name
COALESCE(NULLIF(charindex(' (ms)',p1.counter_name), 0), len(p1.counter_name) + 1))
= SUBSTRING(p2.counter_name, 1, CHARINDEX(' Base', p2.counter_name))) –Remove Base from counter name
AND p1.instance_name = p2.instance_name
AND p2.cntr_type = 1073939459 — Only join if row is a ratio
WHERE p1.cntr_type 1073939459 — Don't include rows with base divisor value