Accessing Master Sysperfinfo Table in SQL Server
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
A Simple Introduction to Cisco CML2
Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2
How to Build in a PSMethod to your PowerShell Code
In this video, PowerShell instructor Jason Yoder shows how to add Methods (PSMethod) to your code using free software that’s added into the PSObject. For instructor-led PowerShell courses, see our course schedule. Microsoft Windows PowerShell Training Download the Building Methods PowerShell script</a> used in this video. <# ╔══════════════════════════════════════════════════════════════════════════════╗ ║ ║ ║ Building Methods ║ ╟──────────────────────────────────────────────────────────────────────────────╢ … Continue reading How to Build in a PSMethod to your PowerShell Code
OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj
In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.
Write a Comment
