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

Database Performance Monitoring on the Cheap

The system views are (to many) hidden gems in SQL. One in particular I'd like to address today is [sys].[dm_os_performance_counters], available in SQL2005 (and I assume 2008).

A quick SELECT * FROM [sys].[dm_os_performance_counters] will show you all that's available; a whole range of performance counters that you'd often go to Perfmon to check out.

The advantage of having this all so simply queryable from SQL is that we can start recording this every x minutes/hours, and start charting it out, and get some ideas about some of the weaknesses of our DBs, when they're under load etc.

First we need somewhere to store the data:

   ----------------------------------------------------------------------------------------
   --
   -- Create a schema
   --
   ----------------------------------------------------------------------------------------
CREATE SCHEMA [Monitoring];
   
   ----------------------------------------------------------------------------------------
   --
   -- Create a table for the stats to live in
   --
   ----------------------------------------------------------------------------------------
CREATE TABLE [Monitoring].[PerformanceCounters](
            [DateTimeID] [bigint] NOT NULL,
            [DateTime] [datetime] NOT NULL,
            [object_name] [nvarchar](128) NOT NULL,
            [counter_name] [nvarchar](128) NOT NULL,
            [instance_name] [nvarchar](128) NULL,
            [cntr_value] [bigint] NULL,
            [cntr_type] [int] NULL,
            CONSTRAINT [pkPerformanceCounters] PRIMARY KEY CLUSTERED
      (
               [DateTimeID] ASC,
               [counter_name] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
   ) ON [PRIMARY]
         

I've used a DateTimeID field as I don't like using Datetimes as part of primary keys, and also they can be useful in any Analysis Services you might want to do down the road.

Now for a proc to record some counters (feel free to pick and choose which counters you think are appropriate, and please let me know if you've any suggestions):

   ----------------------------------------------------------------------------------------
   --
   -- Create a procedure which will query the DMVs and store the results for reporting
   --
   ----------------------------------------------------------------------------------------
CREATE PROCEDURE [Monitoring].[sPerformanceCountersInsert]
         AS
   BEGIN
   INSERT INTO
               [Monitoring].[PerformanceCounters]
      SELECT
            CAST(DATEPART(YEAR, GETDATE()) AS bigint)*100000000+
            DATEPART(MONTH, GETDATE())*1000000+
            DATEPART(DAY, GETDATE())*10000+
            DATEPART(hour, GETDATE())*100+
            DATEPART(minute, GETDATE()),
            GETDATE(),
            [dm_os_performance_counters].[object_name],
            [dm_os_performance_counters].[counter_name],
            [dm_os_performance_counters].[instance_name],
            [dm_os_performance_counters].[cntr_value],
            [dm_os_performance_counters].[cntr_type]
      FROM
            [TheDBYouWantToMonitor].[sys].[dm_os_performance_counters]
      WHERE
      (
               OBJECT_NAME = 'SQLServer:Buffer Manager'
            AND (
                  counter_name = 'Page life expectancy'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:General Statistics'
            AND (
                  counter_name = 'User Connections'
               OR counter_name = 'Processes blocked'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:Databases'
            AND instance_name = 'TheDBYouWantToMonitor'
            AND (
                  counter_name = 'Transactions/sec'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:Access Methods'
            AND (
                  counter_name = 'Full Scans/sec'
               OR counter_name = 'Range Scans/sec'
               OR counter_name = 'Index Searches/sec'
               OR counter_name = 'Page Splits/sec'
               OR counter_name = 'Table Lock Escalations/sec'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:SQL Statistics'
            AND counter_name = 'SQL Re-Compilations/sec'
      )
         OR (
               OBJECT_NAME = 'SQLServer:Memory Manager'
            AND counter_name = 'Memory Grants Outstanding'
      )
         OR (
               OBJECT_NAME = 'SQLServer:Transactions'
            AND counter_name = 'Transactions'
      )
   END

This is all pretty DB centric stuff, it's unfortunate that more server level stats aren't available with this method, such as memory and disk usage.

Now we just need to set up a SQL Server Agent job to fire off that job every x minutes/hours. That'll depend on how busy your DB is, on my main prod DB I've got this guy running every 5 minutes, and a similar proc recording Data and Log file sizes on a daily basis.

Once you've got enough historical data, it's a simple matter to query:

CREATE PROCEDURE [Monitoring].[sPerformanceCountersSelectByDateRange]
   @FormerDate DATETIME,
   @LatterDate DATETIME
         AS
   BEGIN
   SELECT
            [DateTimeID]
            ,[DateTime]
            ,[object_name]
            ,[counter_name]
            ,[instance_name]
            ,[cntr_value]
            ,[cntr_type]
      FROM
            [Monitoring].[PerformanceCounters]
      WHERE
            [DateTime] BETWEEN @FormerDate 
         AND @LatterDate
   END

I'm hooking this proc up into a Reporting Services report at the moment, charting each counter seperately. It's quite interesting to see the shapes of the charts, and spurring me into understanding more about what each one really means. It's great stuff to show to mgt as well, giving them a bit of a look into the black box that is their SQL Server.