SQL Server – Increasing ‘Stored Procedures Invoked/sec’ Counter

monitoringperformanceservice-brokersql serversql-server-2008-r2

We've implemented some feature using service broker and deployed recently on production. I am observing that "Stored Procedures Invoked/sec" counter is always increasing. However, I can see that other counters like Tasks Running etc are stable. Why Stored Procedures Invoked/sec not stick to a range?

Best Answer

There are several different types of performance counters, five of which are used by SQL Server (at least in terms of what you will find in the sys.dm_os_performance_counters DMV). You can see this via the following query:

SELECT DISTINCT [cntr_type]
FROM sys.dm_os_performance_counters
ORDER BY [cntr_type];

Returns:

65792
272696576
537003264
1073874176
1073939712

Those values mean:

  • Noncomputational Counter Types

    • 65792 == PERF_COUNTER_LARGE_RAWCOUNT == "Raw counter value that does not require calculations, and represents one sample which is the last observed value only."

  • Counter Algorithm Counter Types

    • 272696576 == PERF_COUNTER_BULK_COUNT == "Average number of operations completed during each second of the sample interval."
    • 1073874176 == PERF_AVERAGE_BULK == "Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_AVERAGE_BASE as the counter type."

  • Base Counter Types

    • 1073939712 == PERF_LARGE_RAW_BASE == "Base value found in the calculation of PERF_RAW_FRACTION"

  • Basic Algorithm Counter Types

    • 537003264 == PERF_LARGE_RAW_FRACTION == "Ratio of a subset to its set as a percentage. This counter type displays the current percentage only, not an average over time."

You can find more info in the following blog post: Interpreting the counter values from sys.dm_os_performance_counters

The Stored Procedures Invoked/sec counters in the SQLServer:Broker Activation category are of type 272696576 (i.e. "PERF_COUNTER_BULK_COUNT"). These values are cumulative. You need to capture a value into a variable, wait for N seconds (i.e. WAITFOR DELAY '00:00:30'; -- 30 seconds), capture a second value, and then divide the difference between the values by N (i.e. SELECT (@var2 - @var1) / 30.0;). For example:

DECLARE @Sample1 BIGINT,
        @Sample2 BIGINT,
        @Time1 DATETIME,
        @Time2 DATETIME;

SELECT @Sample1 = [cntr_value],
       @Time1 = GETDATE()
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Databases' -- SQLServer:Broker Activation
AND   [counter_name] = N'Transactions/sec' -- Stored Procedures Invoked/sec
AND   [instance_name] = N'_Total';

WAITFOR DELAY '00:00:30.000'; -- 30 second pause/sleep

SELECT @Sample2 = [cntr_value],
       @Time2 = GETDATE()
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Databases' -- SQLServer:Broker Activation
AND   [counter_name] = N'Transactions/sec' -- Stored Procedures Invoked/sec
AND   [instance_name] = N'_Total';

SELECT (@Sample2 - @Sample1) AS [Difference],
       (DATEDIFF(MILLISECOND, @Time1, @Time2) / 1000.0) AS [Seconds],
       (@Sample2 - @Sample1) / (DATEDIFF(MILLISECOND, @Time1, @Time2) / 1000.0);
Related Question