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
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

Agile Methodology in Project Management

0 171 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

Creating Users and Managing Passwords in Microsoft Office 365

0 740 4

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    

Windows 10 Features and Navigation – December 1, 2015

0 128 1

In this recorded Windows 10 webinar from December 1,2015, Windows Instructor Steve Fullmer presents the navigation and some of the new features associated with Windows 10 including Sysinternals Tools for Windows Client, Windows core concepts, exploring Process Explorer as well as some of the features that are not yet ready for prime time but will … Continue reading Windows 10 Features and Navigation – December 1, 2015

Write a Comment

Share your thoughts...

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