-
Notifications
You must be signed in to change notification settings - Fork 17
/
GetOpenTransactions.sql
152 lines (149 loc) · 9.02 KB
/
GetOpenTransactions.sql
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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
/*
Part of PSBlitz - https://github.com/VladDBA/PSBlitz
License - https://github.com/VladDBA/PSBlitz/blob/main/LICENSE
*/
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @DatabaseName NVARCHAR(256);
SET @DatabaseName = N'';
/*
This is a fix for an edge case that causes the same record(s) to show up multiple times
- can't do SELECT DISTINCT when XML data is involved, so I'll split this up using a CTE
*/
WITH qcte
AS (SELECT DISTINCT GETDATE() AS [time_of_check],
DB_NAME([es].[database_id]) AS [database_name],
[s].[session_id],
[re].[blocking_session_id],
[re].[wait_type],
( [re].[wait_time] / 1000.00 ) AS [wait_time_seconds],
[re].[wait_resource],
[re].[command],
[es].[status] AS [session_status],
[re].[status] AS [current_reuqest_status],
[t].[name] AS [transaction_name],
[s].[open_transaction_count],
[t].[transaction_begin_time],
CASE
WHEN [t].[transaction_type] = 1 THEN 'Read/write transaction'
WHEN [t].[transaction_type] = 2 THEN 'Read-only transaction'
WHEN [t].[transaction_type] = 3 THEN 'System transaction'
WHEN [t].[transaction_type] = 4 THEN 'Distributed transaction'
END AS [transaction_type],
CASE
WHEN [t].[transaction_state] = 0 THEN 'The transaction has not been completely initialized yet'
WHEN [t].[transaction_state] = 1 THEN 'The transaction has been initialized but has not started'
WHEN [t].[transaction_state] = 2 THEN 'The transaction is active'
WHEN [t].[transaction_state] = 3 THEN 'The transaction has ended. This is used for read-only transactions'
WHEN [t].[transaction_state] = 4 THEN 'The commit process has been initiated on the distributed transaction.'
+ ' The distributed transaction is still active but further processing cannot take place.'
WHEN [t].[transaction_state] = 5 THEN 'The transaction is in a prepared state and waiting resolution'
WHEN [t].[transaction_state] = 6 THEN 'The transaction has been committed'
WHEN [t].[transaction_state] = 7 THEN 'The transaction is being rolled back'
WHEN [t].[transaction_state] = 8 THEN 'The transaction has been rolled back'
END AS [transaction_state],
ISNULL([re].[start_time], [es].[last_request_start_time]) AS [request_start_time],
CASE
WHEN [re].[start_time] IS NOT NULL THEN NULL
ELSE [es].[last_request_end_time]
END AS [request_end_time],
CASE
WHEN [re].[start_time] IS NOT NULL THEN DATEDIFF(second, [re].[start_time], GETDATE())
ELSE NULL
END AS [active_request_elapsed_seconds],
[es].[host_name],
[es].[login_name],
[es].[program_name],
[es].[client_interface_name],
[sqltext].[text] AS [current_sql],
[current_qs].[plan_handle] AS [current_plan_handle],
/*
Because [most_recent_sql_handle] = [sql_handle] for running sessions,
there's no need to show the same info twice (as current and most recent sql & plan)
*/
CASE
WHEN [es].[status] = N'running'
AND [conn].[most_recent_sql_handle] = [re].[sql_handle] THEN NULL
ELSE [sqltext_rec].[text]
END AS [most_recent_sql],
CASE
WHEN [es].[status] = N'running'
AND [conn].[most_recent_sql_handle] = [re].[sql_handle] THEN NULL
ELSE [recent_qs].[plan_handle]
END AS [most_recent_plan_handle],
[conn].[most_recent_sql_handle],
[re].[sql_handle]
FROM sys.[dm_tran_session_transactions] AS [s]
INNER JOIN sys.[dm_tran_active_transactions] AS [t]
ON [s].[transaction_id] = [t].[transaction_id]
LEFT JOIN sys.[dm_exec_sessions] AS [es]
ON [es].[session_id] = [s].[session_id]
LEFT JOIN sys.[dm_exec_connections] AS [conn]
ON [conn].[session_id] = [s].[session_id]
LEFT JOIN sys.[dm_exec_requests] AS [re]
ON [s].[session_id] = [re].[session_id]
LEFT JOIN sys.[dm_exec_query_stats] AS [recent_qs]
ON [conn].[most_recent_sql_handle] = [recent_qs].[sql_handle]
LEFT JOIN sys.[dm_exec_query_stats] AS [current_qs]
ON [re].[sql_handle] = [current_qs].[sql_handle]
OUTER APPLY sys.dm_exec_sql_text([re].[sql_handle]) AS [sqltext]
OUTER APPLY sys.dm_exec_sql_text([conn].[most_recent_sql_handle]) AS [sqltext_rec]
WHERE [es].[database_id] = CASE
WHEN @DatabaseName <> N'' THEN DB_ID(@DatabaseName)
ELSE [es].[database_id]
END)
SELECT [qcte].[time_of_check],
[qcte].[database_name],
[qcte].[session_id],
[qcte].[blocking_session_id],
[qcte].[wait_type],
[qcte].[wait_time_seconds],
[qcte].[wait_resource],
[qcte].[command],
[qcte].[session_status],
[qcte].[current_reuqest_status],
[qcte].[transaction_name],
[qcte].[open_transaction_count],
[qcte].[transaction_begin_time],
[qcte].[transaction_type],
[qcte].[transaction_state],
[qcte].[request_start_time],
[qcte].[request_end_time],
[qcte].[active_request_elapsed_seconds],
[qcte].[host_name],
[qcte].[login_name],
[qcte].[program_name],
[qcte].[client_interface_name],
[qcte].[current_sql],
CASE
WHEN [qcte].[current_sql] IS NOT NULL THEN 'Current_'
+ CAST([qcte].[session_id] AS VARCHAR(10))
+ '.query'
ELSE ''
END AS [current_query],
[sqlplan_curr].[query_plan] AS [current_plan],
CASE
WHEN [sqlplan_curr].[query_plan] IS NOT NULL THEN 'OpenTranCurrent_'
+ CAST([qcte].[session_id] AS VARCHAR(10))
+ '.sqlplan'
ELSE '-- N/A --'
END AS [current_plan_file],
[qcte].[most_recent_sql],
CASE
WHEN [qcte].[most_recent_sql] IS NOT NULL THEN 'MostRecent_'
+ CAST([qcte].[session_id] AS VARCHAR(10))
+ '.query'
ELSE ''
END AS [most_recent_query],
[sqlplan_rec].[query_plan] AS [most_recent_plan],
CASE
WHEN [sqlplan_rec].[query_plan] IS NOT NULL THEN 'OpenTranRecent_'
+ CAST([qcte].[session_id] AS VARCHAR(10))
+ '.sqlplan'
ELSE '-- N/A --'
END AS [most_recent_plan_file]
FROM [qcte]
OUTER APPLY sys.dm_exec_query_plan([qcte].[current_plan_handle]) AS [sqlplan_curr]
OUTER APPLY sys.dm_exec_query_plan([qcte].[most_recent_plan_handle]) AS [sqlplan_rec]
OPTION(RECOMPILE);