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:
- Retrieves the total transactions occurred in SQL Server Instance since last restart
- Database Wise Average Transactions since last restart
T-SQL to capture SQL Server Transactions per day:
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
|
/************************************************************/
/******* 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:
|
