"Stored Procedures Invoked/sec" counter always increasing

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?

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);


StackExchange Database Administrators Q#153381, answer score: 2