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.