· 5 years ago · Jul 04, 2020, 10:12 PM
1SET NOCOUNT ON;
2
3DECLARE @retention INT = 7,
4 @destination_table VARCHAR(500) = 'whoisactive_interim',
5 @destination_database sysname = 'ADS',
6 @schema VARCHAR(MAX),
7 @SQL NVARCHAR(4000),
8 @parameters NVARCHAR(500),
9 @exists BIT,
10 @minuteLimit INTEGER = 3;
11
12SET @destination_table = @destination_database + '.dbo.' + @destination_table;
13
14----- CREATE THE INTERIM TABLE TO COLLECT THE OUTPUT FROM USP
15IF OBJECT_ID(@destination_table) IS NULL
16 BEGIN;
17 EXEC [dbo].[sp_WhoIsActive] @get_transaction_info = 1,
18 @get_outer_command = 1,
19 @get_plans = 1,
20 @return_schema = 1,
21 @schema = @schema OUTPUT;
22 SET @schema = REPLACE(@schema, '<table_name>', @destination_table);
23 EXEC ( @schema );
24 END;
25
26----- CREATE INDEX ON COLLECTION_TIME
27
28SET @SQL
29 = 'USE ' + QUOTENAME(@destination_database)
30 + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
31SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
32EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;
33
34IF @exists = 0
35 BEGIN;
36 SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
37 EXEC ( @SQL );
38 END;
39
40----- COLLECT ACTIVITY INTO LOGGING TABLE
41EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
42 @get_outer_command = 1,
43 @get_plans = 1,
44 @destination_table = @destination_table;
45
46--------- PURGE DATA NEWER THAN n MINUTES
47SET @SQL
48 = 'DELETE FROM ' + @destination_table + ' WHERE [start_time] > DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE() )-' + CAST(@minuteLimit AS NVARCHAR(MAX)) + ', 0)';
49EXEC ( @SQL );
50
51
52----- MERGE RESULTS INTO FINAL TABLE
53MERGE [ADS].[dbo].[whoisactive_scd2] AS TARGET
54USING
55 (
56 SELECT
57 [dd hh:mm:ss.mss]
58 ,[session_id]
59 ,[sql_text]
60 --,[sql_command]
61 ,[login_name]
62 ,[wait_info]
63 ,[tran_log_writes]
64 ,[CPU]
65 ,[tempdb_allocations]
66 ,[tempdb_current]
67 ,[blocking_session_id]
68 ,[reads]
69 ,[writes]
70 ,[physical_reads]
71 --,[query_plan]
72 ,[used_memory]
73 ,[status]
74 ,[tran_start_time]
75 ,[open_tran_count]
76 ,[percent_complete]
77 ,[host_name]
78 ,[database_name]
79 ,[program_name]
80 ,[start_time]
81 ,[login_time]
82 ,[request_id]
83 ,[collection_time]
84 FROM [ADS].[dbo].[whoisactive_interim]
85 )
86 AS SOURCE
87 ON CAST(TARGET.[session_id] AS NVARCHAR(MAX)) = CAST(SOURCE.[session_id] AS NVARCHAR(MAX))
88 AND CAST(TARGET.[sql_text] AS NVARCHAR(MAX)) = CAST( SOURCE.[sql_text] AS NVARCHAR(MAX))
89 AND TARGET.[login_name] = SOURCE.[login_name]
90 AND TARGET.[program_name] = SOURCE.[program_name]
91WHEN MATCHED
92 THEN UPDATE SET
93 TARGET.[dd hh:mm:ss.mss] = SOURCE.[dd hh:mm:ss.mss]
94 --, TARGET.[sql_command] = SOURCE.[sql_command]
95 , TARGET.[wait_info] = SOURCE.[wait_info]
96 , TARGET.[tran_log_writes] = SOURCE.[tran_log_writes]
97 , TARGET.[CPU] = SOURCE.[CPU]
98 , TARGET.[tempdb_allocations] = SOURCE.[tempdb_allocations]
99 , TARGET.[tempdb_current] = SOURCE.[tempdb_current]
100 , TARGET.[blocking_session_id] = SOURCE.[blocking_session_id]
101 , TARGET.[reads] = SOURCE.[reads]
102 , TARGET.[writes] = SOURCE.[writes]
103 , TARGET.[physical_reads] = SOURCE.[physical_reads]
104 --, TARGET.[query_plan] = SOURCE.[query_plan]
105 , TARGET.[used_memory] = SOURCE.[used_memory]
106 , TARGET.[status] = SOURCE.[status]
107 , TARGET.[tran_start_time] = SOURCE.[tran_start_time]
108 , TARGET.[open_tran_count] = SOURCE.[open_tran_count]
109 , TARGET.[percent_complete] = SOURCE.[percent_complete]
110 , TARGET.[host_name] = SOURCE.[host_name]
111 , TARGET.[database_name] = SOURCE.[database_name]
112 , TARGET.[start_time] = SOURCE.[start_time]
113 , TARGET.[login_time] = SOURCE.[login_time]
114 , TARGET.[request_id] = SOURCE.[request_id]
115 , TARGET.[collection_time] = SOURCE.[collection_time]
116WHEN NOT MATCHED BY TARGET THEN
117 INSERT ([dd hh:mm:ss.mss],[session_id],[sql_text]
118 ,[login_name],[wait_info],[tran_log_writes],[CPU]
119 ,[tempdb_allocations],[tempdb_current],[blocking_session_id]
120 ,[reads],[writes],[physical_reads],[used_memory]
121 ,[status],[tran_start_time],[open_tran_count],[percent_complete]
122 ,[host_name],[database_name],[program_name],[start_time]
123 ,[login_time],[request_id],[collection_time])
124 VALUES (SOURCE.[dd hh:mm:ss.mss], SOURCE.[session_id], SOURCE.[sql_text]
125 , SOURCE.[login_name], SOURCE.[wait_info]
126 , SOURCE.[tran_log_writes], SOURCE.[CPU], SOURCE.[tempdb_allocations]
127 , SOURCE.[tempdb_current], SOURCE.[blocking_session_id], SOURCE.[reads]
128 , SOURCE.[writes], SOURCE.[physical_reads]
129 , SOURCE.[used_memory], SOURCE.[status], SOURCE.[tran_start_time]
130 , SOURCE.[open_tran_count], SOURCE.[percent_complete], SOURCE.[host_name]
131 , SOURCE.[database_name], SOURCE.[program_name], SOURCE.[start_time]
132 , SOURCE.[login_time], SOURCE.[request_id], SOURCE.[collection_time])
133 OUTPUT $action AS [action]
134;
135
136----- PURGE INTERIM TABLE
137SET @SQL
138 = 'DELETE FROM ' + @destination_table;
139EXEC ( @SQL );