SQL Server Transactions Per Day

This post can help you in capturing SQL Server Transactions per day. Sometimes we need to capture the average number of transactions per day / hour / minute. Below is the T-SQL script that can help you to capture the SQL Server Transactions per day. Below script returns two result sets:

1. Retrieves the total transactions occurred in SQL Server Instance since last restart
2. Database Wise Average Transactions since last restart

T-SQL to capture SQL Server Transactions per day:

/************************************************************/
/******* SQL Server Transactions Per Day / Hour / Min *******/
/******* Tested : SQL Server 2008 R2, 2012, 2014 ************/
/******* Author : udayarumilli.com **************************/
/************************************************************/
DECLARE	@Days	SMALLINT,
		@Hours		INT,
		@Minutes	BIGINT,
		@Restarted_Date DATETIME;

/*** Capture the SQL Server instance last restart date ***/
/*** We will gte the Tempdb creation date ***/
SELECT  @Days = DATEDIFF(D, create_date, GETDATE()),
		@Restarted_Date = create_date
FROM    sys.databases
WHERE   database_id = 2;

/*** Prepare Number of Days and Hours Since the last SQL Server restart ***/
SELECT @Days = CASE WHEN @Days = 0 THEN 1 ELSE @Days END; 
SELECT @Hours = @Days * 24; 
SELECT @Minutes = @Hours * 60; 


/*** Retrieve the total transactions occurred in SQL Server Instance since last restart ***/
SELECT  @Restarted_Date			AS 'Last_Restarted_On',
		@@SERVERNAME		AS 'Instance_Name',
		cntr_value		AS 'Total_Trans_Since_Last_Restart',
		cntr_value / @Days	AS 'Avg_Trans_Per_Day',
		cntr_value / @Hours	AS 'Avg_Trans_Per_Hour',
		cntr_value / @Minutes	AS 'Avg_Trans_Per_Min'
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Transactions/sec'
        AND instance_name = '_Total';


/*** Database Wise Average Transactions since last restart ***/
SELECT  @Restarted_Date			AS 'Last_Restarted_On',
		@@SERVERNAME		AS 'Instance_Name',
		instance_name		AS 'Database_Name',
		cntr_value		AS 'Total_Trans_Since_Last_Restart',
		cntr_value / @Days	AS 'Avg_Trans_Per_Day',
		cntr_value / @Hours	AS 'Avg_Trans_Per_Hour',
		cntr_value / @Minutes	AS 'Avg_Trans_Per_Min'
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Transactions/sec'
        AND instance_name <> '_Total'
ORDER BY cntr_value DESC;

Here is the script file: SQL_Server_Transactions_Per_Day

These are the average values. To get the exact transaction details we can automate the capture process:

• Create a table to capture the transaction counts since last restart
• Create a stored procedure to capture the transactions since the last restart
• Schedule a job to execute the procedure on hourly basis
• Write a query to capture the hourly differences