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