Accessing Master Sysperfinfo Table in SQL Server

Home > Blogs > SQL Server > Accessing Master Sysperfinfo Table in SQL Server

Accessing Master Sysperfinfo Table in SQL Server

Like This Blog 0 Jeff Jones
Added by 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

Videos You May Like

Creating Users and Managing Passwords in Microsoft Office 365

0 642 3

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.   For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365    

JavaScript for C# Developers – September 24, 2014

0 487 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 608 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.