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.

How to Measure Transactions Per Seconds in SQL Server? - Interview Question of the Week #262 transactionsperseconds-800x195

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)

SQL DMV, SQL Performance, SQL Scripts, SQL Server, SQL Transactions, Windows
Previous Post
What are Different Methods to Know the Row Count of Table? – Interview Question of the Week #261
Next Post
How to Measure Resource Status on Azure? – Interview Question of the Week #264

Related Posts

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

    Reply
  • 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 :)

    Reply
  • Seems all wrong. Transactions/s subtracted from Transaction/s gives you the difference in Transactions per second, but not the total transactions.

    Reply
  • Bahti Samet Coban
    September 28, 2021 1:27 pm

    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

    Reply
  • Bahti Samet Coban
    September 28, 2021 5:14 pm

    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

    Reply
  • it’s about the UPDATE transaction, doesnot reflact the SELECT transaction. won’t be baseline of the database load.

    Reply

Leave a Reply