1
2 /*
3 ** sp_monitor_deadlock
4 **
5 ** The documented user-interface is via 'sp_monitor deadlock'.
6 ** Caller might invoke us in the following ways:
7 **
8 ** -- With no arguments, process deadlock info from monDeadLock table:
9 ** sp_monitor deadlock
10 **
11 ** -- Process monDeadLock for all deadlocks on a given date entry.
12 ** sp_monitor deadlock, "Apr 27 2006"
13 **
14 ** -- Asking us to archive data in the monDeadLock table in the current db:
15 ** sp_monitor "archive", 'deadlock'
16 **
17 ** -- Asking us to archive data in the monDeadLock table in tempdb db:
18 ** tempdb..sp_monitor "archive", deadlock
19 **
20 ** -- Process all rows from the archive table (monDeadLock) in the
21 ** -- current db.
22 ** sp_monitor [, 'report' , ] deadlock
23 ** sp_monitor 'report', deadlock
24 **
25 ** -- If you have archived data in default archive-named 'monDeadLock',
26 ** -- then the following commands works identically when issued from the
27 ** -- archive db (e.g. mondb), or from 'master'. It will simply process
28 ** -- the data from 'monDeadLock' in the current db.
29 **
30 ** sp_monitor deadlock
31 ** tempdb..sp_monitor deadlock
32 ** tempdb..sp_monitor report, deadlock
33 ** master..sp_monitor report, deadlock
34
35 ** -- Process all rows from the archived table, 'daily_monDeadLock' in
36 ** -- pubs2:
37 ** pubs2..sp_monitor "report using prefix=daily_", deadlock
38 **
39 ** -- Process deadlock_info table for a given deadlock ID, from an archive
40 ** -- table named "daily_monDeadLock".
41 ** --
42 ** sp_monitor "report using prefix=daily_", deadlock, "3456"
43 **
44 ** Parameters:
45 ** @deadlock_tabname - Name of table to process deadlock events from.
46 ** @dlkID_or_for_Date - Gives either the deadlock ID to process,
47 ** or the date (ResolveTime) on which all
48 ** deadlock events have to be processed.
49 ** @output - Output mode (terse/verbose).
50 {
51 */
52 create procedure sp_monitor_deadlock(
53 @arg1 varchar(30) = NULL
54 , @arg2 varchar(30) = NULL
55 , @arg3 varchar(30) = NULL
56 ) as
57 begin
58 declare @sqlstmt varchar(2100)
59 , @dbname varchar(30)
60 , @num_deadlocks int
61 , @retval int -- from sproc.
62 , @mdacfg_retval int
63 , @num_rows int -- affected/inserted.
64 , @sel_stmt varchar(1800) -- to define SELECT list.
65 , @for_item varchar(10) -- 'deadlockID' or 'spid'
66 , @crt_newarchive tinyint
67 , @do_freeze_mda tinyint
68 , @datestamp datetime
69
70 , @master_monDeadLock varchar(22) -- master.dbo.
71 , @mondeadlock varchar(11) -- monDeadLock
72 , @deadlock_tabname varchar(30)
73 , @dlkID_or_for_Date varchar(30)
74 , @output_mode varchar(30)
75
76 -- Various output options.
77 , @output_count_by_date varchar(20)
78 , @output_count_by_appln varchar(20)
79 , @output_count_by_object varchar(20)
80 , @output_count_by_date_object varchar(25)
81 , @output_verbose varchar(7)
82 , @output_pagediag varchar(8)
83
84 , @opt_verbose int
85 , @opt_pagediag int
86 , @trace int
87
88 -- Supported output options:
89 select @output_count_by_date = 'count by date'
90 , @output_count_by_appln = 'count by application'
91 , @output_count_by_object = 'count by object'
92 , @output_count_by_date_object = 'count by date, object'
93 , @output_verbose = 'verbose'
94 , @output_pagediag = 'pagediag'
95
96 -- Set some defaults to avoid errors
97 , @deadlock_tabname = NULL
98
99 -- Print usage help if no args are provided, or 'help' is requested.
100 --
101 if (@arg1 = 'help')
102 begin
103 exec sp_monitor_deadlock_usage
104 @arg2 -- Gives verbosity level
105 , @output_count_by_date
106 , @output_count_by_appln
107 , @output_count_by_object
108 , @output_count_by_date_object
109 , @output_verbose
110 , @output_pagediag
111 return 0
112 end
113
114 select @sel_stmt = object_name(@@procid, db_id('sybsystemprocs'))
115 exec @retval = sp_monitor_check_permission @sel_stmt
116 if (@retval != 0)
117 return @retval
118
119 -- Do routine monitoring specific validation for setup/configuration.
120 --
121 select @sqlstmt = 'deadlock'
122 exec @retval = sp_monitor_verify_setup @sqlstmt
123
124 exec @mdacfg_retval = sp_monitor_verify_cfgval @sqlstmt
125
126 -- Bail out if there were any errors.
127 if (@retval != 0)
128 return @retval
129 else if (@mdacfg_retval != 0)
130 return @mdacfg_retval
131
132 set nocount on
133
134 select @do_freeze_mda = 0
135 , @mondeadlock = 'monDeadLock'
136
137 select @master_monDeadLock = "master.dbo." + @mondeadlock
138
139 exec @trace = sp_monitor_trace_level
140
141 -- ==================================================================
142 -- Process the 'archive' mode first; If table exists, insert to it,
143 -- or else, create a new archive table using SELECT-INTO.
144 --
145 if (@arg1 LIKE 'archive%')
146 begin
147 exec @retval = sp_monitor_archive 'deadlock'
148 , @arg1
149 , @trace
150 return @retval
151
152 end
153
154 -- ==================================================================
155 -- IF the user has asked to report from an archive, parse that command,
156 -- and setup out the archive table name.
157 --
158 else if (@arg1 LIKE 'report%')
159 begin
160 exec @retval = sp_monitor_parse_archive_cmd
161 @arg1
162 , 'report'
163 , 'report [using prefix=<string>]'
164 , @deadlock_tabname output
165
166 if (@retval != 0)
167 return @retval
168
169 -- Create the full name of the archive table using the
170 -- extracted prefix sub-string.
171 --
172 select @deadlock_tabname = @deadlock_tabname
173 + @mondeadlock
174
175 -- Setup local vars for remaining args.
176 select @dlkID_or_for_Date = @arg2
177 , @output_mode = @arg3
178 end
179 else
180 begin
181 -- Setup local vars for remaining args.
182 select @dlkID_or_for_Date = @arg1
183 , @output_mode = @arg2
184
185 -- Default events table name is this one.
186 , @deadlock_tabname = @mondeadlock
187 end
188
189 select @dbname = db_name()
190
191 -- ==================================================================
192 -- Validate that the requested deadlock events table name existst.
193 -- Otherwise bail. Note that by now if we are running in report mode,
194 -- @deadlock_tabname would be the name of the archived table.
195 --
196 if (object_id(@deadlock_tabname) IS NULL)
197 begin
198 select @sqlstmt = 'dbo.' + @deadlock_tabname
199 raiserror 17870, @sqlstmt, @dbname, ""
200
201 -- Disallow certain key system-dbs for archival uses.
202 -- Guide the user if their current db is one of the restricted
203 -- ones, and they are doing a report.
204 --
205 if ((@arg1 LIKE 'report%')
206 and @dbname IN ('master'
207 , 'model'
208 , 'sybsystemprocs'
209 , 'sybsystemdb'
210 , 'sybsecurity'
211 )
212 )
213 begin
214 print "Cannot use database '%1!' as a database to archive data."
215 , @dbname
216 end
217
218 return 1
219 end
220
221 -- If no cached archive tablename was given, then process data
222 -- real-time from monDeadLock table. In this case, produce the
223 -- extra columns that would normally appear in an archived table.
224 -- If the archive name was provided (and found), simply select all
225 -- the columns from it.
226 --
227 if (@deadlock_tabname = @mondeadlock) and (db_name() = 'master')
228 begin
229 -- We have to freeze MDA tables as this branch
230 -- *does* SELECT from monDeadLock directly.
231 --
232 select @do_freeze_mda = 1
233 , @datestamp = getdate()
234
235 -- This SELECT should re-generate all the columns that
236 -- would normally be created in the archive from
237 -- #mon__deadlock_info
238 --
239 exec @retval = sp_mon_archive_genSQL @mondeadlock
240 , @datestamp
241 , @sel_stmt output
242
243 end
244 else
245 begin
246 -- We are processing data from an archived table. This
247 -- archive was (or should have been) created by this sproc
248 -- interface. Select all the columns from that archive, so
249 -- that we have all the event-related information in one place.
250 --
251 select @sel_stmt = 'SELECT * '
252 end /* Adaptive Server has expanded all '*' elements in the following statement */
253
254 -- ==================================================================
255 -- Cache the info from @deadlock_tabname into a #temp table in tempdb
256 -- that is created as a copy of monDeadLock so that can iterate through
257 -- it subsequently to generate a report for interesting deadlock IDs.
258 --
259 select master.dbo.monDeadLock.DeadlockID, master.dbo.monDeadLock.VictimKPID, master.dbo.monDeadLock.InstanceID, master.dbo.monDeadLock.ResolveTime, master.dbo.monDeadLock.ObjectDBID, master.dbo.monDeadLock.ObjectID, master.dbo.monDeadLock.PageNumber, master.dbo.monDeadLock.RowNumber, master.dbo.monDeadLock.HeldFamilyID, master.dbo.monDeadLock.HeldSPID, master.dbo.monDeadLock.HeldKPID, master.dbo.monDeadLock.HeldInstanceID, master.dbo.monDeadLock.HeldProcDBID, master.dbo.monDeadLock.HeldProcedureID, master.dbo.monDeadLock.HeldBatchID, master.dbo.monDeadLock.HeldContextID, master.dbo.monDeadLock.HeldLineNumber, master.dbo.monDeadLock.HeldStmtNumber, master.dbo.monDeadLock.HeldNumLocks, master.dbo.monDeadLock.WaitFamilyID, master.dbo.monDeadLock.WaitSPID, master.dbo.monDeadLock.WaitKPID, master.dbo.monDeadLock.WaitProcDBID, master.dbo.monDeadLock.WaitProcedureID, master.dbo.monDeadLock.WaitBatchID, master.dbo.monDeadLock.WaitContextID, master.dbo.monDeadLock.WaitLineNumber, master.dbo.monDeadLock.WaitStmtNumber, master.dbo.monDeadLock.WaitTime, master.dbo.monDeadLock.ObjectDBName, master.dbo.monDeadLock.ObjectName, master.dbo.monDeadLock.HeldUserName, master.dbo.monDeadLock.HeldApplName, master.dbo.monDeadLock.HeldHostName, master.dbo.monDeadLock.HeldClientName, master.dbo.monDeadLock.HeldClientHostName, master.dbo.monDeadLock.HeldClientApplName, master.dbo.monDeadLock.HeldTranName, master.dbo.monDeadLock.HeldLockType, master.dbo.monDeadLock.HeldCommand, master.dbo.monDeadLock.HeldProcDBName, master.dbo.monDeadLock.HeldProcedureName, master.dbo.monDeadLock.WaitUserName, master.dbo.monDeadLock.WaitApplName, master.dbo.monDeadLock.WaitHostName, master.dbo.monDeadLock.WaitClientName, master.dbo.monDeadLock.WaitClientHostName, master.dbo.monDeadLock.WaitClientApplName, master.dbo.monDeadLock.WaitLockType, master.dbo.monDeadLock.WaitTranName, master.dbo.monDeadLock.WaitCommand, master.dbo.monDeadLock.WaitProcDBName, master.dbo.monDeadLock.WaitProcedureName, master.dbo.monDeadLock.HeldSourceCodeID, master.dbo.monDeadLock.WaitSourceCodeID
260
261 -- This piece is hand-copied from the SQL generated by:
262 -- sp_mon_archive_genSQL monDeadLock, 'May 25 2006', 'yyy', 'zzz', 1
263
264 -- Columns generated using sp_mon_archive_genSQL
265 -- edited for readability with some white-spaces.
266 --
267 , InstanceName = convert(varchar(30) NULL,
268 instance_name(InstanceID))
269 , ServerName = convert(varchar(30) NULL, @@servername)
270 , ArchiveDate = convert(datetime NULL, 'May 25 2006 12:00AM')
271 , VersionNum = convert(int NOT NULL, 1)
272 , ESDNum = convert(int NOT NULL, 0)
273 , EBFNum = convert(int NOT NULL, 0)
274
275 into #mon__deadlock_info
276 from master.dbo.monDeadLock
277 where 1 = 0
278
279 -- The table alias 'm' is essential as one of the generated SQL
280 -- fragments uses this 'm.' notation to disambiguate
281 -- column name references.
282 --
283 select @sqlstmt = 'INSERT #mon__deadlock_info '
284 + @sel_stmt
285 + " FROM " + @dbname + '..' + @deadlock_tabname + " m"
286 + " WHERE 1=1 "
287
288 -- ==================================================================
289 -- Filter out deadlocks occuring on requested date, if any.
290 --
291 if (@dlkID_or_for_Date IS NOT NULL)
292 begin
293 -- Check if user has passed in a deadlockID; i.e. all digits.
294 if (patindex("%[^0-9]%", @dlkID_or_for_Date) = 0)
295 begin
296 select @sqlstmt = @sqlstmt
297 + " AND DeadlockID="
298 + @dlkID_or_for_Date
299
300 , @for_item = 'DeadlockID'
301 end
302 else
303 begin
304 -- Assume that the user is playing fair and will
305 -- only pass in valid dates. If not, the following
306 -- SQL will barf any way, and they'll get an error.
307 --
308 select @sqlstmt = @sqlstmt
309 + " AND convert(date, ResolveTime)='"
310 + @dlkID_or_for_Date + "'"
311 , @for_item = 'date'
312 end
313 end
314
315 -- ==================================================================
316 -- This creates the cached #mon__deadlock_info table on which
317 -- the rest of the SQL code operates.
318 --
319 if (@do_freeze_mda = 1)
320 select @mdacfg_retval = mdaconfig('freeze', 'begin')
321
322 -- No more accesses to master.dbo.monDeadLock should happen after here.
323 exec @retval = sp_exec_SQL @sqlstmt, 'sp_monitor_deadlock'
324 , NULL, @trace
325
326 if (@do_freeze_mda = 1)
327 select @mdacfg_retval = mdaconfig('freeze', 'end')
328
329 if (@retval != 0)
330 begin
331 return @retval
332 end
333
334 -- ==================================================================
335 -- Process the output modes, displaying summary only if so
336 -- requested. Then bail.
337 -- NOTE: If the user has specified any filters, they are applied
338 -- first before generating the GROUP BY result. So, users can check
339 -- for frequency of deadlocks by 'count by object' for a given
340 -- ResolveTime (date), for instance.
341 --
342 if (@output_mode LIKE "%count by%")
343 begin
344 exec @retval = sp_monitor_deadlock_count_by
345 @deadlock_tabname
346 , @dbname
347 , "#mon__deadlock_info"
348 , @output_count_by_date
349 , @output_count_by_appln
350 , @output_count_by_object
351 , @output_count_by_date_object
352 , @output_mode
353 return @retval
354 end
355
356 -- ==================================================================
357 -- Extract other possible print/output options
358
359 exec @retval = sp_monitor_deadlock_getopts
360 @output_mode
361
362 , @output_verbose
363 , @output_pagediag
364 , @opt_verbose output
365 , @opt_pagediag output
366 if (@retval != 0)
367 return @retval
368
369 -- ==================================================================
370 -- Process the data from the #mon__deadlock_info table.
371 --
372 exec sp_monitor_deadlock_driver -- @deadlock_tabname
373 @opt_verbose
374 , @opt_pagediag
375 , @num_deadlocks output
376
377 -- Print summary statistics of run.
378 --
379 print " "
380 select @sqlstmt = @dbname + '.dbo.' + @deadlock_tabname
381 if (@dlkID_or_for_Date IS NULL)
382 begin
383 print "Total of %1! deadlocks were analyzed in this run from '%2!'."
384 , @num_deadlocks, @sqlstmt
385 end
386 else
387 begin
388 print "Total of %1! deadlocks were analyzed in this run for %2! %3! from '%4!'."
389 , @num_deadlocks, @for_item, @dlkID_or_for_Date
390 , @sqlstmt
391 end
392
393 -- Cleanup before exiting.
394 if (object_id("#mon__deadlock_info") IS NOT NULL)
395 drop table #mon__deadlock_info
396
397 end -- }
398
exec sp_procxmode 'sp_monitor_deadlock', 'AnyMode'
go
Grant Execute on sp_monitor_deadlock to public
go