Database | Proc | Application | Created | Links |
sybsystemprocs | sp_monitor_deadlock_count_by ![]() | ![]() | 31 Aug 14 | Defects 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 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
exec sp_procxmode 'sp_monitor_deadlock_count_by', 'AnyMode' go Grant Execute on sp_monitor_deadlock_count_by to public go
DEPENDENCIES |
PROCS AND TABLES USED writes table tempdb..#freq_by_applname (1) calls proc sybsystemprocs..sp_exec_SQL ![]() calls proc sybsystemprocs..sp_getmessage ![]() reads table master..sysmessages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() writes table tempdb..#freq_by_date_name (1) calls proc sybsystemprocs..sp_autoformat ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..syscolumns (1) ![]() reads table tempdb..syscolumns (1) ![]() calls proc sybsystemprocs..sp_namecrack ![]() reads table master..systypes (1) ![]() read_writes table tempdb..#colinfo_af (1) reads table tempdb..systypes (1) ![]() writes table tempdb..#freq_by_name (1) reads table master..sysobjects (1) ![]() writes table tempdb..#freq_by_resolvedate (1) CALLERS called by proc sybsystemprocs..sp_monitor_deadlock ![]() called by proc sybsystemprocs..sp_monitor ![]() |