DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_deadlock_count_by  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** sp_monitor_deadlock_count_by
4     **
5     **	Process the @output option, if supplied. We support only a few types
6     **	of output options, mainly to generate summary data. The caller tells
7     **	us what options to support. Check if @output is one of them, and
8     **	generate the required frequency data.
9     **
10    ** Parameters:
11    **	@deadlock_tabname	- Table containing deadlock event data.
12    **	@dbname			- DB where @deadlock_tabname exists.
13    **	@temp_tablename		- #temp table created in caller for processing.
14    **	@output_count_by_date
15    **	@output_count_by_appln
16    **	@output_count_by_object
17    **	@output_count_by_date_object
18    **				- String constants for various output modes.
19    **	@output			- Use supplied output mode argument.
20    **
21    ** Returns:
22    **	0	- If all went well.
23    **	!= 0	- Otherwise.
24    {
25    */
26    create or replace procedure sp_monitor_deadlock_count_by(
27        @deadlock_tabname varchar(30)
28        , @dbname varchar(30)
29        , @temp_tablename varchar(30)
30        , @output_count_by_date varchar(30)
31        , @output_count_by_appln varchar(30)
32        , @output_count_by_object varchar(30)
33        , @output_count_by_date_object varchar(30)
34        , @output varchar(30)
35    ) as
36        begin
37            declare @sqlstmt varchar(256)
38                , @mdacfg_retval int
39                , @retval int
40                , @insert varchar(6)
41                , @select varchar(6)
42    
43            -- We only proces 'count by ' output options here. Anything else
44            -- might be a valid option, but we don't check for that here.
45            -- 
46            if ((@output IS NULL) OR (@output NOT LIKE "%count by%"))
47                return 0
48    
49            -- ==================================================================
50            -- Process the output modes, displaying summary only if so
51            -- requested. Then bail.
52            -- For all output modes, cache the monitor table info into a #temp
53            -- table, doing the GROUP BY while generating the #temp table.
54            -- Then autoformat the results from that #temp for readability.
55            --
56            if (@output NOT IN (@output_count_by_date
57                        , @output_count_by_appln
58                        , @output_count_by_object
59                        , @output_count_by_date_object
60                    ))
61            begin
62                select @sqlstmt = "'" + @output_count_by_date + "'"
63                    + ", '" + @output_count_by_appln + "'"
64                    + ", '" + @output_count_by_object + "'"
65                    + ", '" + @output_count_by_date_object + "'"
66    
67                raiserror 18640, "output", @output, @sqlstmt
68                return 1
69            end
70    
71            select @insert = "INSERT"
72                , @select = "SELECT"
73    
74            if (@output = @output_count_by_date)
75            begin
76                print " "
77                exec sp_getmessage 19637, @sqlstmt output
78                print @sqlstmt, 'ResolveDate', @dbname, @deadlock_tabname
79                print " "
80    
81                -- Create an empty template table for future inserts.
82                create table #freq_by_resolvedate(
83                    ResolveDate date
84                    , Frequency int
85                )
86                select @sqlstmt = @insert
87                    + " #freq_by_resolvedate "
88                    + @select
89                    + " CONVERT(DATE, ResolveTime)"
90                    + ", COUNT(DISTINCT DeadlockID)"
91                    + " FROM " + @temp_tablename
92                    + " GROUP BY CONVERT(DATE, ResolveTime)"
93    
94                exec @retval = sp_exec_SQL @sqlstmt, @output
95    
96                if (@retval = 0)
97                    exec sp_autoformat
98                        @fulltabname = #freq_by_resolvedate
99                        , @orderbyclause = "order by ResolveDate asc"
100           end
101   
102           else if (@output = @output_count_by_appln)
103           begin
104               print " "
105               exec sp_getmessage 19637, @sqlstmt output
106               print @sqlstmt, 'HeldApplName', @dbname, @deadlock_tabname
107               print " "
108   
109               -- Create an empty template table for future inserts.
110               create table #freq_by_applname(
111                   ApplName varchar(30)
112                   , Frequency int
113               )
114   
115               select @sqlstmt = @insert + " #freq_by_applname "
116                   + @select
117                   + " HeldApplName "
118                   + ", COUNT(DISTINCT DeadlockID)"
119                   + " FROM " + @temp_tablename
120                   + " GROUP BY HeldApplName"
121   
122               exec @retval = sp_exec_SQL @sqlstmt, @output
123   
124               if (@retval = 0)
125                   exec sp_autoformat
126                       @fulltabname = #freq_by_applname
127                       , @orderbyclause = "order by HeldApplName asc"
128           end
129   
130           else if (@output = @output_count_by_object)
131           begin
132   
133               print " "
134               exec sp_getmessage 19637, @sqlstmt output
135               print @sqlstmt, 'DBName, ObjectName', @dbname, @deadlock_tabname
136               print " "
137   
138               -- Create an empty template table for future inserts.
139               select DBName = db_name()
140                   , ObjectName = o.name
141                   , Frequency = convert(int null, 0)
142               into #freq_by_name
143               from master.dbo.sysobjects o
144               where 1 = 0
145   
146               -- Only generate the columns we really need to generate the
147               -- group by counts into a #temptable.
148               --
149               select @sqlstmt = @insert + " #freq_by_name "
150                   + @select
151                   + " ObjectDBName"
152                   + ", ObjectName"
153                   + ", count(DeadlockID)"
154                   + " FROM " + @temp_tablename
155                   + " GROUP BY ObjectDBName, ObjectName"
156   
157               exec @retval = sp_exec_SQL @sqlstmt, @output
158   
159               if (@retval = 0)
160                   exec sp_autoformat
161                       @fulltabname = #freq_by_name
162                       , @orderbyclause = "order by 1, 2"
163           end
164   
165           else if (@output = @output_count_by_date_object)
166           begin
167   
168               print " "
169               exec sp_getmessage 19637, @sqlstmt output
170               print @sqlstmt, 'ResolveDate, DBName, ObjectName'
171                   , @dbname, @deadlock_tabname
172               print " "
173   
174               -- Create an empty template table for future inserts.
175               select ResolveDate = CONVERT(DATE, o.crdate)
176                   , DBName = db_name()
177                   , ObjectName = o.name
178                   , Frequency = convert(int null, 0)
179               into #freq_by_date_name
180               from master.dbo.sysobjects o
181               where 1 = 0
182   
183               -- Only generate the columns we really need to generate the
184               -- group by counts into a #temptable.
185               --
186               select @sqlstmt = @insert + " #freq_by_date_name "
187                   + @select
188                   + " CONVERT(DATE, ResolveTime)"
189                   + ", ObjectDBName"
190                   + ", ObjectName"
191                   + ", count(DeadlockID) "
192                   + " FROM " + @temp_tablename
193                   + " GROUP BY "
194                   + "CONVERT(DATE, ResolveTime), ObjectDBName, ObjectName"
195   
196               exec @retval = sp_exec_SQL @sqlstmt, @output
197   
198               if (@retval = 0)
199                   exec sp_autoformat
200                       @fulltabname = #freq_by_date_name
201                       , @orderbyclause = "order by 1, 2, 3"
202           end
203   
204           -- Single return point for all "count by" options.
205           -- Any error from sp_exec_SQL will be returned back via this stmt.
206           return @retval
207   
208       end -- }
209   

DEFECTS
 MBPA 6 Not a parameter of this proc sybsystemprocs..sp_autoformat: @orderbyclause 99
 MBPA 6 Not a parameter of this proc sybsystemprocs..sp_autoformat: @orderbyclause 127
 MBPA 6 Not a parameter of this proc sybsystemprocs..sp_autoformat: @orderbyclause 162
 MBPA 6 Not a parameter of this proc sybsystemprocs..sp_autoformat: @orderbyclause 201
 MTYP 4 Assignment type mismatch null = varchar(24) 99
 MTYP 4 Assignment type mismatch null = varchar(25) 127
 MTYP 4 Assignment type mismatch null = varchar(13) 162
 MTYP 4 Assignment type mismatch null = varchar(16) 201
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 98
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 126
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 161
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 200
 MGTP 3 Grant to public master..sysobjects  
 MNER 3 No Error Check should check return value of exec 77
 MNER 3 No Error Check should check return value of exec 97
 MNER 3 No Error Check should check return value of exec 105
 MNER 3 No Error Check should check return value of exec 125
 MNER 3 No Error Check should check return value of exec 134
 MNER 3 No Error Check should check @@error after select into 139
 MNER 3 No Error Check should check return value of exec 160
 MNER 3 No Error Check should check return value of exec 169
 MNER 3 No Error Check should check @@error after select into 175
 MNER 3 No Error Check should check return value of exec 199
 MUCO 3 Useless Code Useless Brackets in create proc 26
 MUCO 3 Useless Code Useless Begin-End Pair 36
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 198
 MUIN 3 Column created using implicit nullability 82
 MUIN 3 Column created using implicit nullability 110
 QCTC 3 Conditional Table Creation 82
 QCTC 3 Conditional Table Creation 110
 QCTC 3 Conditional Table Creation 139
 QCTC 3 Conditional Table Creation 175
 QPNC 3 No column in condition 144
 QPNC 3 No column in condition 181
 VUNU 3 Variable is not used @mdacfg_retval 38
 MTR1 2 Metrics: Comments Ratio Comments: 30% 26
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 12dec - 2exi + 2 26
 MTR3 2 Metrics: Query Complexity Complexity: 69 26

DATA PROPAGATION detailed
ColumnWritten To
@temp_tablenamesp_exec_SQL_rset_001.sqlbNoName57

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
writes table tempdb..#freq_by_date_name (1) 
writes table tempdb..#freq_by_name (1) 
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
writes table tempdb..#freq_by_resolvedate (1) 
reads table master..sysobjects (1)  
writes table tempdb..#freq_by_applname (1) 
calls proc sybsystemprocs..sp_exec_SQL  
   writes table sybsystemprocs..sp_exec_SQL_rset_001 

CALLERS
called by proc sybsystemprocs..sp_monitor_deadlock  
   called by proc sybsystemprocs..sp_monitor