· 3 years ago · Feb 02, 2022, 03:00 PM
1------------------------------------
2-- Create schema
3------------------------------------
4
5IF NOT EXISTS ( SELECT *
6 FROM sys.schemas
7 WHERE name = N'xe' )
8 EXEC('CREATE SCHEMA xe');
9GO
10
11------------------------------------
12-- Create and start event session
13------------------------------------
14IF EXISTS(SELECT 1 FROM sys.dm_xe_sessions xe WHERE xe.[name] = 'monitor_sp_durations') DROP EVENT SESSION [monitor_sp_durations] ON SERVER;
15
16CREATE EVENT SESSION [monitor_sp_durations] ON SERVER
17ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
18 ACTION(sqlos.task_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.username)
19 WHERE (
20 [sqlserver].[is_system]=(0)
21 AND sqlserver.database_name != N'SSISDB'
22 AND sqlserver.database_name != N'msdb'
23 AND sqlserver.database_name != N'master'
24 AND duration >= 100000
25 )),
26ADD EVENT sqlserver.module_end(SET collect_statement=(1)
27 ACTION(sqlos.task_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.username)
28 WHERE (
29 [sqlserver].[is_system]=(0)
30 AND object_type='P '
31 AND sqlserver.database_name != N'SSISDB'
32 AND sqlserver.database_name != N'msdb'
33 AND sqlserver.database_name != N'master'
34 AND duration >= 100000
35 ))
36ADD TARGET package0.ring_buffer(SET max_memory=(20480))
37WITH (EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,STARTUP_STATE=OFF,MAX_DISPATCH_LATENCY=30 SECONDS)
38GO
39
40ALTER EVENT SESSION [monitor_sp_durations] ON SERVER
41STATE = START;
42
43------------------------------------
44-- Create tables to log data and view to consume
45------------------------------------
46IF OBJECT_ID('xe.LoggedProcedures') IS NOT NULL DROP TABLE [xe].[LoggedProcedures];
47CREATE TABLE [xe].[LoggedProcedures](
48 [RowHash] [binary](16) NOT NULL
49 CONSTRAINT [PK_LoggedProcedures] PRIMARY KEY CLUSTERED ([RowHash] ASC),
50 [ObjectName] [nvarchar](128) NULL,
51 [ExecStatement] [nvarchar](4000) NULL,
52 [EventName] [varchar](64) NOT NULL,
53 [DatabaseName] [varchar](64) NOT NULL,
54 [ClientAppName] [varchar](255) NULL,
55 [ClientHostName] [varchar](255) NULL,
56 [ServerPrincipalName] [nvarchar](255) NULL,
57 [UserName] [nvarchar](128) NULL,
58 [ObjectId] [int] NULL,
59);
60
61IF OBJECT_ID('xe.ProcedureExecutions') IS NOT NULL DROP TABLE [xe].[ProcedureExecutions];
62CREATE TABLE [xe].[ProcedureExecutions](
63 [Id] [int] IDENTITY(1,1) NOT NULL
64 CONSTRAINT [PK_ProcedureExecutions] PRIMARY KEY CLUSTERED ([Id] ASC),
65 [LoggedProceduresRowHash] [int] NOT NULL,
66 [EventTimeUTC] [datetime] NOT NULL,
67 [Duration] [bigint] NOT NULL,
68 [RowsAffected] [bigint] NULL,
69);
70
71CREATE NONCLUSTERED INDEX NCIX_RowHash ON [xe].[ProcedureExecutions]([LoggedProceduresRowHash]);
72GO
73
74
75------------------------------------
76-- Create procedure to write events to permanent tables
77------------------------------------
78IF OBJECT_ID('xe.ShredProcedureEvents') IS NOT NULL DROP PROCEDURE [xe].[ShredProcedureEvents];
79GO
80
81CREATE PROCEDURE xe.ShredProcedureEvents
82 @MinuteLookback int
83AS
84BEGIN
85 SET NOCOUNT ON;
86 --Inspired by this SA question: https://dba.stackexchange.com/questions/121491/how-can-i-shred-this-extended-events-xml-as-fast-as-possible
87
88 DECLARE @xml XML;
89 DECLARE @FilterDate datetime2 = (SELECT DATEADD(minute, -@MinuteLookback, GETUTCDATE()));
90
91 SELECT TOP(1) @xml = CONVERT(xml, xet.target_data)
92 FROM sys.dm_xe_session_targets AS xet
93 INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
94 WHERE xe.name = 'monitor_sp_durations';
95
96 IF OBJECT_ID('tempdb..#XmlResults') IS NOT NULL
97 DROP TABLE #XmlResults;
98 IF OBJECT_ID('tempdb..#TabularResults') IS NOT NULL
99 DROP TABLE #TabularResults;
100
101
102 CREATE TABLE #XmlResults
103 (
104 RowNum INT NOT NULL
105 PRIMARY KEY CLUSTERED
106 IDENTITY(1,1)
107 , xeXML XML NOT NULL
108 );
109
110 INSERT INTO #XmlResults (xeXML)
111 SELECT xm.s.query('.')
112 FROM @xml.nodes('/RingBufferTarget/event') AS xm(s)
113 OPTION (Optimize FOR (@xml = NULL)); --Allegedly useful for SQL 2008
114
115 --SELECT *
116 --FROM #XmlResults;
117 WITH XmlParse AS (
118 SELECT EventName = xeXML.value('(event/@name)[1]','varchar(64)')
119 , EventDateStamp = xeXML.value('(event/@timestamp)[1]','datetime2')
120 , DatabaseName = xeXML.value('(event/action[(@name)[1] eq "database_name"]/value/text())[1]','varchar(64)')
121 , ClientAppName = xeXML.value('(event/action[(@name)[1] eq "client_app_name"]/value/text())[1]','varchar(255)')
122 , ClientHostName = xeXML.value('(event/action[(@name)[1] eq "client_hostname"]/value/text())[1]','varchar(255)')
123 , ServerPrincipalName = xeXML.value('(event/action[(@name)[1] eq "server_principal_name"]/value/text())[1]','nvarchar(255)')
124 , UserName = xeXML.value('(event/action[(@name)[1] eq "username"]/value/text())[1]','nvarchar(128)')
125 , ObjectId = xeXML.value('(event/data[(@name)[1] eq "object_id"]/value/text())[1]','int')
126 , ObjectName = xeXML.value('(event/data[(@name)[1] eq "object_name"]/value/text())[1]','nvarchar(128)')
127 , Duration = xeXML.value('(event/data[(@name)[1] eq "duration"]/value/text())[1]','bigint')
128 , RowsAffected = xeXML.value('(event/data[(@name)[1] eq "row_count"]/value/text())[1]','bigint')
129 , ExecStatement = xeXML.value('(event/data[(@name)[1] eq "statement"]/value/text())[1]','nvarchar(4000)')
130 FROM #XmlResults xm
131 )
132
133 SELECT *
134 , HASHBYTES('MD5',(SELECT EventName, DatabaseName, ClientAppName, ClientHostName, ServerPrincipalName, UserName, ObjectId, ObjectName, ExecStatement FROM (values(null))foo(bar) FOR XML AUTO)) AS [RowHash]
135 INTO #TabularResults
136 FROM XmlParse
137 WHERE EventDateStamp>@FilterDate;
138
139 --SELECT * FROM #TabularResults
140
141 INSERT INTO xe.LoggedProcedures(RowHash, EventName, DatabaseName, ClientAppName, ClientHostName, ServerPrincipalName, UserName, ObjectId, ObjectName, ExecStatement)
142 SELECT DISTINCT RowHash
143 ,EventName, DatabaseName, ClientAppName, ClientHostName, ServerPrincipalName, UserName, ObjectId, ObjectName, ExecStatement
144 FROM #TabularResults t
145 WHERE RowHash NOT IN (SELECT RowHash FROM xe.LoggedProcedures)
146
147 INSERT INTO xe.ProcedureExecutions([LoggedProceduresRowHash], [EventTimeUTC], Duration, RowsAffected)
148 SELECT RowHash, EventDateStamp, Duration, RowsAffected
149 FROM #TabularResults
150END
151GO
152
153------------------------------------
154-- Create job to schedule persisting events from ringbuffer to tables
155------------------------------------
156
157IF EXISTS (SELECT job_id
158 FROM msdb.dbo.sysjobs_view
159 WHERE name = N'ShredExtendedEvents') EXEC msdb.dbo.sp_delete_job @job_name=N'ShredExtendedEvents', @delete_unused_schedule=1;
160GO
161
162BEGIN TRANSACTION
163DECLARE @ReturnCode INT
164SELECT @ReturnCode = 0
165IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
166BEGIN
167EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
168IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
169
170END
171
172DECLARE @jobId BINARY(16)
173EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ShredExtendedEvents',
174 @enabled=1,
175 @notify_level_eventlog=0,
176 @notify_level_email=0,
177 @notify_level_netsend=0,
178 @notify_level_page=0,
179 @delete_level=0,
180 @description=N'Shreds extended event xml from ring buffer into tables',
181 @category_name=N'[Uncategorized (Local)]',
182 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
183IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
184
185EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Shred past 10 minutes of events',
186 @step_id=1,
187 @cmdexec_success_code=0,
188 @on_success_action=1,
189 @on_success_step_id=0,
190 @on_fail_action=2,
191 @on_fail_step_id=0,
192 @retry_attempts=0,
193 @retry_interval=0,
194 @os_run_priority=0, @subsystem=N'TSQL',
195 @command=N'EXEC xe.ShredProcedureEvents @MinuteLookback=10',
196 @database_name=N'DBAdmin',
197 @flags=0
198IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
199EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
200IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
201EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Shred_Every_10min',
202 @enabled=1,
203 @freq_type=4,
204 @freq_interval=1,
205 @freq_subday_type=4,
206 @freq_subday_interval=10,
207 @freq_relative_interval=0,
208 @freq_recurrence_factor=0,
209 @active_start_date=20200703,
210 @active_end_date=99991231,
211 @active_start_time=0,
212 @active_end_time=235959
213IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
214EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
215IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
216COMMIT TRANSACTION
217GOTO EndSave
218QuitWithRollback:
219 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
220EndSave:
221GO
222
223