DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_deadlock  14 déc. 14Defects Propagation 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 or replace 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   
116           -- Do routine monitoring specific validation for setup/configuration.
117           --
118           select @sqlstmt = 'deadlock'
119           exec @retval = sp_monitor_verify_setup @sqlstmt
120   
121           exec @mdacfg_retval = sp_monitor_verify_cfgval @sqlstmt
122   
123           -- Bail out if there were any errors.
124           if (@retval != 0)
125               return @retval
126           else if (@mdacfg_retval != 0)
127               return @mdacfg_retval
128   
129           set nocount on
130   
131           select @do_freeze_mda = 0
132               , @mondeadlock = 'monDeadLock'
133   
134           select @master_monDeadLock = "master.dbo." + @mondeadlock
135   
136           exec @trace = sp_monitor_trace_level
137   
138           -- ==================================================================
139           -- Process the 'archive' mode first; If table exists, insert to it,
140           -- or else, create a new archive table using SELECT-INTO.
141           --
142           if (@arg1 LIKE 'archive%')
143           begin
144               exec @retval = sp_monitor_archive 'deadlock'
145                   , @arg1
146                   , @trace
147               return @retval
148   
149           end
150   
151           -- ==================================================================
152           -- IF the user has asked to report from an archive, parse that command,
153           -- and setup out the archive table name.
154           --
155           else if (@arg1 LIKE 'report%')
156           begin
157               exec @retval = sp_monitor_parse_archive_cmd
158                   @arg1
159                   , 'report'
160                   , 'report [using prefix=<string>]'
161                   , @deadlock_tabname output
162   
163               if (@retval != 0)
164                   return @retval
165   
166               -- Create the full name of the archive table using the
167               -- extracted prefix sub-string.
168               --
169               select @deadlock_tabname = @deadlock_tabname
170                   + @mondeadlock
171   
172               -- Setup local vars for remaining args.
173               select @dlkID_or_for_Date = @arg2
174                   , @output_mode = @arg3
175           end
176           else
177           begin
178               -- Setup local vars for remaining args.
179               select @dlkID_or_for_Date = @arg1
180                   , @output_mode = @arg2
181   
182                   -- Default events table name is this one.
183                   , @deadlock_tabname = @mondeadlock
184           end
185   
186           select @dbname = db_name()
187   
188           -- ==================================================================
189           -- Validate that the requested deadlock events table name existst.
190           -- Otherwise bail. Note that by now if we are running in report mode,
191           -- @deadlock_tabname would be the name of the archived table.
192           --
193           if (object_id(@deadlock_tabname) IS NULL)
194           begin
195               select @sqlstmt = 'dbo.' + @deadlock_tabname
196               raiserror 17870, @sqlstmt, @dbname, ""
197   
198               -- Disallow certain key system-dbs for archival uses.
199               -- Guide the user if their current db is one of the restricted
200               -- ones, and they are doing a report. 
201               --
202               if ((@arg1 LIKE 'report%')
203                       and @dbname IN ('master'
204                           , 'model'
205                           , 'sybsystemprocs'
206                           , 'sybsystemdb'
207                           , 'sybsecurity'
208                       )
209                   )
210               begin
211                   print "Cannot use database '%1!' as a database to archive data."
212                       , @dbname
213               end
214   
215               return 1
216           end
217   
218           -- If no cached archive tablename was given, then process data
219           -- real-time from monDeadLock table. In this case, produce the
220           -- extra columns that would normally appear in an archived table.
221           -- If the archive name was provided (and found), simply select all
222           -- the columns from it.
223           --
224           if (@deadlock_tabname = @mondeadlock) and (db_name() = 'master')
225           begin
226               -- We have to freeze MDA tables as this branch
227               -- *does* SELECT from monDeadLock directly.
228               --
229               select @do_freeze_mda = 1
230                   , @datestamp = getdate()
231   
232               -- This SELECT should re-generate all the columns that
233               -- would normally be created in the archive from
234               -- #mon__deadlock_info
235               --
236               exec @retval = sp_mon_archive_genSQL @mondeadlock
237                   , @datestamp
238                   , @sel_stmt output
239   
240           end
241           else
242           begin
243               -- We are processing data from an archived table. This
244               -- archive was (or should have been) created by this sproc
245               -- interface. Select all the columns from that archive, so
246               -- that we have all the event-related information in one place.
247               --
248               select @sel_stmt = 'SELECT * '
249           end /* Adaptive Server has expanded all '*' elements in the following statement */
250   
251           -- ==================================================================
252           -- Cache the info from @deadlock_tabname into a #temp table in tempdb
253           -- that is created as a copy of monDeadLock so that can iterate through
254           -- it subsequently to generate a report for interesting deadlock IDs.
255           --
256           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.PartitionID, [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
257   
258               -- This piece is hand-copied from the SQL generated by:
259               -- sp_mon_archive_genSQL monDeadLock, 'May 25 2006', 'yyy', 'zzz', 1
260   
261               -- Columns generated using sp_mon_archive_genSQL
262               -- edited for readability with some white-spaces.
263               --
264               , InstanceName = convert(varchar(30) NULL,
265               instance_name(InstanceID))
266               , ServerName = convert(varchar(30) NULL, @@servername)
267               , ArchiveDate = convert(datetime NULL, 'May 25 2006 12:00AM')
268               , VersionNum = convert(int NOT NULL, 1)
269               , SPNum = convert(int NOT NULL, 0)
270               , PLNum = convert(int NOT NULL, 0)
271               , EBFNum = convert(int NOT NULL, 0)
272   
273           into #mon__deadlock_info
274           from master.dbo.monDeadLock
275           where 1 = 0
276   
277           -- The table alias 'm' is essential as one of the generated SQL
278           -- fragments uses this 'm.' notation to disambiguate
279           -- column name references.
280           --
281           select @sqlstmt = 'INSERT #mon__deadlock_info '
282               + @sel_stmt
283               + " FROM " + @dbname + '..' + @deadlock_tabname + " m"
284               + " WHERE 1=1 "
285   
286           -- ==================================================================
287           -- Filter out deadlocks occuring on requested date, if any.
288           --
289           if (@dlkID_or_for_Date IS NOT NULL)
290           begin
291               -- Check if user has passed in a deadlockID; i.e. all digits.
292               if (patindex("%[^0-9]%", @dlkID_or_for_Date) = 0)
293               begin
294                   select @sqlstmt = @sqlstmt
295                       + " AND DeadlockID="
296                       + @dlkID_or_for_Date
297   
298                       , @for_item = 'DeadlockID'
299               end
300               else
301               begin
302                   -- Assume that the user is playing fair and will
303                   -- only pass in valid dates. If not, the following
304                   -- SQL will barf any way, and they'll get an error.
305                   --
306                   select @sqlstmt = @sqlstmt
307                       + " AND convert(date, ResolveTime)='"
308                       + @dlkID_or_for_Date + "'"
309                       , @for_item = 'date'
310               end
311           end
312   
313           -- ==================================================================
314           -- This creates the cached #mon__deadlock_info table on which
315           -- the rest of the SQL code operates.
316           --
317           if (@do_freeze_mda = 1)
318               select @mdacfg_retval = mdaconfig('freeze', 'begin')
319   
320           -- No more accesses to master.dbo.monDeadLock should happen after here.
321           exec @retval = sp_exec_SQL @sqlstmt, 'sp_monitor_deadlock'
322               , NULL, @trace
323   
324           if (@do_freeze_mda = 1)
325               select @mdacfg_retval = mdaconfig('freeze', 'end')
326   
327           if (@retval != 0)
328           begin
329               return @retval
330           end
331   
332           -- ==================================================================
333           -- Process the output modes, displaying summary only if so
334           -- requested. Then bail.
335           -- NOTE: If the user has specified any filters, they are applied
336           -- first before generating the GROUP BY result. So, users can check
337           -- for frequency of deadlocks by 'count by object' for a given 
338           -- ResolveTime (date), for instance.
339           --
340           if (@output_mode LIKE "%count by%")
341           begin
342               exec @retval = sp_monitor_deadlock_count_by
343                   @deadlock_tabname
344                   , @dbname
345                   , "#mon__deadlock_info"
346                   , @output_count_by_date
347                   , @output_count_by_appln
348                   , @output_count_by_object
349                   , @output_count_by_date_object
350                   , @output_mode
351               return @retval
352           end
353   
354           -- ==================================================================
355           -- Extract other possible print/output options
356   
357           exec @retval = sp_monitor_deadlock_getopts
358               @output_mode
359   
360               , @output_verbose
361               , @output_pagediag
362               , @opt_verbose output
363               , @opt_pagediag output
364           if (@retval != 0)
365               return @retval
366   
367           -- ==================================================================
368           -- Process the data from the #mon__deadlock_info table.
369           --
370           exec sp_monitor_deadlock_driver -- @deadlock_tabname
371               @opt_verbose
372               , @opt_pagediag
373               , @num_deadlocks output
374   
375           -- Print summary statistics of run.
376           --
377           print " "
378           select @sqlstmt = @dbname + '.dbo.' + @deadlock_tabname
379           if (@dlkID_or_for_Date IS NULL)
380           begin
381               print "Total of %1! deadlocks were analyzed in this run from '%2!'."
382                   , @num_deadlocks, @sqlstmt
383           end
384           else
385           begin
386               print "Total of %1! deadlocks were analyzed in this run for %2! %3! from '%4!'."
387                   , @num_deadlocks, @for_item, @dlkID_or_for_Date
388                   , @sqlstmt
389           end
390   
391           -- Cleanup before exiting.
392           if (object_id("#mon__deadlock_info") IS NOT NULL)
393               drop table #mon__deadlock_info
394   
395       end -- }
396   

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 196
 MTYP 4 Assignment type mismatch @output_type: varchar(8) = varchar(30) 104
 MTYP 4 Assignment type mismatch @montype: varchar(30) = varchar(2100) 119
 MTYP 4 Assignment type mismatch @montype: varchar(10) = varchar(2100) 121
 MTYP 4 Assignment type mismatch @arch_prefix: varchar(8) = varchar(30) 161
 MTYP 4 Assignment type mismatch @trace: tinyint = int 322
 TNOI 4 Table with no index master..monDeadLock master..monDeadLock
 MNER 3 No Error Check should check return value of exec 103
 MNER 3 No Error Check should check return value of exec 119
 MNER 3 No Error Check should check return value of exec 136
 MNER 3 No Error Check should check return value of exec 144
 MNER 3 No Error Check should check return value of exec 236
 MNER 3 No Error Check should check @@error after select into 256
 MNER 3 No Error Check should check return value of exec 321
 MNER 3 No Error Check should check return value of exec 342
 MNER 3 No Error Check should check return value of exec 370
 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 124
 MUCO 3 Useless Code Useless Brackets 126
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 324
 MUCO 3 Useless Code Useless Brackets 327
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 379
 MUCO 3 Useless Code Useless Brackets 392
 QAPT 3 Access to Proxy Table master..monDeadLock 274
 QPNC 3 No column in condition 275
 VNRD 3 Variable is not read @master_monDeadLock 134
 VNRD 3 Variable is not read @mdacfg_retval 325
 VUNU 3 Variable is not used @num_rows 63
 VUNU 3 Variable is not used @crt_newarchive 66
 MTR1 2 Metrics: Comments Ratio Comments: 40% 52
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 21dec - 8exi + 2 52
 MTR3 2 Metrics: Query Complexity Complexity: 89 52

DATA PROPAGATION detailed
ColumnWritten To
@arg1sp_exec_SQL_rset_001.sqlbNoName57
@arg2sp_exec_SQL_rset_001.sqlbNoName57

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

CALLERS
called by proc sybsystemprocs..sp_monitor