Transactions per interval can tell you how busy a SQL Server instance is during a particular period. In a recent performance tuning discussion, a reader asked how to count them. The Databases performance counter gives us a useful starting point.

Sample the transactions counter
The Transactions/sec counter in sys.dm_os_performance_counters is cumulative. Take two readings and subtract the first from the second to find how many transactions started during the sample. This example requires SQL Server 2016 or later. It waits ten seconds and also calculates an average rate using the measured elapsed time.
DECLARE @First bigint, @Second bigint;
DECLARE @StartTime datetime2, @EndTime datetime2;
DECLARE @Startup datetime, @StartupAfter datetime;
SELECT @Startup = sqlserver_start_time FROM sys.dm_os_sys_info;
SELECT @First = cntr_value, @StartTime = SYSDATETIME()
FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE '%:Databases'
AND counter_name = 'Transactions/sec'
AND instance_name = '_Total';
WAITFOR DELAY '00:00:10';
SELECT @Second = cntr_value, @EndTime = SYSDATETIME()
FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE '%:Databases'
AND counter_name = 'Transactions/sec'
AND instance_name = '_Total';
SELECT @StartupAfter = sqlserver_start_time FROM sys.dm_os_sys_info;
IF @First IS NULL OR @Second IS NULL
THROW 50000, 'The Transactions/sec counter was not found.', 1;
IF @Startup <> @StartupAfter OR @Second < @First
THROW 50001, 'The counter reset during sampling. Try again.', 1;
SELECT @Second - @First AS TransactionsStarted,
DATEDIFF_BIG(millisecond, @StartTime, @EndTime) / 1000.0 AS ElapsedSeconds,
(@Second - @First) * 1000.0 /
NULLIF(DATEDIFF_BIG(millisecond, @StartTime, @EndTime), 0) AS AveragePerSecond;Understand the result
TransactionsStarted is the change in the counter over the interval. AveragePerSecond is that change divided by the elapsed time. The _Total instance combines database activity; it is not a count of commits, successful transactions, or individual business operations. A transaction involving more than one database may contribute to more than one database counter.
The script uses bigint because the counter can grow beyond the range of int. It also detects a missing counter or a reset between samples. SQL Server resets these performance counters when the engine restarts. The measured interval may be a little longer than the requested delay.
Choose an interval and check permissions
Change WAITFOR DELAY to suit your observation window. For example, use ’00:01:00′ for approximately one minute. The batch waits for the chosen period, so run it in a separate query window when needed.
On SQL Server, reading these views requires the appropriate server permissions: VIEW SERVER STATE on older versions, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. If the counter is missing, check the instance and its performance counter configuration.
For a single database, replace ‘_Total’ with that database’s counter instance name. To study a longer period, collect timestamped readings at regular intervals and compare consecutive samples, keeping SQL Server restarts in mind.
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.





21 Comments. Leave new
Thanks Pinal, this sure comes handy..
Thanks ,
Nice Script,
I had changed it as follows so it would work on my Sql sever instance.
— First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE ‘%Databases%’ — Change name of your server
AND counter_name like ‘%Transactions/sec%’
AND instance_name like ‘%_Total%’;
SELECT @First
— Following is the delay
WAITFOR DELAY ’00:00:30′
— Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE ‘%Databases%’ — Change name of your server
AND counter_name like ‘%Transactions/sec%’
AND instance_name like ‘%_Total%’;
SELECT @Second
SELECT (@Second – @First) ‘TotalTransactions’
GO
I too modified this slightly, to make it so that I can capture results form multiple instances on multiple servers at the same time.
I modified @Veepools script to add a breakdown per second.
I am sure someone else has worked out how to paramaterise the delay so that this is the only field to change but I am not that good yet.
—-
— First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE ‘%Databases%’ — Change name of your server
AND counter_name like ‘%Transactions/sec%’
AND instance_name like ‘%_Total%’;
— Following is the delay
WAITFOR DELAY ’00:01:00′
–Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE ‘%Databases%’ –Change name of your server
AND counter_name like ‘%Transactions/sec%’
AND instance_name like ‘%_Total%’;
SELECT (@Second – @First) ‘Total Transactions’, ((@Second – @First)/60) ‘Avg Transactions P/S’ –Change the division to the number of seconds in the time frame you chose.
GO
—
Oops forgot to add, to execute against many servers in the same window create a Central Management Server Group and run against a group of registered servers.
User account needs permissions on all servers.
You can enable Change Tracking, to track the changes to the database-table.
Benefits here would be:
1) You can track changes which you are trying to troubleshoot.
2) You can get the more info on whether you have more INSERTS/DELETES/UPDATES.
I guess the query you have suggested wont help me much in pinning down the suffering table
can anybody please explain me about the instance_name ‘_Total’ ,iam confused about it
Pinal, I really like your posts… short, sweet, and directly to the point. Always useful.
I know that this is very old article… but in order to make query dynamic irrespective of the instance name or default instance, below is the code:
— First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = CASE
WHEN SERVERPROPERTY(‘InstanceName’) IS NULL
THEN ‘SQLServer’
ELSE ‘MSSQL$’ + CAST(SERVERPROPERTY(‘InstanceName’) AS VARCHAR)
END + ‘:Databases’ — Change name of your server
AND 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 OBJECT_NAME = CASE
WHEN SERVERPROPERTY(‘InstanceName’) IS NULL
THEN ‘SQLServer’
ELSE ‘MSSQL$’ + CAST(SERVERPROPERTY(‘InstanceName’) AS VARCHAR)
END + ‘:Databases’ — Change name of your server
AND counter_name = ‘Transactions/sec’
AND instance_name = ‘_Total’;
SELECT (@Second – @First) ‘TotalTransactions’
GO
Dont Work for me, allways said NULL
Hello Pinal Sir,
I have an application which needs to perform 700 Database transactions per minute.
The Transactions are extremely light in nature. (Updating a column value in a table, copying the newly inserted row using the after insert trigger etc)
300 transactions are on a Database named TWPQueues & the remaining 400 are on another database.
My problem is that I have to rent a server to host the application but I am unable to decide what capacity of server I shall buy which can perform 700 database transactions in a minute.
I asked a few experts & they said that the no of database transactions that a server can perform depends on the size of your RAM and the complexity of your database transactions.
As I am not an expert in this, please help me.
Thanks in advance.
Regards,
Swapnil Khandode
What about transactions happened on a specific table?
This gets all transactions for a Server, is there any way to get these transactions by database?
can anyone tell me , how many transaction can be happen on a single table at a time.
Hi Guys,
Can anyone help how to find out the toltal number of transaction in one day (Business hours).
I want to know how much data is writing on database in single day so that i can manage the Hardware accordingly and w.r.t to performance.
Naveed, (and anyone else finding this later)
Functionally if you look at the example, what is going on is a COUNTER value is being recorded, and then we wait 1 minute and record it again. We take each sample (recorded value) and subtract one from the other.
This works because this particular counter is a cumulative counter since the last time you started the SQL server instance.
To get what you desire, step up your game and create your own small reporting / logging database. then you can sample the counter when ever you want. Perhaps you create a table with only two columns: DateRecorded, TransactionsCount
Then insert a single sample when ever you want. Now you can take any two sample and measure the difference between them. If you sample at 1 minute intervals, you could get measurements of transactions in a minute, 5 minutes, 15 minute, an hour, 6 hours, etc..
Take it up a notch and add another column: DatabaseName. The Instance_Name is the database name. Sample each one separately, (or step it up again and realize you can capture them all at once). Now you have even more granular data.
Thanks Pinal, Great Post!
Thanks @Bruce for the additional input.
I am getting below error:
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.
Msg 8115, Level 16, State 2, Line 11
Arithmetic overflow error converting expression to data type int.
Just replace INT with “bigint” and you are done!!
That’s a busy server where transactions are bigger than INT value. Thanks for sharing error and solution.
hi sir, can u pls help me to know on table level transaction during certain time duration.
I don’t think there is a performance counter for table level.