Feb 26, 2013

SQL Server Transaction per interval

If you are wondering how much SQL transaction is happening on your database, run the below query.

DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'SQLServer:Databases' -- Change name of your server
AND counter_name = '%Transactions/sec%'
AND instance_name = '_Total'; -- Database name
-- delay time
WAITFOR DELAY '00:00:01'
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'SQLServer:Databases' -- Change name of your server
AND counter_name = '%Transactions/sec%'
AND instance_name = '_Total'; -- Database name
SELECT (@Second - @First) 'TotalTransactions'
GO

If you are having some problem in execution, please confirm the OBJECT_NAME and instance_name by running select * FROM sys.dm_os_performance_counters. You can change the WAITFOR DELAY time to meet your time interval requirement. I have used currently 1 Second.

No comments:

Post a Comment