DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_deadlock  31 Aug 14Defects Dependencies

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
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 199
 MTYP 4 Assignment type mismatch @output_type: varchar(8) = varchar(30) 104
 MTYP 4 Assignment type mismatch @procname: varchar(30) = varchar(1800) 115
 MTYP 4 Assignment type mismatch @montype: varchar(30) = varchar(2100) 122
 MTYP 4 Assignment type mismatch @montype: varchar(10) = varchar(2100) 124
 MTYP 4 Assignment type mismatch @arch_prefix: varchar(8) = varchar(30) 164
 MTYP 4 Assignment type mismatch @trace: tinyint = int 324
 TNOI 4 Table with no index master..monDeadLock master..monDeadLock
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_deadlock  
 MNER 3 No Error Check should check return value of exec 103
 MNER 3 No Error Check should check return value of exec 122
 MNER 3 No Error Check should check return value of exec 139
 MNER 3 No Error Check should check return value of exec 147
 MNER 3 No Error Check should check return value of exec 239
 MNER 3 No Error Check should check @@error after select into 259
 MNER 3 No Error Check should check return value of exec 323
 MNER 3 No Error Check should check return value of exec 344
 MNER 3 No Error Check should check return value of exec 372
 MUCO 3 Useless Code Useless Brackets in create proc 52
 MUCO 3 Useless Code Useless Begin-End Pair 57
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 319
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 329
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 381
 MUCO 3 Useless Code Useless Brackets 394
 QAPT 3 Access to Proxy Table master..monDeadLock 276
 QPNC 3 No column in condition 277
 VNRD 3 Variable is not read @master_monDeadLock 137
 VNRD 3 Variable is not read @mdacfg_retval 327
 VUNU 3 Variable is not used @num_rows 63
 VUNU 3 Variable is not used @crt_newarchive 66
 MTR1 2 Metrics: Comments Ratio Comments: 41% 52
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 21dec - 8exi + 2 52
 MTR3 2 Metrics: Query Complexity Complexity: 93 52

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_monitor_verify_cfgval  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
      reads table master..syscurconfigs (1)  
      reads table master..sysconfigures (1)  
writes table tempdb..#mon__deadlock_info (1) 
calls proc sybsystemprocs..sp_exec_SQL  
calls proc sybsystemprocs..sp_monitor_archive  
   calls proc sybsystemprocs..sp_monitor_verify_setup  
      reads table master..sysobjects (1)  
      calls proc sybsystemprocs..sp_monitor_getcfgval  
      calls proc sybsystemprocs..sp_monitor_getcfgnum  
         reads table master..sysconfigures (1)  
   calls proc sybsystemprocs..sp_getmessage  
      reads table master..sysmessages (1)  
      reads table sybsystemprocs..sysusermessages  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
      reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_monitor_parse_archive_cmd  
      calls proc sybsystemprocs..sp_spaceusage_processusing  
         calls proc sybsystemprocs..sp_split_string  
      calls proc sybsystemprocs..sp_split_string  
   calls proc sybsystemprocs..sp_monitor_verify_cfgval  
   calls proc sybsystemprocs..sp_monitor_check_permission  
   calls proc sybsystemprocs..sp_monitor_trace_level  
   reads table master..sysdatabases (1)  
reads table master..monDeadLock (1)  
calls proc sybsystemprocs..sp_mon_archive_genSQL  
   calls proc sybsystemprocs..sp_versioncrack  
      calls proc sybsystemprocs..sp_split_string  
   calls proc sybsystemprocs..sp_mon_gen_columnlist  
      reads table master..sysobjects (1)  
      reads table master..syscolumns (1)  
calls proc sybsystemprocs..sp_monitor_verify_setup  
calls proc sybsystemprocs..sp_monitor_deadlock_count_by  
   calls proc sybsystemprocs..sp_autoformat  
      reads table tempdb..systypes (1)  
      reads table tempdb..syscolumns (1)  
      reads table master..systypes (1)  
      calls proc sybsystemprocs..sp_autoformat  
      read_writes table tempdb..#colinfo_af (1) 
      reads table master..syscolumns (1)  
      calls proc sybsystemprocs..sp_namecrack  
   writes table tempdb..#freq_by_resolvedate (1) 
   writes table tempdb..#freq_by_applname (1) 
   calls proc sybsystemprocs..sp_exec_SQL  
   writes table tempdb..#freq_by_date_name (1) 
   writes table tempdb..#freq_by_name (1) 
   calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysobjects (1)  
calls proc sybsystemprocs..sp_monitor_deadlock_getopts  
   calls proc sybsystemprocs..sp_getopts  
calls proc sybsystemprocs..sp_monitor_trace_level  
calls proc sybsystemprocs..sp_monitor_parse_archive_cmd  
calls proc sybsystemprocs..sp_monitor_deadlock_usage  
   calls proc sybsystemprocs..sp_getmessage  
calls proc sybsystemprocs..sp_monitor_check_permission  
calls proc sybsystemprocs..sp_monitor_deadlock_driver  
   calls proc sybsystemprocs..sp_monitor_deadlock_verbose  
   reads table tempdb..#mon__deadlock_info (1) 

CALLERS
called by proc sybsystemprocs..sp_monitor