March Madness – SQL Server System Tables – sysperfinfo

Did you ever wish that you could access the information contained inside of Perfmon counters by using some simple T-SQL statements and not resorting to using WMI code or xp_cmdshell? Well today is your lucky day! Well, mostly your lucky day. Read on to find out why sysperfinfo may be the greatest thing ever for you. Or not.

As with the previous disclaimers, please note that this system table will be removed from a future version of SQL Server.

What is this table for?

This table will contain information on the SQL specific performance counters found in Perfmon. That’s the good news. It does not, however, contain information on each and every performance counter in Perfmon. That’s the bad news. Want to see which ones? Simple enough, go run this code:

SELECT * FROM
master.dbo.sysperfinfo

And browse through the results that are returned. Notice a pattern? Well you should. Now compare that result set to a list of all available Perfmon objects and counters and you should see a difference. If not, let me give you a quick example: Your result set doesn’t list ‘Processor’ or ‘Physical Disk’.

That’s right, you can get counters specific to your instance (HINT: they start with ‘MSSQL’), but not the counters specific to the O/S. Which means you have a mixed bag here. On the one hand, you can’t get everything. On the other hand, you can get some things.

I was given some advice a long time ago that I will share with you now: Something is better than nothing.

Why should you care?

Simple: you need this information, more often than you might think. I like to tell my clients that when it comes to effective performance tuning I need to perform some forensics on their system. Collecting details from Perfmon is one of the tools I like to utilize. Now, I may not always have access to the server O/S, but I almost always have access to the SQL instance. And if I have access to that, then it is nice to know that I can start collecting some basic performance counters in order to help me do some of  the forensics I need to do for performance tuning.

Now for the disclaimer: The cntr_value column will often contain values that don’t make sense. In other words, if you were to look at the buffer cache hit ratio:

SELECT cntr_value
FROM master.dbo.sysperfinfo
WHERE counter_name = 'Buffer cache hit ratio'

you get back a number like 138, which is very different than what you would see in Perfmon (typically you see something like 99%). That means you need to do some extra legwork on your end in order to get some meaning out of the values returned. Look, I never said this was going to be easy, I just said it was going to be possible. The cntr_type column is going to be your best friend here, as it will not only tell you what type of counter but it will tell you how the counter is calculated.

On my SQL2008R2 instance running on my laptop (x64 Win7), if I run this:

SELECT DISTINCT cntr_type FROM master.dbo.sysperfinfo

I get back five values: 65792, 272696576, 537003264, 1073874176, and 1073939712. Digging through some Windows documentation (i.e., using The Google) I can find what those types are, and how to make them serve me well.

So the type 65792 is a PERF_COUNTER_LARGE_RAWCOUNT, which means I don’t have to do any extra calculations. Good, I like things that are easy. And fast. And cheap.

But type 537003264 is PERF_AVERAGE_BULK, which needs to be paired with type 1073939712 the PERF_LARGE_RAW_BASE like this:

SELECT cntr_value, cntr_type
FROM master.dbo.sysperfinfo
WHERE counter_name like 'Buffer cache hit ratio%'

You get back two rows, one for the value and one for the base. You just need to divide the value associated with PERF_AVERAGE_BULK by the value associated with PERF_LARGE_RAW_BASE. Simple, right? Unfortunately you need to do the math manually, whereas Perfmon does it all for you. I never said this was going to be easy.

Where else is this information?

Remember how I said this system table will be removed from a future version of SQL Server? Well, what you want to use instead going forward is the sys.dm_os_performance_counters DMV. Go ahead and run this code:

SELECT *
FROM sys.dm_os_performance_counters

Now examine the difference in the output from the sysperfinfo system table…keep looking…OK, stop looking. There is no difference. They are the same. But since the system table will be going away eventually then you should start using the DMV instead anyway.