Accessing Master..Sysperfinfo Table

Accessing Master..Sysperfinfo Table


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