| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_monitor_enable | | 31 Aug 14 | Defects Dependencies |
1 2 create procedure sp_monitor_enable(@montype varchar(30) = NULL 3 ) as 4 begin 5 6 /* Values of the configuration parameters */ 7 declare @stmt_pipe_maxmsg int 8 9 /* Names of the configuration parameters */ 10 declare @stmt_pipe_maxmsg_cfg char(100) 11 12 /* Configuration option numbers for the configuration parameters */ 13 declare @stmt_pipe_maxmsg_cfg_num int 14 15 /* Misc declarations */ 16 declare @rtnstatus int 17 declare @val int 18 , @turn_all_on tinyint 19 20 -- Generated fragments of SQL that will be executed to turn ON options. 21 declare @inlist_str varchar(400) 22 , @inlist_initial varchar(30) 23 , @sqlstmt varchar(1024) 24 , @notexists_sql varchar(128) 25 , @insert_stmt varchar(128) 26 , @rest_of_select varchar(80) 27 , @value_zero varchar(20) 28 , @updstmt varchar(400) 29 , @lcl_montype varchar(30) 30 , @found_montype tinyint -- validate monitoring type arg. 31 , @char_index int 32 , @retval int 33 , @nrowsaffected int 34 35 -- Names for individual configuration options. 36 , @enable_monitoring varchar(30) 37 , @sql_batch_capture varchar(30) 38 , @stmt_stats_active varchar(30) 39 , @stmt_pipe_active varchar(30) 40 , @perobj_stats_active varchar(30) 41 , @wait_event_timing varchar(30) 42 , @process_wait_events varchar(30) 43 , @deadlock_pipe_active varchar(30) 44 , @whoami varchar(30) 45 46 select @whoami = object_name(@@procid, db_id('sybsystemprocs')) 47 exec @rtnstatus = sp_monitor_check_permission @whoami 48 if (@rtnstatus != 0) 49 return (@rtnstatus) 50 51 exec @rtnstatus = sp_monitor_verify_setup 52 if (@rtnstatus != 0) 53 return (@rtnstatus) 54 55 -- Initialize names for individual config options that we might use. 56 select @enable_monitoring = "'enable monitoring'" 57 , @sql_batch_capture = "'SQL batch capture'" 58 , @stmt_stats_active = "'statement statistics active'" 59 , @stmt_pipe_active = "'statement pipe active'" 60 , @perobj_stats_active = "'per object statistics active'" 61 , @wait_event_timing = "'wait event timing'" 62 , @process_wait_events = "'process wait events'" 63 , @deadlock_pipe_active = "'deadlock pipe active'" 64 65 , @turn_all_on = 0 66 , @found_montype = 0 67 68 -- User might have entered: sp_monitor enable, 'procstack monitoring' 69 -- If so, strip out the term 'monitoring', and get the base monitoring type 70 -- which would be one of terms like 'all', 'connection', 'procstack' etc. 71 -- 72 if (@montype LIKE "% monitoring") 73 begin 74 -- Locate start of ' monitoring' phrase. 75 select @char_index = charindex(' ', @montype) 76 77 -- We could raise an error in the else case, but it's of no use. 78 -- If the monitoring type is wrong, you get an error later on. 79 if (@char_index > 0) 80 begin 81 select @montype = ltrim(rtrim(substring(@montype, 1, 82 (@char_index - 1)))) 83 end 84 end 85 86 -- Re-enabling 'all' means user is starting a new monitoring window. Drop and 87 -- re-create the control table, to re-capture the state of the config options. 88 -- 89 if (@montype IS NULL) or (@montype = "all") 90 begin 91 if exists (select 1 from tempdb.dbo.sysobjects 92 where name = "mon_config" and uid = 1) 93 begin 94 print "Drop and recreate control table '%1!'" 95 , "tempdb.dbo.mon_config" 96 drop table tempdb.dbo.mon_config 97 end 98 select @turn_all_on = 1 99 , @found_montype = 1 100 end 101 102 -- Create the table if it does not exist (dropped above), or if the user 103 -- is runnning 'enable' the 1st time on a monitor type (in which case the 104 -- table would not have been created earlier). 105 -- 106 if (object_id('tempdb.dbo.mon_config') IS NULL) 107 begin 108 exec @retval = sp_monitor_crt_mon_config 109 if (@retval != 0) 110 return @retval 111 end 112 113 select @insert_stmt = " INSERT INTO tempdb.dbo.mon_config" 114 + "(monitor, confignum, configval, configname, enabled)" 115 , @value_zero = " AND value = 0" 116 117 -- Insert a value of -1 for 'configval' as it will be updated later. 118 -- See below. 119 -- 120 , @rest_of_select = ", config, -1, name, getdate()" 121 + " FROM master.dbo.sysconfigures WHERE name IN " 122 123 -- NOT EXISTS clause sql substring, to which we will append later on the 124 -- IN-list that we are looking for before we insert any new rows. 125 -- 126 , @notexists_sql = "IF NOT EXISTS (SELECT 1 FROM tempdb.dbo.mon_config" 127 + " WHERE configname IN " 128 129 /* 130 ** This is an UPDATE statement to register in the control table the value 131 ** of the configuration option *BEFORE* the sp_monitor enable was ever 132 ** started. We are trying to note down what the original config setting was 133 ** prior to a config option being turned ON for some monitoring type. 134 ** 135 ** o If this is the 1st row being inserted for a configuration option, save 136 ** the current run value from syscurconfigs for this config option. 137 ** 138 ** o If this is a row being inserted for a config option, and another row 139 ** already exists, then save off the min(configval) value, which will give 140 ** us the value of the config option *before* the first row was inserted. 141 ** 142 ** o ONLY do the update if the row is (are) newly inserted; i.e. configval = 143 ** -1. This way we avoid doing needless updates of the same rows if the 144 ** 'enable' was run on the same monitoring type repeatedly. 145 ** 146 ** We do the UPDATE via execute immediate as it is the same exact SQL 147 ** fragment that we want to run for each monitoring type, *except* that the 148 ** WHERE clause will change for each monitoring type. Hence, build a common 149 ** SQL frament and then attach the WHERE clause. 150 */ 151 select @updstmt = 152 "UPDATE tempdb.dbo.mon_config " 153 + " SET configval = (SELECT ISNULL(MIN(configval)" 154 + ", (SELECT cu.value" 155 + " FROM master.dbo.syscurconfigs cu" 156 157 + " WHERE cu.config = o.confignum) )" 158 159 + " FROM tempdb.dbo.mon_config i" 160 + " WHERE i.configval != -1" 161 + " AND i.confignum = o.confignum)" 162 + " FROM tempdb.dbo.mon_config o" 163 + " WHERE o.configval = -1" 164 165 select @inlist_initial = "(" + @enable_monitoring 166 167 /* 168 ** Here we continually build an IN-list string, for various configuration 169 ** options that would be needed if a particular type of monitoring is being 170 ** enabled. In the 'all' case, we might end up generating duplicate config 171 ** options in the IN-list, but that's not a problem as the WHERE clause 172 ** will only essentially consider one instance. 173 */ 174 if (@montype = 'connection') or (@turn_all_on = 1) 175 begin 176 select @inlist_str = @inlist_initial 177 + "," + @sql_batch_capture 178 + "," + @wait_event_timing 179 + "," + @perobj_stats_active 180 + ")" 181 , @lcl_montype = "connection" 182 , @found_montype = 1 183 184 -- Do not include the @value_zero clause here as we want to INSERT 185 -- all the config options that are required for this monitoring type 186 -- into the control table. If we insert only the ones that are 187 -- currently zero, it might be that some of the config options that 188 -- are shared between different monitoring types are already ON, and 189 -- so don't get tracked under this monitoring type in the control 190 -- table. This means if this monitoring type is subsequently disabled, 191 -- we don't have all the data needed to turn OFF all the required 192 -- config options; some will be left ON (which is inconsistent with 193 -- the defined semantic of disable turning OFF all related items). 194 -- 195 select @sqlstmt = @notexists_sql 196 + @inlist_str 197 + " AND monitor = '" + @lcl_montype + "'" 198 + ")" 199 + @insert_stmt 200 + " SELECT " + "'" + @lcl_montype + "'" 201 + @rest_of_select 202 + @inlist_str 203 -- + @value_zero 204 205 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 206 if (@rtnstatus != 0) 207 return @rtnstatus 208 209 select @sqlstmt = @updstmt 210 + " AND monitor = '" + @lcl_montype + "'" 211 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 212 , @nrowsaffected output 213 if (@rtnstatus != 0) 214 return @rtnstatus 215 end 216 217 if (@montype = 'statement') or (@turn_all_on = 1) 218 begin 219 select @inlist_str = @inlist_initial 220 + "," + @sql_batch_capture 221 + "," + @stmt_stats_active 222 + "," + @perobj_stats_active 223 + "," + @wait_event_timing 224 + ")" 225 , @lcl_montype = "statement" 226 , @found_montype = 1 227 228 select @sqlstmt = @notexists_sql 229 + @inlist_str 230 + " AND monitor = '" + @lcl_montype + "'" 231 + ")" 232 + @insert_stmt 233 + " SELECT " + "'" + @lcl_montype + "'" 234 + @rest_of_select 235 + @inlist_str 236 237 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 238 if (@rtnstatus != 0) 239 return @rtnstatus 240 241 select @sqlstmt = @updstmt 242 + " AND monitor = '" + @lcl_montype + "'" 243 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 244 if (@rtnstatus != 0) 245 return @rtnstatus 246 end 247 248 if (@montype = 'event') or (@turn_all_on = 1) 249 begin 250 select @inlist_str = @inlist_initial 251 + "," + @wait_event_timing 252 + "," + @process_wait_events 253 + ")" 254 , @lcl_montype = "event" 255 , @found_montype = 1 256 257 select @sqlstmt = @notexists_sql 258 + @inlist_str 259 + " AND monitor = '" + @lcl_montype + "'" 260 + ")" 261 + @insert_stmt 262 + " SELECT " + "'" + @lcl_montype + "'" 263 + @rest_of_select 264 + @inlist_str 265 266 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 267 if (@rtnstatus != 0) 268 return @rtnstatus 269 270 select @sqlstmt = @updstmt 271 + " AND monitor = '" + @lcl_montype + "'" 272 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 273 if (@rtnstatus != 0) 274 return @rtnstatus 275 end 276 277 if (@montype = 'procedure') or (@turn_all_on = 1) 278 begin 279 select @inlist_str = @inlist_initial 280 + "," + @stmt_stats_active 281 + "," + @perobj_stats_active 282 + "," + @stmt_pipe_active 283 + ")" 284 , @lcl_montype = "procedure" 285 , @found_montype = 1 286 287 select @sqlstmt = @notexists_sql 288 + @inlist_str 289 + " AND monitor = '" + @lcl_montype + "'" 290 + ")" 291 + @insert_stmt 292 + " SELECT " + "'" + @lcl_montype + "'" 293 + @rest_of_select 294 + @inlist_str 295 296 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 297 if (@rtnstatus != 0) 298 return @rtnstatus 299 300 select @sqlstmt = @updstmt 301 + " AND monitor = '" + @lcl_montype + "'" 302 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 303 if (@rtnstatus != 0) 304 return @rtnstatus 305 end 306 307 if (@montype = 'deadlock') or (@turn_all_on = 1) 308 begin 309 select @inlist_str = @inlist_initial 310 + "," + @deadlock_pipe_active 311 + ")" 312 , @lcl_montype = "deadlock" 313 , @found_montype = 1 314 315 select @sqlstmt = @notexists_sql 316 + @inlist_str 317 + " AND monitor = '" + @lcl_montype + "'" 318 + ")" 319 + @insert_stmt 320 + " SELECT " + "'" + @lcl_montype + "'" 321 + @rest_of_select 322 + @inlist_str 323 324 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 325 if (@rtnstatus != 0) 326 return @rtnstatus 327 328 select @sqlstmt = @updstmt 329 + " AND monitor = '" + @lcl_montype + "'" 330 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 331 if (@rtnstatus != 0) 332 return @rtnstatus 333 end 334 335 if (@montype = 'procstack') or (@turn_all_on = 1) 336 begin 337 -- Procedure stack monitoring does not need any additional 338 -- config options to be turned ON. It selects from a table that is 339 -- always around. 340 341 select @inlist_str = @inlist_initial 342 + ")" 343 , @lcl_montype = "procstack" 344 , @found_montype = 1 345 346 select @sqlstmt = @notexists_sql 347 + @inlist_str 348 + " AND monitor = '" + @lcl_montype + "'" 349 + ")" 350 + @insert_stmt 351 + " SELECT " + "'" + @lcl_montype + "'" 352 + @rest_of_select 353 + @inlist_str 354 355 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 356 if (@rtnstatus != 0) 357 return @rtnstatus 358 359 select @sqlstmt = @updstmt 360 + " AND monitor = '" + @lcl_montype + "'" 361 exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype 362 if (@rtnstatus != 0) 363 return @rtnstatus 364 end 365 366 -- This means user asked to enable a monitoring type that is invalid. 367 -- Raise an error and bail. 368 if (@found_montype = 0) 369 begin 370 raiserror 19260, "sp_monitor 'help', 'enable'" 371 return 1 372 end 373 374 -- 375 -- Enable the configuration parameters that have not already been turned 376 -- ON, from the ones that are relevant to the monitoring type being enabled. 377 -- If the user is running in 'all' mode, then turn ON all the config options 378 -- that are currently 0. If user is running enable for a particular monitor 379 -- type, turn ON only those config options that are needed for that type of 380 -- monitoring. In the latter case, the CASE statement below is the one that 381 -- selects based on an IN-list only those config options that need to be turned 382 -- ON. 383 384 select @sqlstmt = "declare @val int " 385 + "SELECT @val = config_admin(23, confignum, 1, 0, NULL, NULL)" 386 + " FROM tempdb.dbo.mon_config" 387 + " WHERE 1 = 1" 388 + case @turn_all_on 389 when 1 then "" 390 else " AND configname IN " + @inlist_str 391 end 392 + " AND configval = 0" 393 + " AND confignum > 0" -- eliminate non-config item rows. 394 395 exec @rtnstatus = sp_exec_SQL @sqlstmt, "sp_monitor_enable-config_admin" 396 397 -- If the config_admin() built-in were to fail due to some memory configuration 398 -- issue, rollback the entire 'enable' command's work, by calling the disable 399 -- feature on the monitoring type being enabled. 400 -- 401 if (@rtnstatus != 0) 402 begin 403 -- Reuse variable to define name of sp_monitor sproc. 404 select @enable_monitoring = "sybsystemprocs.dbo.sp_monitor" 405 exec @enable_monitoring 'disable', @montype 406 return @rtnstatus 407 end 408 409 /* 410 ** Certain configuration parameters such as 'statement pipe max messages' 411 ** need to be tuned to some optimal values. Get the config value for 412 ** these options and tune them to some predefined values. Also insert them 413 ** into the mon_config table so that as part of sp_monitor "disable", these 414 ** values can be reset back to their original values. 415 */ 416 select @stmt_pipe_maxmsg_cfg = "statement pipe max messages" 417 418 exec sp_monitor_getcfgval @stmt_pipe_maxmsg_cfg, @stmt_pipe_maxmsg output 419 420 exec sp_monitor_getcfgnum @stmt_pipe_maxmsg_cfg, @stmt_pipe_maxmsg_cfg_num output 421 422 423 -- Tune the 'statement pipe max messages' option if it is not already on. 424 -- It's only needed for procedure monitoring, so turn it ON to some 425 -- pre-selected default value only in case we are enabling proceduring 426 -- monitoring, or 'all' monitoring. 427 -- 428 if ((@stmt_pipe_maxmsg = 0) 429 and ((@montype = 'procedure') or (@turn_all_on = 1))) 430 begin 431 select @val = config_admin(23, @stmt_pipe_maxmsg_cfg_num, 100000, 0, NULL, NULL) 432 insert into tempdb.dbo.mon_config( 433 monitor, confignum, configval, configname, enabled) 434 values ('procedure', 435 @stmt_pipe_maxmsg_cfg_num, 100000, @stmt_pipe_maxmsg_cfg, 436 getdate()) 437 end 438 439 select @val = mdaconfig('disable_lwp', 'begin') 440 441 return (0) 442 end -- } 443
exec sp_procxmode 'sp_monitor_enable', 'AnyMode' go Grant Execute on sp_monitor_enable to public go
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_exec_SQL calls proc sybsystemprocs..sp_monitor_check_permission 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 reads table master..syscurconfigs (1) reads table master..sysconfigures (1) reads table tempdb..sysobjects (1)  calls proc sybsystemprocs..sp_monitor_getcfgval calls proc sybsystemprocs..sp_monitor_getcfgnum calls proc sybsystemprocs..sp_monitor_crt_mon_config  reads table master..sysconfigures (1) reads table tempdb..sysobjects (1) |
