How to Measure Transactions Per Seconds in SQL Server? – Interview Question of the Week #262

Question: How to Measure Transactions Per Seconds in SQL Server?

Answer: This is very with the help of OS Performance Counter. Here is the script which I have written earlier to measure the total number of transactions on the interval. SQL SERVER – Find Total Number of Transactions on Interval. I have recently updated this script for my consulting service Comprehensive Database Performance Health Check and thought of sharing the same with everyone.

Measure Total Transactions on All Instances

If you have multiple instances on your server, you can run the following script to get an idea about how many transactions have happened in the last 10 seconds across all the instances.

-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
-- Following is the delay
WAITFOR DELAY '00:00:10'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec';
SELECT (@Second - @First) 'TotalTransactions'
GO

Instance Specific Transactions

If you have multiple instances on your server and if you want to get details about any specific instance, you can run the following script to get an idea about how many transactions have happened in the last 10 seconds for that particular instance.

-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'MSSQL$SQL19:Databases' AND -- Change name of your server ' AND -- Change name of your server
counter_name = 'Transactions/sec' AND
instance_name = '_Total';
-- Following is the delay
WAITFOR DELAY '00:00:10'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'MSSQL$SQL19:Databases' AND -- Change name of your server
counter_name = 'Transactions/sec' AND
instance_name = '_Total';
SELECT (@Second - @First) 'TotalTransactions'
GO

Database Specific Transactions

If you have multiple instances on your server and if you want to get details about any specific instance and also for a specific database, you can run the following script to get an idea about how many transactions have happened in the last 10 seconds for that particular database.

-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'MSSQL$SQL19:Databases' AND -- Change name of your server ' AND -- Change name of your server
counter_name = 'Transactions/sec' AND
instance_name = 'tempdb'; -- Change name of your database
-- Following is the delay
WAITFOR DELAY '00:00:10'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE
OBJECT_NAME = 'MSSQL$SQL19:Databases' AND -- Change name of your server
counter_name = 'Transactions/sec' AND
instance_name = 'tempdb'; -- Change name of your database
SELECT (@Second - @First) 'TotalTransactions'
GO

I hope you will find this script helpful to identify how many transactions are happening at any point in time on your database. Please note that I have provided the delay in the time as 10 seconds but you can change that to any period you want to capture the transactions.

Additionally, the counter value is cumulative, which means it will keep on increasing. Whenever there are new transactions, it will be added to the counter, hence you need to subtract the second value from the first value to get the transactions per second.

I use this script in my consulting service Comprehensive Database Performance Health Check very frequently.

Reference: Pinal Dave (https://blog.sqlauthority.com)