Monday, February 23

Database Performance Monitoring on the Cheap Part 2

I was pretty happy to see that my last post "Database Performance Monitoring on the Cheap" got a bunch of hits on the intertubes. This somewhat balanced out my shame that the procedure I provided for querying the recorded data needed to be a bit smarter to be truly helpful.
Turns out that the stats provided by the sys.dm_os_performance_counters DMV are not all straightforward to interpret.
Some of them are either ratios (where one counter needs to be divided by another), or they are cumulative (where the counter needs to be compared to a previous value, and divided by the number of seconds elapsed).
Thankfully, there's an easy way to tell which counters are which, by checking the cntr_type field. When it comes to ratios, the numerator type is 537003264, and the denominator (or base) is 1073939712 (and has the word "base" at the end of the counter_name). The cumulative fellows are of type 272696576.

So without further ado, here's a smarter way to query that data which we've had collecting:

CREATE PROCEDURE [Monitoring].[sPerformanceCountersSelectByDateRange]
  
@FormerDate DATETIME,
  
@LatterDate DATETIME
         AS
   BEGIN
   SELECT
            
DateTimeID
            
,DATETIME
            
,OBJECT_NAME
            
,counter_name
            
,instance_name
            
,cntr_value
            
,cntr_type
      
FROM
      
(
      
SELECT
              
DateTimeID
              
,DATETIME
              
,OBJECT_NAME
              
,counter_name
              
,instance_name
              
,cntr_value
              
,cntr_type
        
FROM
              
Monitoring.PerformanceCounters
        
WHERE
              
NOT PerformanceCounters.cntr_type IN (272696576,537003264,1073939712) --Cumulative and Ratio counters
        
UNION
      SELECT
--Cumulative Counters
              
CurrentPerformanceCounters.DateTimeID
              
,CurrentPerformanceCounters.DATETIME
              
,CurrentPerformanceCounters.OBJECT_NAME
              
,CurrentPerformanceCounters.counter_name
              
,CurrentPerformanceCounters.instance_name
              
,(CurrentPerformanceCounters.cntr_value-PreviousPerformanceCounters.cntr_value)/
              
DATEDIFF(ss,PreviousPerformanceCounters.DATETIME,CurrentPerformanceCounters.DATETIME) cntr_value
              
,CurrentPerformanceCounters.cntr_type
        
FROM
              
Monitoring.PerformanceCounters CurrentPerformanceCounters
              
JOIN
              
Monitoring.PerformanceCounters PreviousPerformanceCounters
              
ON CurrentPerformanceCounters.counter_name = PreviousPerformanceCounters.counter_name
            
AND CurrentPerformanceCounters.instance_name = PreviousPerformanceCounters.instance_name
            
AND CurrentPerformanceCounters.DateTimeID
        
= PreviousPerformanceCounters.DateTimeID
              
+ CASE RIGHT(CurrentPerformanceCounters.DateTimeID,2)
                  
WHEN 00 THEN 45
        
ELSE 5
        
END
         WHERE
              
CurrentPerformanceCounters.cntr_type = 272696576
        
UNION
      SELECT
--Ratio Counters
              
NumeratorPerformanceCounters.DateTimeID
              
,NumeratorPerformanceCounters.DATETIME
              
,NumeratorPerformanceCounters.OBJECT_NAME
              
,NumeratorPerformanceCounters.counter_name
              
,NumeratorPerformanceCounters.instance_name
              
,NumeratorPerformanceCounters.cntr_value/CAST(DenominatorPerformanceCounters.cntr_value AS FLOAT) cntr_value
              
,NumeratorPerformanceCounters.cntr_type
        
FROM
              
Monitoring.PerformanceCounters NumeratorPerformanceCounters
              
JOIN
              
Monitoring.PerformanceCounters DenominatorPerformanceCounters
              
ON DenominatorPerformanceCounters.cntr_type = 1073939712
            
AND NumeratorPerformanceCounters.DateTimeID = DenominatorPerformanceCounters.DateTimeID
            
AND NumeratorPerformanceCounters.instance_name = DenominatorPerformanceCounters.instance_name
        
WHERE
              
NumeratorPerformanceCounters.DATETIME BETWEEN @FormerDate
            
AND @LatterDate
            
AND NumeratorPerformanceCounters.cntr_type = 537003264
      
) AllResults
      
WHERE
            
AllResults.DATETIME BETWEEN @FormerDate
        
AND @LatterDate
  
END

5 comments:

Jan said...

Thx, nice post!

I'm currently in the process of making some graphs for it in flash.

Nico Van de Venne said...

Hi SQL Ninja.

I'm only a novice in SQL and I just copied and pasted the codeyou supplied on our test SQL 2008 server but got a few errors back :-) I know what you are thinking, what an id*t.

Could yo supply me with some more steps, what am I missing?

Thanks for te help in anycase,
Nico

Scott Herbert said...

Hi Nico.

Can you post what errors you're finding? I'll help you out as soon as possible.

Thanks,
Scott

Nico Van de Venne said...

As you requeste the error I get;

"Msg 2760, Level 16, State 1, Procedure sPerformanceCountersSelectByDateRange, Line 7
The specified schema name "Monitoring" either does not exist or you do not have permission to use it."

Scott Herbert said...

OK, your first step there is to create the schema which the table and stored proc are going to live in.

CREATE SCHEMA Monitoring
GO