In one of my recent Performance Tuning assignment I was asked how do someone know how many transactions are happening on server during certain interval. I had handy script for the same. Following script displays transactions happened on server at the interval of one minute. You can change the WAITFOR DELAY to any other interval and it should work.
-- First PASS
DECLARE @First INT
DECLARE @Second INT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$SQLENT1: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 = 'MSSQL$SQLENT1:Databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = '_Total';
SELECT (@Second - @First) 'TotalTransactions'
GO
If you are using any other method to detect transactions per interval, I request you to post it over here.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
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
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