Monitoring Process for Performance Counters

Recently I needed to create a process to monitor performance counters over a short period of time. We were going to implement a change and we wanted to compare performance before and after to see if there was any impact.

To do this, I first created a couple of tables. One table is used to actually store the monitored values. The second table is used for configuration; you insert only the counters you want to monitor.

/* Create the table to store our logged perfmon counters */
Create Table dbo.dba_perfCounterMonitor
(
      capture_id    int Identity(1,1)   Not Null
    , captureDate   smalldatetime       Not Null
    , objectName    nvarchar(128)       Not Null
    , counterName   nvarchar(128)       Not Null
    , instanceName  nvarchar(128)       Not Null
    , value         float(6)            Not Null
    , valueType     nvarchar(10)        Not Null
 
    Constraint PK_dba_perfCounterMonitor
        Primary Key Clustered(capture_id)
);
 
/* Create the table that controls which counters we're going to monitor */
Create Table dbo.dba_perfCounterMonitorConfig
(
      objectName    nvarchar(128)   Not Null
    , counterName   nvarchar(128)   Not Null
    , instanceName  nvarchar(128)   Null
);

If you leave the instanceName NULL in the config table, it’ll monitor all instances. Now we’re going to insert some sample performance counters into the config table. The counters you’re interested in can, and likely will, vary.

/* Insert some perfmon counters to be monitored */
Insert Into dbo.dba_perfCounterMonitorConfig
Select 'SQLServer:Buffer Manager', 'Page Life Expectancy', Null Union All
Select 'SQLServer:Locks', 'Lock Requests/sec', Null Union All
Select 'SQLServer:Locks', 'Lock Waits/sec', Null Union All
Select 'SQLServer:Locks', 'Lock Wait Time (ms)', Null Union All
Select 'SQLServer:Buffer Manager', 'Page reads/sec', Null Union All
Select 'SQLServer:Buffer Manager', 'Page writes/sec', Null Union All
Select 'SQLServer:Buffer Manager', 'Buffer cache hit ratio', Null Union All
Select 'SQLServer:Databases', 'Transactions/sec', 'AdventureWorks' Union All
Select 'SQLServer:General Statistics', 'Processes blocked', Null;

Now let’s create our proc. This proc will run for a specified time period and will *average* the counters over that time. I personally take snapshots every 15 seconds for 4 minutes; I have a scheduled task that runs this every 5 minutes. It’s not perfect, but it gives me a good idea of what’s happening on the server.

Create Procedure dbo.dba_perfCounterMonitor_sp
 
        /* Declare Parameters */
          @samplePeriod    int      =  240  /* how long to sample, in seconds */
        , @sampleRate      char(8)  =  '00:00:15'  /* how frequently to sample, in seconds */
        , @displayResults  bit      =  0  /* display the results when done */
As
/*********************************************************************************
    Name:       dba_perfCounterMonitor_sp
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    Monitors performance counters.  Uses the dba_perfCounterMonitorConfig
                table to manage which perf counters to monitor.  
 
                @samplePeriod - specifies how long the process will try to monitor
                                performance counters; in seconds.
 
                @sampleRate - how long inbetween samples; in seconds.
 
                The average values over sample period is then logged to the
                dba_perfCounterMonitor table.
 
    Notes:      There are 3 basic types of performance counter calculations:
 
                Value/Base: these calculations require 2 counters. The value 
                            counter (cntr_type = 537003264) has to be divided 
                            by the base counter (cntr_type = 1073939712).
 
                Per Second: these counters are store cumulative values; the
                            value must be compared at 2 different times to
                            calculate the difference (cntr_type = 537003264).
 
                Point In Time:  these counters show what the value of the
                                counter is at the current point-in-time 
                                (cntr_type = 65792).  No calculation is 
                                necessary to derive the value.
 
    Called by:  DBA
 
    Date        User    Description
    ----------------------------------------------------------------------------
    2009-09-04  MFU     Initial Release
*********************************************************************************
    Exec dbo.dba_perfCounterMonitor_sp
          @samplePeriod     = 60
        , @sampleRate       = '00:00:01'
        , @displayResults   = 1;
*********************************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set Numeric_RoundAbort Off;
 
Begin
 
    /* Declare Variables */
    Declare @startTime datetime
        , @endTime datetime
        , @iteration int;
 
    Select @startTime = GetDate()
        , @iteration = 1;
 
    Declare @samples Table
    (
          iteration     int             Not Null
        , objectName    nvarchar(128)   Not Null
        , counterName   nvarchar(128)   Not Null
        , instanceName  nvarchar(128)   Not Null
        , cntr_value    float           Not Null
        , base_value    float           Null
        , cntr_type     bigint          Not Null
    );
 
    Begin Try
 
        /* Start a new transaction */
        Begin Transaction;
 
        /* Grab all of our counters */
        Insert Into @samples
        Select @iteration
            , RTrim(dopc.object_name)
            , RTrim(dopc.counter_name)
            , RTrim(dopc.instance_name)
            , RTrim(dopc.cntr_value)
            , (Select cntr_value From sys.dm_os_performance_counters As dopc1
                Where dopc1.object_name = pcml.objectName
                And dopc1.counter_name = pcml.counterName + ' base'
                And dopc1.instance_name = IsNull(pcml.instanceName, dopc.instance_name))
            , dopc.cntr_type
        From sys.dm_os_performance_counters As dopc
        Join dbo.dba_perfCounterMonitorConfig As pcml
            On dopc.object_name = pcml.objectName
                And dopc.counter_name = pcml.counterName
                And dopc.instance_name = IsNull(pcml.instanceName, dopc.instance_name);
 
        /* During our sample period, grab our counter values and store the results */
        While GetDate() < DateAdd(second, @samplePeriod, @startTime)
        Begin
 
            Set @iteration = @iteration + 1;
 
            Insert Into @samples
            Select @iteration
                , RTrim(dopc.object_name)
                , RTrim(dopc.counter_name)
                , RTrim(dopc.instance_name)
                , dopc.cntr_value
                , (Select cntr_value From sys.dm_os_performance_counters As dopc1
                    Where dopc1.object_name = pcml.objectName
                    And dopc1.counter_name = pcml.counterName + ' base'
                    And dopc1.instance_name = IsNull(pcml.instanceName, dopc.instance_name))
                , dopc.cntr_type
            From sys.dm_os_performance_counters As dopc
            Join dbo.dba_perfCounterMonitorConfig As pcml
                On dopc.object_name = pcml.objectName
                    And dopc.counter_name = pcml.counterName
                    And dopc.instance_name = IsNull(pcml.instanceName, dopc.instance_name);
 
            /* Wait for a small delay */
            WaitFor Delay @sampleRate;
 
        End;
 
        /* Grab our end time for calculations */
        Set @endTime = GetDate();
 
        /* Store the average of our point-in-time counters */
        Insert Into dbo.dba_perfCounterMonitor 
        (
			  captureDate
			, objectName
			, counterName
			, instanceName
			, value
			, valueType
		) 
		Select @startTime
		    , objectName
		    , counterName
		    , instanceName
		    , Avg(cntr_value)
		    , 'value'
		From @samples
		Where cntr_type = 65792
		Group By objectName
		    , counterName
		    , instanceName;
 
        /* Store the average of the value vs the base for cntr_type = 537003264 */
        Insert Into dbo.dba_perfCounterMonitor 
        (
			  captureDate
			, objectName
			, counterName
			, instanceName
			, value
			, valueType
		) 
		Select @startTime
		    , objectName
		    , counterName
		    , instanceName
		    , Avg(cntr_value)/Avg(IsNull(base_value, 1))
		    , 'percent'
		From @samples
		Where cntr_type = 537003264
		Group By objectName
		    , counterName
		    , instanceName;
 
        /* Compare the first and last values for our cumulative, per-second counters */
        Insert Into dbo.dba_perfCounterMonitor 
        (
			  captureDate
			, objectName
			, counterName
			, instanceName
			, value
			, valueType
		) 
		Select @startTime
		    , objectName
		    , counterName
		    , instanceName
		    , (Max(cntr_value) - Min(cntr_value)) / DateDiff(second, @startTime, @endTime)
		    , 'value'
		From @samples
		Where cntr_type = 272696576
        Group By objectName
		    , counterName
		    , instanceName;
 
        /* Should we display the results of our most recent execution?  */
        If @displayResults = 1
            Select captureDate
                , objectName
                , counterName
                , instanceName
                , value
                , valueType
            From dbo.dba_perfCounterMonitor With (NoLock)
            Where captureDate = Cast(@startTime As smalldatetime)
            Order By objectName
                , counterName
                , instanceName;
 
        /* If you have an open transaction, commit it */
        If @@TranCount > 0
            Commit Transaction;
 
    End Try
    Begin Catch
 
        /* Whoops, there was an error... rollback! */
        If @@TranCount > 0
            Rollback Transaction;
 
        /* Return an error message and log it */
        Execute dbo.dba_logError_sp;
 
    End Catch;
 
    Set NoCount Off;
    Return 0;
End
Go

Like I said, it’s not perfect, but it gets the job done. :)

Getting an error about dba_logError_sp? Take a look at my error handling proc.

Undocumented Function in SQL 2008

If you’ve been following my blog for a little while, you’ll know that I’m a fan of SQL Server internals. There’s a lot that can be learned or better understood by rolling up your sleeves and getting into the nitty-gritty of data pages (i.e. see my post on Overhead in Non-Unique Clustered Indexes). So imagine how happy I was when my co-worker Jeff shared an undocumented function with me today that retrieves the file number, page number, and slot number of a single record. Very cool! Well, at least to me. So now let’s see how you can use it.

The fn_physLocCracker function can be called in the following way:

Select Top 100 plc.*, soh.SalesOrderID
From Sales.SalesOrderHeader As soh
Cross Apply sys.fn_physLocCracker (%%physloc%%) As plc;

Results (just a sample):

file_id     page_id     slot_id     SalesOrderID
----------- ----------- ----------- ------------
1           14032       0           43659
1           14032       1           43660
1           14032       2           43661
1           14032       3           43662
1           14032       4           43663

If you look at the sp_helptext for sys.fn_physLocCracker, %%physloc%% is apparently a virtual column that contains information on where the record is stored. In fact, you can even append %%physloc%% to your column list if you want to see how the information is stored. But for our purposes, we now have a file number, page number, and slot number. What do we do with it?

Well, you can use the investigation proc I wrote to retrieve the actual data page:

Execute dba_viewPageData_sp 
      @databaseName = 'AdventureWorks'
    , @fileNumber = 1
    , @pageNumber = 14032;

Results (just a sample):

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
 
SalesOrderID = 43659                 
 
Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
 
RevisionNumber = 1                   
 
Slot 0 Column 3 Offset 0x9 Length 8 Length (physical) 8
 
OrderDate = 2001-07-01 00:00:00.000  
 
Slot 0 Column 4 Offset 0x11 Length 8 Length (physical) 8
 
DueDate = 2001-07-13 00:00:00.000

Neat, huh? So why would you use it to look up the data page and file number when you can just pass the table name and index name to my proc and retrieve data pages? Well, my investigation proc will retrieve data pages for any index type — the fn_physLocCracker function will only retrieve data for the clustered index — but it will not retrieve the data page for a specific record. So just something to be aware of.

That’s all for now. Back to the #24HoursOfPASS! :)