Question: How do you measure transactions per second in SQL Server? Sample the Transactions/sec performance counter twice, subtract the earlier cumulative value from the later one, then divide by elapsed seconds.
I originally wrote an interval transaction script and updated it for health checks. The ten-second sampling idea is useful, but the old article mixed interval totals, per-second rates and SQL instance scope. Here is the corrected version.
DECLARE @Database sysname = N'_Total'; -- Or a database name, for example tempdb.
DECLARE @Object nvarchar(128), @First bigint, @Second bigint;
DECLARE @Start datetime2(7), @Finish datetime2(7), @Boot datetime2;
IF (SELECT COUNT(*) FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE N'%:Databases'
AND counter_name = N'Transactions/sec' AND instance_name = @Database) <> 1
THROW 50104, 'Expected one database transaction counter for this instance.', 1;
SELECT @Object = object_name, @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE N'%:Databases'
AND counter_name = N'Transactions/sec' AND instance_name = @Database;
SELECT @Boot = sqlserver_start_time FROM sys.dm_os_sys_info;
SET @Start = SYSUTCDATETIME();
WAITFOR DELAY '00:00:10';
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = @Object AND counter_name = N'Transactions/sec'
AND instance_name = @Database;
SET @Finish = SYSUTCDATETIME();
IF @Second IS NULL OR @Second < @First
OR @Boot <> (SELECT sqlserver_start_time FROM sys.dm_os_sys_info)
THROW 50105, 'Counter missing or reset: discard this sample.', 1;
SELECT @Database AS CounterInstance,
@Second - @First AS TransactionsInInterval,
CAST(DATEDIFF_BIG(MICROSECOND, @Start, @Finish) / 1000000.0
AS decimal(19,6)) AS ElapsedSeconds,
CAST((@Second - @First) * 1000000.0 /
NULLIF(DATEDIFF_BIG(MICROSECOND, @Start, @Finish), 0)
AS decimal(19,3)) AS TransactionsPerSecond;_Total is the counter instance for this connected SQL Server instance’s database total. To inspect one database, change @Database to its name, such as tempdb. This DMV does not read every separate SQL Server instance installed on the Windows host; connect and sample each SQL instance separately.
The code identifies one matching counter rather than assigning an arbitrary row from many database counters. bigint matches cntr_value’s type and avoids the original int overflow risk. The result separates TransactionsInInterval from TransactionsPerSecond.
If a counter grows by 200 over exactly ten seconds, the interval total is 200 and the average rate is 20 per second. The old subtraction alone produced the total, not the rate. Measuring the actual interval also avoids pretending WAITFOR and the two queries consume exactly ten seconds.
The Databases object’s Transactions/sec counts transactions started, not necessarily committed business operations. Microsoft notes it doesn’t count XTP-only transactions started by natively compiled procedures. Database activity and a business transaction are not interchangeable definitions.
Read access requires the appropriate server-state permission, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. Missing counters, counter resets or a disconnected sample need investigation; discard such a sample rather than reporting a negative or NULL rate.
I still use interval measurements in performance investigations. A rate is useful when its counter, scope and sample duration are clear.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
6 Comments. Leave new
Hey Pinal, thanks again for posing. Always appreciate that you share your knowledge with the community. One question regarding this post; why don’t you use perfmon for this? I found that a little more conveniant. If you are a real geek you can put it into Grafana and that way also get some baseline (can be applied to many more counters btw).
I believe the “All Instances” query should be restricted to “instance_name = ‘_Total'” as well. I wish that column name was “database_name” to be less confusing :)
Seems all wrong. Transactions/s subtracted from Transaction/s gives you the difference in Transactions per second, but not the total transactions.
Hi Pinal,
First query under “Measure Total Transactions on All Instances” title gives the transaction count on the first database on that instance due to that fact that it is querying entire instance but assign the value of first database(first row) into @First and same goes for the second part of the query. As a result, it actually gives you “Total Transactions on the first database of the Instance”.
Instead, one has to use SUM function on cntr_value column to get total transactions on the instance as follows:
-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = sum(cntr_value)
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
-- Following is the delay
WAITFOR DELAY '00:01:00'
-- Second PASS
SELECT @Second = sum(cntr_value)
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec';
SELECT (@Second - @First) 'TotalTransactions'
GO
I just noticed that my answer has a flaw too. This view also has a row with a key “_Total” as previously mentioned in Josiah’s comment. Hence, the correct query is
-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
AND instance_name='_Total';
-- Following is the delay
WAITFOR DELAY '00:01:00'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
AND instance_name='_Total';
SELECT (@Second - @First) 'TotalTransactions'
GO
it’s about the UPDATE transaction, doesnot reflact the SELECT transaction. won’t be baseline of the database load.