Transaction count shows that how heavily the database is used. Some third-party applications can show the transaction count graphs of the databases. But each company may not buy these applications. This article will be useful if you would like to find out manually the transaction count in your system.
You can find the database transaction count since the start of SQL Server by using the following script.
|
1 2 3 4 5 |
--You should write your own database name instead of TestDB in the below script select instance_name AS 'Database_Name', cntr_value AS 'Transaction Count' from sys.dm_os_performance_counters WHERE counter_name = 'Transactions/sec' AND instance_name = 'TestDB'; |
You can find all database’s transaction count in the instance since the start of SQL Server by using the following script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DECLARE @DatabaseName VARCHAR(250) create table #TempTable (DatabaseName varchar(250),TransactionCount bigint) /*Give the Cursor a name*/ DECLARE cursordbnames CURSOR FOR /*This is the Select clause that returns the recordset to move the cursor.*/ SELECT name FROM sys.sysdatabases where dbid>4 OPEN cursordbnames FETCH NEXT FROM cursordbnames INTO @DatabaseName /*"WHILE @@ FETCH_STATUS = 0" means that you continue to move the cursor by going to the next record until there are no more records left in the cursor.*/ WHILE @@FETCH_STATUS =0 BEGIN Insert Into #TempTable(DatabaseName, TransactionCount) select instance_name 'Database Name', cntr_value 'Total Transaction Count' from sys.dm_os_performance_counters WHERE counter_name = 'Transactions/sec' AND instance_name = @DatabaseName; FETCH NEXT FROM cursordbnames INTO @DatabaseName END /*We close the Cursor using the "CLOSE" and "DEALLOCATE" commands.*/ CLOSE cursordbnames DEALLOCATE cursordbnames Select * From #TempTable order by TransactionCount Desc |
You can also calculate the database transaction count in a specific time interval. With the following script, let’s calculate a database transaction count in a specific time interval. In Script, we set this time interval as 1 minute. You can extend this interval if you like.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @Start BIGINT DECLARE @Finish BIGINT SELECT @Start = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Transactions/sec' AND instance_name = 'TestDB'; WAITFOR DELAY '00:01:00' SELECT @Finish = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Transactions/sec' AND instance_name = 'TestDB'; SELECT (@Finish - @Start) 'Transaction_Count_in_a_1_Minute' GO |
You can use the following script to calculate the transaction count in a specific time interval(Its 1 minute in our example) for all databases on Instance.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
DECLARE cursordbnames CURSOR FOR SELECT name FROM sys.sysdatabases where dbid>4 OPEN cursordbnames FETCH NEXT FROM cursordbnames INTO @DatabaseName WHILE @@FETCH_STATUS =0 BEGIN Insert Into #TempTable(DatabaseName, TransactionCount) select instance_name 'Database Name', cntr_value 'Total Transaction Count' from sys.dm_os_performance_counters WHERE counter_name = 'Transactions/sec' AND instance_name = @DatabaseName; FETCH NEXT FROM cursordbnames INTO @DatabaseName END CLOSE cursordbnames DEALLOCATE cursordbnames WAITFOR DELAY '00:01:00' create table #TempTable2 (DatabaseName varchar(250),TransactionCount bigint) DECLARE cursordbnames CURSOR FOR SELECT name FROM sys.sysdatabases where dbid>4 OPEN cursordbnames FETCH NEXT FROM cursordbnames INTO @DatabaseName WHILE @@FETCH_STATUS =0 BEGIN Insert Into #TempTable2(DatabaseName, TransactionCount) select instance_name 'Database Name', cntr_value 'Total Transaction Count' from sys.dm_os_performance_counters WHERE counter_name = 'Transactions/sec' AND instance_name = @DatabaseName; FETCH NEXT FROM cursordbnames INTO @DatabaseName END CLOSE cursordbnames DEALLOCATE cursordbnames select tmp1.DatabaseName,tmp2.TransactionCount-tmp1.TransactionCount 'Transaction_Count_in_a_Minute' from #TempTable tmp1 INNER JOIN #TempTable2 tmp2 ON tmp1.DatabaseName=tmp2.DatabaseName order by tmp2.TransactionCount-tmp1.TransactionCount desc DROP TABLE #TempTable DROP TABLE #TempTable2 |
