| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_sysmon_collect | | 31 Aug 14 | Defects Dependencies |
1 2 create procedure sp_sysmon_collect 3 @interval_or_option char(12), /* Time interval string, option 4 ** "begin_sample" or "end_sample" */ 5 @section_name char(80), /* used to decide call to mda proc */ 6 @applmon char(14), /* Application monitoring option */ 7 @noclear int = 0 /* Whether to operate in no clear mode */ 8 as 9 10 /* --------- declare local variables --------- */ 11 declare @status int /* hold sproc status codes */ 12 declare @mda_procname char(50) /* call sp_sysmon_collect_mda */ 13 declare @rtn_status int /* hold mda sproc return status codes */ 14 declare @mda_status int /* check if mda is enabled */ 15 declare @enabled_status int /* success of enabling monitoring */ 16 declare @has_mon_role int /* check for mon_role */ 17 declare @cache_wizard_section tinyint /* 1 indicates cache wizard section */ 18 declare @monitor_usage_count int /* # of processes using monitor counters */ 19 20 /* --------- Setup Environment --------- */ 21 set nocount on /* disable row counts being sent to client */ 22 23 /* 24 ** If the interval_or_option string is not an option (i.e. "begin_sample" or "end_sample") 25 ** then it must be an time interval string. Validate that. 26 */ 27 28 if (@interval_or_option != "begin_sample" and @interval_or_option != "end_sample") 29 begin 30 if (patindex("[0-9][0-9]:[0-5][0-9]:[0-5][0-9]", @interval_or_option) = 0 or 31 patindex("00:00:00", @interval_or_option) = 1) 32 begin 33 raiserror 18179, @interval_or_option 34 return 1 35 end 36 end 37 38 /* 39 ** Check if OK to Run 40 */ 41 if proc_role("sa_role") < 1 42 begin 43 /* 18127, "You must have System Administrator (SA) role to execute 44 this stored procedure."*/ 45 raiserror 18127, "sp_sysmon" 46 return 1 47 end 48 49 /* 50 ** In a transaction?, disallow since it might make recovery impossible 51 */ 52 if @@trancount > 0 53 begin 54 /* 17260, "Can't run %1! from within a transaction." */ 55 raiserror 17260, "sp_sysmon" 56 return 1 57 end 58 else 59 set chained off 60 set transaction isolation level 1 61 select @mda_procname = 'sp_sysmon_collect_mda' 62 select @has_mon_role = 1 63 /* Check if user has mon_role */ 64 if (charindex("mon_role", show_role()) = 0) 65 begin 66 select @has_mon_role = 0 67 end 68 69 /* 70 ** Retrieve the current settings for 'enable monitoring'(356) 71 ** and 'per object statistics'(387) config options. We will 72 ** have to enable these if they are not enabled and restore them 73 ** to their original state at the end of sampling. 74 ** We use @mda_status as a bit map with the first bit representing 75 ** config # 356 and the 2nd bit representing 387. 76 */ 77 select @mda_status = isnull(cur.value, 0) 78 from master.dbo.sysconfigures con, 79 master.dbo.syscurconfigs cur 80 where con.config = 356 81 and con.config *= cur.config 82 83 select @mda_status = @mda_status + 2 * isnull(cur.value, 0) 84 from master.dbo.sysconfigures con, 85 master.dbo.syscurconfigs cur 86 where con.config = 387 87 and con.config *= cur.config 88 89 if (@section_name = "cache wizard") 90 begin 91 select @cache_wizard_section = 1 92 end 93 else 94 begin 95 select @cache_wizard_section = 0 96 end 97 98 99 /* 100 ** Clear out all monitor counters 101 ** only if an interval is specified 102 ** or if the option is "begin_sample" 103 */ 104 if (@interval_or_option != "end_sample") 105 begin 106 /* 107 ** Clear and enable monitor counters if not in noclear mode. In 108 ** noclear mode this will have been done by the sp_sysmon procedure. 109 */ 110 if (@noclear = 0) 111 begin 112 /* 113 ** Clear monitor counter values to create 114 ** baseline. 115 */ 116 dbcc monitor("clear", "all", "on") 117 dbcc monitor("clear", "spinlock_s", "on") 118 if (@applmon != "no_appl" and @cache_wizard_section != 1) 119 begin 120 dbcc monitor("clear", "appl", "on") 121 end 122 end 123 124 /* snapshot the monThread statistics */ 125 if (@@kernelmode != "process" and 126 @section_name in ("NULL", "kernel")) 127 begin 128 if exists (select 1 from tempdb.dbo.sysobjects 129 where name = 'tempThreadStats' and uid = 1) 130 begin 131 drop table tempdb.dbo.tempThreadStats 132 end 133 134 select th.ThreadID, th.ThreadPoolID, 135 th.MinorFaults, th.MajorFaults, 136 th.UserTime, th.SystemTime, 137 th.VoluntaryCtxtSwitches, 138 th.NonVoluntaryCtxtSwitches, 139 tk.Name "TaskName" 140 into tempdb.dbo.tempThreadStats 141 from master.dbo.monThread th, master.dbo.monTask tk 142 where th.KTID *= tk.KTID 143 144 if exists (select 1 from tempdb.dbo.sysobjects 145 where name = 'tempIOCStats' and uid = 1) 146 begin 147 drop table tempdb.dbo.tempIOCStats 148 end 149 150 select ControllerID, BlockingPolls, NonBlockingPolls, 151 EventPolls, NonBlockingEventPolls, FullPolls, 152 Events, Type 153 into tempdb.dbo.tempIOCStats 154 from master.dbo.monIOController 155 156 if exists (select 1 from tempdb.dbo.sysobjects 157 where name = 'tempWorkQueue' and uid = 1) 158 begin 159 drop table tempdb.dbo.tempWorkQueue 160 end 161 162 select Name, TotalRequests, QueuedRequests, WaitTime 163 into tempdb.dbo.tempWorkQueue 164 from master.dbo.monWorkQueue 165 end 166 167 /* 168 ** If user has specified the interval and a section other than 169 ** cache wizard, there is no need to worry about the 170 ** sp_sysmon_collect_mda procedure 171 ** Else, invoke sp_sysmon_collect_mda if it has been installed. 172 */ 173 if (((@interval_or_option = "begin_sample") or 174 (@cache_wizard_section = 1)) 175 and exists (select 1 from sybsystemprocs.dbo.sysobjects where 176 sysstat & 7 = 4 and name like @mda_procname)) 177 begin 178 /* 179 ** Only in the case of begin_sample, we do not know 180 ** the sections the user is interested in, hence 181 ** the error message is not printed. 182 */ 183 if (@has_mon_role = 0) 184 begin 185 if (@interval_or_option = "begin_sample") 186 begin 187 return 0 188 end 189 else if (@cache_wizard_section = 1) 190 begin 191 raiserror 19121, "sp_sysmon_collect" 192 return 1 193 end 194 end 195 196 /* Enable MDA if not enabled */ 197 if (@mda_status & 1 = 0) 198 begin 199 select @enabled_status = 200 config_admin(23, 356, 1, 0, NULL, NULL) 201 end 202 203 if (@mda_status & 2 = 0) 204 begin 205 select @enabled_status = 206 config_admin(23, 387, 1, 0, NULL, NULL) 207 end 208 209 /* 210 ** We don't want the MDA collect operation to affect 211 ** monitoring. Hence disable monitoring and disable 212 ** MDA using mdaconfig. After the initial snapshot 213 ** is taken, enable monitoring and MDA. 214 */ 215 select @status = mdaconfig('freeze', 'begin') 216 dbcc monitor("sample", "all", "off") 217 dbcc monitor("sample", "spinlock_s", "off") 218 exec @rtn_status = @mda_procname @interval_or_option, 219 @mda_status 220 dbcc monitor("sample", "all", "on") 221 dbcc monitor("sample", "spinlock_s", "on") 222 select @status = mdaconfig('freeze', 'end') 223 end 224 225 /* 226 ** if the option is "begin_sample", then 227 ** the job is done, so return 228 */ 229 230 if (@interval_or_option = "begin_sample") 231 begin 232 return 0 233 end 234 235 /* 236 ** Sleep while accumulating counter statistics 237 ** for the interval time specified 238 */ 239 240 waitfor delay @interval_or_option 241 end 242 243 /* 244 ** Sample Performance Stats 245 */ 246 dbcc monitor("sample", "spinlock_s", "off") 247 if (@applmon != "no_appl") 248 begin 249 dbcc monitor("sample", "appl", "off") 250 end 251 select @monitor_usage_count = @@monitors_active 252 dbcc monitor("sample", "all", "off") 253 254 /* 255 ** Check to see whether the monitor counter usage count was decremented 256 ** when monitor counters were turned off. If it was not, this is 257 ** probably because the user executed sp_sysmon begin_sample from 258 ** another login session. Display a message warning the user that 259 ** the usage count was not decremented and suggesting that they 260 ** run dbcc monitor(decrement) to decrement it. 261 */ 262 if (@@monitors_active = @monitor_usage_count) 263 begin 264 raiserror 19374 265 end 266 267 dbcc monitor("select", "spinlock_s", "on") 268 dbcc monitor("select", "all", "on") 269 270 select @rtn_status = 0 271 272 /* snapshot the kernel statistics */ 273 if (@@kernelmode != "process" and 274 @section_name in ("NULL", "kernel")) 275 begin 276 /* diff the counters from the start of the sample */ 277 update tempdb.dbo.tempThreadStats 278 set MinorFaults = n.MinorFaults - o.MinorFaults, 279 MajorFaults = n.MajorFaults - o.MajorFaults, 280 UserTime = n.UserTime - o.UserTime, 281 SystemTime = n.SystemTime - o.SystemTime, 282 VoluntaryCtxtSwitches = n.VoluntaryCtxtSwitches - o.VoluntaryCtxtSwitches, 283 NonVoluntaryCtxtSwitches = n.NonVoluntaryCtxtSwitches - o.NonVoluntaryCtxtSwitches 284 from tempdb.dbo.tempThreadStats o, master.dbo.monThread n 285 where o.ThreadID = n.ThreadID 286 287 /* set the TaskName to reflect the Engine where appropriate */ 288 update tempdb.dbo.tempThreadStats 289 set t.TaskName = "Engine " + convert(varchar(4), e.EngineNumber) 290 from tempdb.dbo.tempThreadStats t, master.dbo.monEngine e 291 where e.ThreadID = t.ThreadID 292 293 update tempdb.dbo.tempIOCStats 294 set BlockingPolls = n.BlockingPolls - o.BlockingPolls, 295 NonBlockingPolls = n.NonBlockingPolls - o.NonBlockingPolls, 296 EventPolls = n.EventPolls - o.EventPolls, 297 NonBlockingEventPolls = n.NonBlockingEventPolls - o.NonBlockingEventPolls, 298 FullPolls = n.FullPolls - o.FullPolls, 299 Events = n.Events - o.Events 300 from tempdb.dbo.tempIOCStats o, master.dbo.monIOController n 301 where o.ControllerID = n.ControllerID 302 303 update tempdb.dbo.tempWorkQueue 304 set TotalRequests = n.TotalRequests - o.TotalRequests, 305 QueuedRequests = n.QueuedRequests - o.QueuedRequests, 306 WaitTime = n.WaitTime - o.WaitTime 307 from tempdb.dbo.tempWorkQueue o, master.dbo.monWorkQueue n 308 where o.Name = n.Name 309 end 310 311 /* 312 ** Call sp_sysmon_collect_mda to store the snapshot at 313 ** the end of time interval only if the section is a 314 ** valid MDA section. 315 */ 316 if (@cache_wizard_section = 1) 317 begin 318 if exists (select 1 from sybsystemprocs.dbo.sysobjects where 319 sysstat & 7 = 4 and name = 'sp_sysmon_collect_mda') 320 begin 321 if (@has_mon_role = 0) 322 begin 323 raiserror 19121, "sp_sysmon_collect" 324 return 1 325 end 326 327 /* 328 ** Halt the MDA counters during the time we execute 329 ** sp_sysmon_collect_mda 330 */ 331 select @status = mdaconfig('freeze', 'begin') 332 exec @rtn_status = @mda_procname "end_sample", 333 @mda_status output 334 select @status = mdaconfig('freeze', 'end') 335 336 /* Reset the config #356, 387 if it was set by us */ 337 if (@mda_status & 1 = 0) 338 begin 339 select @enabled_status = 340 config_admin(23, 356, 0, 0, NULL, NULL) 341 end 342 343 if (@mda_status & 2 = 0) 344 begin 345 select @enabled_status = 346 config_admin(23, 387, 0, 0, NULL, NULL) 347 end 348 end 349 350 else 351 begin 352 select @rtn_status = 1 353 /* Print message that MDA is not installed */ 354 raiserror 19122, "MDA", "installmontables", "(mon_role)" 355 end 356 357 if @rtn_status = 1 358 begin 359 return @rtn_status 360 end 361 end 362 /* If we turned on monitoring during begin_sample 363 ** turn it off now. Note that we wouldn't turn this on 364 ** for the interval case unless the 'cache wizard' section 365 ** was specified. 366 */ 367 else if ((@mda_status & 1 = 1) and 368 (@interval_or_option = "end_sample") 369 and exists (select 1 from sybsystemprocs.dbo.sysobjects where 370 sysstat & 7 = 4 and name like @mda_procname)) 371 begin 372 /* 99 indicates sp_sysmon_collect_mda should just return the stored 373 ** value of @mda_status and is chosen since it has the lower bits 374 ** 1,2 set. 375 */ 376 select @mda_status = 99 377 exec @rtn_status = @mda_procname "end_sample", 378 @mda_status output 379 380 /* Reset the config #356, 387 if it was set by us */ 381 if (@mda_status & 1 = 0) 382 begin 383 select @enabled_status = 384 config_admin(23, 356, 0, 0, NULL, NULL) 385 end 386 387 if (@mda_status & 2 = 0) 388 begin 389 select @enabled_status = 390 config_admin(23, 387, 0, 0, NULL, NULL) 391 end 392 end 393 394 /* 395 ** If the interval is less than a second 396 ** do not produce the report 397 */ 398 399 if (@interval_or_option = "00:00:00") 400 begin 401 raiserror 18545, "sp_sysmon" 402 return 1 403 end 404 405 return 0 406
exec sp_procxmode 'sp_sysmon_collect', 'AnyMode' go Grant Execute on sp_sysmon_collect to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..monThread (1)  reads table master..syscurconfigs (1) calls proc sybsystemprocs..sp_sysmon_collect_mda  reads table master..monCachedObject (1) reads table tempdb..sysobjects (1)  reads table master..monCachePool (1) reads table master..monDataCache (1) reads table master..monOpenObjectActivity (1) reads table master..monWorkQueue (1) reads table master..monTask (1) reads table master..sysconfigures (1) reads table master..monIOController (1) reads table sybsystemprocs..sysobjects reads table master..monEngine (1) reads table tempdb..sysobjects (1) CALLERS called by proc sybsystemprocs..sp_sysmon |
