SYSPERFINFO - some info needed.
-
Hello,
Does anyone have any info on sysperfinfo, particularly buffer cache hit ratio?
Does this report an average over time, or is a real time stat?
Any good scripts to get the current Buffer Cache Hit ratio?
Thanks!
-
Here's a spiel I've picked up from somewhere :-
In SQL Server 7.0 and later, you can access SQL Server—related performance counter values through the system table sysperfinfo in the master database. Sysperfinfo is a tabular view of the performance counters. Like sysprocesses and syslockinfo, sysperfinfo is a pseudo table, which SQL Server constructs on the fly only when you access it. For example, when you set up a SQL Server performance condition alert by using sp_add_alert or Enterprise Manager, SQL Server accesses the appropriate performance counter value through this table to determine whether the specified condition is met.
Even though Microsoft advises against directly querying system tables, it's convenient to be able to retrieve performance counter values directly by using a T-SQL script. You might think the values you get would be the same as the values you get from System Monitor charts—but they aren't. Sometimes looking at the values in the charts isn't enough if you want to write queries that depend on the specific value of a performance counter. You can't link the System Monitor chart values to your T-SQL scripts, but you can put the sysperfinfo values into scripts you're using for analysis.
For example, you might want to write a T-SQL script that monitors the performance of your SQL Server buffer manager and alerts you when the buffer cache hit ratio is below 90 percent. Or you might want to collect performance counter values in SQL Server tables for later analysis. You want the values to be meaningful to the person doing the analysis. However, if you select directly from sysperfinfo, some of the values you find in the cntr_value column might not make much sense, especially if you compare them with the performance counter values you get from System Monitor. That's because sysperfinfo doesn't give you completely calculated performance counter values, as the System Monitor does. For example, say you're querying the sysperfinfo table for information about the Buffer Manager's buffer cache hit ratio. The value you get from sysperfinfo is 635391413; this value isn't meaningful for someone who's using it in analysis. But when the values are completely calculated, you get a value of 99.99 percent, which an analyst would find meaningful.
Here's why sysperfinfo doesn't return the calculated value you see in System Monitor. Sysperfinfo's first three columns—object_name, counter_name, and instance_name—correspond directly to the name of the Windows performance object, the name of the performance counter within the object, and the named instance of the counter, respectively. You can use the value of the fifth column, cntr_type, to help calculate the counter values in the cntr_value column, which is the fourth column. The cntr_type column identifies the type of counter in that row and dictates how the counter is calculated. Sysperfinfo's cntr_type column has four distinct values: 65536, 272696320, 537003008, and 1073939459.
According to Windows performance counter documentation (which is available in the Microsoft Windows 2000 Resource Kit), type 65536 is named PERF_COUNTER_RAWCOUNT. A counter of this type (e.g., the Buffer Manager's Free pages counter) shows the last value measured from the source. Type 272696320 is named PERF_COUNTER_COUNTER. A counter of this type (e.g., the Buffer Manager's Page reads/sec counter) shows the average number of operations completed per second during the sample interval. SQL Server already calculates the values of these two counter types for you.
Type 537003008 (called PERF_RAW_FRACTION) and type 1073939459 (called PERF_RAW_BASE) work together to give you performance counter values that are expressed as a ratio. An example of a type 537003008 counter is the Buffer Manager counter Buffer cache hit ratio; the Buffer Manager counter Buffer cache hit ratio base is an example of a type 1073939459 counter. You must divide the value of the former type by the value of the latter type to get the value of the buffer cache hit ratio, which is the same value that System Monitor would give you.
The T-SQL query in Listing 1, page 15, performs the necessary calculations to return meaningful performance counter values. SQL Server uses essentially the same query internally to produce calculated performance counter values. But using this code will let you directly retrieve the values from the sysperfinfo table so that you can use them in your T-SQL script.
LISTING 1: T-SQL Query That Returns
Calculated SQL Server Performance Counter Values
SELECT perf1.object_name, perf1.counter_name, perf1.instance_name,
'value' = CASE perf1.cntr_typeWHEN 537003008 -- This counter is expressed
as a ratio and requires calculation.
THEN CONVERT(FLOAT,perf1.cntr_value) /(SELECT CASE perf2.cntr_valueWHEN 0 THEN 1ELSE perf2.cntr_valueEND
FROM master..sysperfinfo perf2WHERE (perf1.counter_name + ' '= SUBSTRING(perf2.counter_name,1,
PATINDEX('% Base%', perf2.counter_name)))AND (perf1.instance_name = perf2.instance_name)AND (perf2.cntr_type = 1073939459))ELSE perf1.cntr_value -- The values of the other counter types are
-- already calculated.ENDFROM master..sysperfinfo perf1WHERE (perf1.cntr_type <> 1073939459) -- Don't display the divisors. -
Thx, for your reply. It is some helpful information.
So, this is an average counter, as opposed to real-time? Should one be concerned with the average hitting below a certain threshold, or a real-time value that after x times is still below the threshold? Thx
-
Some of the counters are averages and other counters are snapshots of values. What you need to do is apply known threshold rules for these counters. Go to
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
to view some of the SQL Counter threshold rules.
-
/*
The cntr_type value for SysPerfInfo changed with SQL Server 2005.
I modified the SQL in this thread to work with both SQL Server 2000 and 2005
/*
SELECT
perf1.[object_name],
perf1.counter_name,
perf1.instance_name,
perf1.cntr_type,
'value' = CASE perf1.cntr_type
WHEN 537003008 -- This counter is expressed as a ratio and requires calculation. (Sql 2000)
THEN CONVERT(FLOAT,
perf1.cntr_value) /
(SELECT CASE perf2.cntr_value
WHEN 0 THEN 1
ELSE perf2.cntr_value
END
FROM master..sysperfinfo perf2
WHERE (perf1.counter_name + ' '
= SUBSTRING(perf2.counter_name,
1,
PATINDEX('% Base%', perf2.counter_name)))
AND perf1.[object_name] = perf2.[object_name]
AND perf1.instance_name = perf2.instance_name
AND perf2.cntr_type in (1073939459,1073939712)
)
WHEN 537003264 -- This counter is expressed as a ratio and requires calculation.
THEN CONVERT(FLOAT,
perf1.cntr_value) /
(SELECT CASE perf2.cntr_value
WHEN 0 THEN 1
ELSE perf2.cntr_value
END
FROM master..sysperfinfo perf2
WHERE (perf1.counter_name + ' '
= SUBSTRING(perf2.counter_name,
1,
PATINDEX('% Base%', perf2.counter_name)))
AND perf1.[object_name] = perf2.[object_name]
AND perf1.instance_name = perf2.instance_name
AND perf2.cntr_type in (1073939459,1073939712)
)
ELSE perf1.cntr_value -- The values of the other counter types are
-- already calculated.
END
FROM master..sysperfinfo perf1
WHERE perf1.cntr_type not in (1073939459,1073939712) -- Don't display the divisors.
ORDER BY 1,2,3,4
David Bird
-
Can someone please explain why there is no base row for :
MSSQL$myinstance:Access Methods \ Worktables From Cache Ratio
(sql2005 EE X64)
/*
* SQL2005 variant using the DMV
*/
SELECT dmv_cntr.[object_name]
, dmv_cntr.counter_name
, dmv_cntr.instance_name
, dmv_cntr.cntr_value
, dmv_cntr.cntr_type
, CASE dmv_cntr.cntr_type
WHEN 537003264 -- This counter is expressed as a ratio and requires calculation. (Sql2005)
THEN CONVERT(FLOAT, dmv_cntr.cntr_value) / coalesce(RatioBaseValue.cntr_RatioBaseValue,-1)
ELSE dmv_cntr.cntr_value -- The values of the other counter types are already calculated.
END as Calculated_Counter_value
, CASE dmv_cntr.cntr_type
WHEN 537003264 -- This counter is expressed as a ratio and requires calculation. (Sql2005)
THEN RatioBaseValue.cntr_RatioBaseValue
ELSE 1 -- The values of the other counter types are already calculated.
END as cntr_RatioBaseValue
FROM sys.dm_os_performance_counters dmv_cntr
left join ( SELECT CASE cntr_value
WHEN 0 THEN 1
ELSE cntr_value
END as cntr_RatioBaseValue
, counter_name
, [object_name]
, instance_name
, cntr_type
FROM sys.dm_os_performance_counters
WHERE cntr_type = 1073939712
 
RatioBaseValueon dmv_cntr.counter_name + ' ' = SUBSTRING(RatioBaseValue.counter_name, 1, PATINDEX('% Base%', RatioBaseValue.counter_name))
AND dmv_cntr.[object_name] = RatioBaseValue.[object_name]
AND dmv_cntr.instance_name = RatioBaseValue.instance_name
WHERE dmv_cntr.cntr_type <> 1073939712 -- Don't display the divisors. (RatioBaseValue)
order by 1,2,3
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply