1
2 /* This stored procedure produces is used to invoke all the subordinate stored
3 ** procedures
4 */
5 create procedure sp_sysmon_analyze
6 @interval int,
7 @Reco char(1),
8 @section char(80),
9 @applmon char(14),
10 @instid smallint = NULL /* optional SDC instance id */
11 as
12
13 /* ----------- declare local variables ---------- */
14 declare @NumXacts int /* for per transactions calculations */
15 declare @NumElapsedMs int /* for "per Elapsed second" calculations */
16 declare @NumEngines tinyint /* number of engines online */
17 declare @tmpNumEngines tinyint /* number of engines online */
18 declare @status int /* hold sproc status codes */
19 declare @eng_count int
20 declare @tmpelapsedMs int
21 declare @GetStatTime datetime /* time monitor counters sampled */
22 declare @sec_hdr char(80) /* string to delimit sections on printout */
23 declare @blankline char(1) /* to print blank line */
24 declare @rptline char(80) /* formatted stats line for print statement */
25 declare @tmp_grp_name varchar(80)
26 declare @tmp_elapsedMs_total int
27 declare @CacheID smallint /* Cache ID to map to buffer_N group */
28 declare @CacheName varchar(255) /* Cache Name from cache id lookup */
29 declare @i int
30 declare @msg varchar(255)
31
32 select @eng_count = 0
33 select @tmp_elapsedMs_total = 0
34 select @sec_hdr = "==============================================================================="
35
36 select @tmpNumEngines = value
37 from #tempmonitors where
38 field_name = "cg_cmaxonline" and
39 group_name = "config"
40
41 select @NumEngines = 0
42
43 while @eng_count < @tmpNumEngines
44 begin
45 select @tmp_grp_name = "engine_" + convert(varchar(3), @eng_count)
46
47 if (select value
48 from #tempmonitors where field_name = "clock_ticks"
49 and group_name = @tmp_grp_name) > 0
50 begin
51 select @NumEngines = @NumEngines + 1
52 end
53
54 select @eng_count = @eng_count + 1
55 end
56
57
58 select @NumElapsedMs = @interval * 1000
59
60
61 /*
62 ** Name: Number of Transactions
63 ** Descrip: Measures the number of committed transactions, both
64 ** explicit via BEGIN TRAN and COMMIT TRAN as well as
65 ** implicit via single statement DML. Used to report
66 ** not just how much work has been done within the sample
67 ** but, more importantly, as a measure of viewing other
68 ** statistics with a common denominator (ie., I/Os per
69 ** transaction). This provides a common way of judging
70 ** the effects of making changes within a controlled test
71 ** environment.
72 **
73 ** For example, what effect does adding memory have on I/O
74 ** rates.
75 ** Calc: access.xacts
76 ** Caveats: This counter is also incremented once for each
77 ** "sub-transaction" participating in a multi-DB
78 ** transaction.
79 ** Tuning: None, informational only to provide a frame of
80 ** reference for evaluating other statistics.
81 */
82
83 select @NumXacts = value
84 from #tempmonitors
85 where group_name = "access" and
86 field_name = "xacts"
87
88 if @NumXacts = 0
89 select @NumXacts = 1 /* avoid divide by 0 for all per xact calcs */
90
91 /*
92 ** Since monitor counters are updated usually without any synchronization
93 ** these are not completely accurate in multi-engine scenarios where trace
94 ** 4050 is used or if the platform is NT where we don't have engine local
95 ** counters. Usually this is not a problem. But in cases where we deduce some
96 ** values based on the relative values of two or more counters, this can cause
97 ** sysmon to report meaningless values at times. (E.g. -ve values)
98 ** We should fixup any such counters here to avoid meaningless reporting.
99 **
100 ** 1. Cache Misses = Cache searches - Cache hits
101 ** If hits > searches set hits = searches.
102 ** Although we have cache hit counters at pool level, those are not being
103 ** used in reporting so we need not take care of them here.
104 */
105
106 update #tempmonitors
107 set value = b.value
108 from #tempmonitors a, #tempmonitors b
109 where a.group_name = b.group_name
110 and a.group_name like 'buffer_%'
111 and b.group_name like 'buffer_%'
112 and a.field_name = 'bufsearch_calls'
113 and b.field_name = 'bufsearch_finds'
114 and a.value < b.value
115
116 /*
117 ** Execute the subprocedures. If any procedure returns a status of
118 ** 1 we treat it as a fatal error and return. We don't report any
119 ** messages here. That is the job of the subordinate proc.
120 */
121
122 if (@section = "NULL")
123 begin /* { */
124
125 if (@@kernelmode = 'process')
126 begin
127 exec @status = sp_sysmon_kernel @NumEngines, @NumElapsedMs,
128 @NumXacts, @Reco, @instid
129 end
130 else
131 begin
132 exec @status = sp_sysmon_kernel_threaded
133 @NumEngines, @NumElapsedMs,
134 @NumXacts, @Reco, @instid
135 end
136 if @status = 1 return 1
137
138 exec @status = sp_sysmon_wpm @NumElapsedMs, @NumXacts, @Reco
139 if @status = 1 return 1
140
141 exec @status = sp_sysmon_parallel @NumElapsedMs, @NumXacts, @Reco
142 if @status = 1 return 1
143
144 exec @status = sp_sysmon_taskmgmt @NumEngines, @NumElapsedMs, @NumXacts,
145 @Reco
146 if @status = 1 return 1
147
148 exec @status = sp_sysmon_appmgmt @NumEngines, @NumElapsedMs, @NumXacts,
149 @applmon
150 if @status = 1 return 1
151
152 exec @status = sp_sysmon_esp @NumElapsedMs, @NumXacts
153 if @status = 1 return 1
154
155 exec @status = sp_sysmon_hk @NumElapsedMs, @NumXacts, @Reco, @instid
156 if @status = 1 return 1
157
158 exec @status = sp_sysmon_maccess @NumElapsedMs, @NumXacts, @Reco
159 if @status = 1 return 1
160
161 exec @status = sp_sysmon_xactsum @NumElapsedMs, @NumXacts
162 if @status = 1 return 1
163
164 exec @status = sp_sysmon_xactmgmt @NumElapsedMs, @NumXacts, @Reco
165 if @status = 1 return 1
166
167 exec @status = sp_sysmon_index @NumElapsedMs, @NumXacts
168 if @status = 1 return 1
169
170 exec @status = sp_sysmon_mdcache @NumElapsedMs, @NumXacts, @Reco
171 if @status = 1 return 1
172
173 exec @status = sp_sysmon_locks @NumElapsedMs, @NumXacts, @Reco,
174 @NumEngines
175 if @status = 1 return 1
176
177 exec @status = sp_sysmon_dcache @NumEngines, @NumElapsedMs, @NumXacts,
178 @Reco, @instid
179 if @status = 1 return 1
180
181 exec @status = sp_sysmon_pcache @NumElapsedMs, @NumXacts, @Reco
182 if @status = 1 return 1
183
184 exec @status = sp_sysmon_memory @NumElapsedMs, @NumXacts
185 if @status = 1 return 1
186
187 exec @status = sp_sysmon_recovery @NumElapsedMs, @NumXacts
188 if @status = 1 return 1
189
190 exec @status = sp_sysmon_diskio @NumEngines, @NumElapsedMs, @NumXacts,
191 @Reco
192 if @status = 1 return 1
193
194 exec @status = sp_sysmon_netio @NumEngines, @NumElapsedMs, @NumXacts
195 if @status = 1 return 1
196
197 exec @status = sp_sysmon_repagent
198 if @status = 1 return 1
199
200 return 0
201 end /* } */
202 else
203 if (@section = "kernel")
204 begin /* { */
205 print @sec_hdr
206 if (@@kernelmode = 'process')
207 begin
208 exec @status = sp_sysmon_kernel @NumEngines,
209 @NumElapsedMs, @NumXacts,
210 @Reco, @instid
211 end
212 else
213 begin
214 exec @status = sp_sysmon_kernel_threaded @NumEngines,
215 @NumElapsedMs, @NumXacts, @Reco, @instid
216 end
217
218 if @status = 1
219 begin /* { */
220 return 1
221 end /* } */
222 else
223 begin /* { */
224 return 0
225 end /* } */
226
227 end /* } */
228 else
229 if (@section = "wpm")
230 begin /* { */
231 print @sec_hdr
232 exec @status = sp_sysmon_wpm @NumElapsedMs, @NumXacts, @Reco
233 if @status = 1 /* fatal error - abort run */
234 begin /* { */
235 /* Subordinate stored procedures print their own
236 messages.*/
237 return 1
238 end /* } */
239 else
240 begin /* { */
241 return 0
242 end /* } */
243
244 end /* } */
245 else
246 if (@section = "parallel")
247 begin /* { */
248 print @sec_hdr
249 exec @status = sp_sysmon_parallel @NumElapsedMs, @NumXacts, @Reco
250 if @status = 1 /* fatal error - abort run */
251 begin /* { */
252 /* Subordinate stored procedures print their own
253 messages.*/
254 return 1
255 end /* } */
256 else
257 begin /* { */
258 return 0
259 end /* } */
260
261 end /* } */
262 else
263 if (@section = "taskmgmt")
264 begin /* { */
265 print @sec_hdr
266 exec @status = sp_sysmon_taskmgmt @NumEngines, @NumElapsedMs, @NumXacts, @Reco
267 if @status = 1 /* fatal error - abort run */
268 begin /* { */
269 /* Subordinate stored procedures print their own
270 messages.*/
271 return 1
272 end /* } */
273 else
274 begin /* { */
275 return 0
276 end /* } */
277
278 end /* } */
279 else
280 if (@section = "appmgmt")
281 begin /* { */
282 print @sec_hdr
283 exec @status = sp_sysmon_appmgmt @NumEngines, @NumElapsedMs, @NumXacts, @applmon
284 if @status = 1 /* fatal error - abort run */
285 begin /* { */
286 /* Subordinate stored procedures print their own messages. */
287 return 1
288 end /* } */
289 else
290 begin /* { */
291 return 0
292 end /* } */
293 end /* } */
294 else
295 if (@section = "esp")
296 begin
297 exec @status = sp_sysmon_esp @NumElapsedMs, @NumXacts
298 if @status = 1 /* fatal error - abort run */
299 begin
300 /* Subordinate stored procedures print their own messages. */
301 return 1
302 end /* } */
303 end /* } */
304 else
305 if (@section = "housekeeper")
306 begin
307 exec @status = sp_sysmon_hk @NumElapsedMs, @NumXacts, @Reco, @instid
308 if @status = 1 /* fatal error - abort run */
309 begin
310 /* Subordinate stored procedures print their own messages. */
311 return 1
312 end /* } */
313 end /* } */
314 else
315 if (@section = "monaccess")
316 begin /* { */
317 print @sec_hdr
318 exec @status = sp_sysmon_maccess @NumElapsedMs, @NumXacts, @Reco
319 if @status = 1 /* fatal error - abort run */
320 begin /* { */
321 /* Subordinate stored procedures print their own messages. */
322 return 1
323 end /* } */
324 end /* } */
325 else
326 if (@section = "xactsum")
327 begin /* { */
328 print @sec_hdr
329 exec @status = sp_sysmon_xactsum @NumElapsedMs, @NumXacts
330 if @status = 1 /* fatal error - abort run */
331 begin /* { */
332 /* Subordinate stored procedures print their own
333 messages.*/
334 return 1
335 end /* } */
336 else
337 begin /* { */
338 return 0
339 end /* } */
340
341 end /* } */
342 else
343 if (@section = "xactmgmt")
344 begin /* { */
345 print @sec_hdr
346 exec @status = sp_sysmon_xactmgmt @NumElapsedMs, @NumXacts, @Reco
347 if @status = 1 /* fatal error - abort run */
348 begin /* { */
349 /* Subordinate stored procedures print their own
350 messages.*/
351 return 1
352 end /* } */
353 else
354 begin /* { */
355 return 0
356 end /* } */
357
358 end /* } */
359 else
360 if (@section = "indexmgmt")
361 begin /* { */
362 print @sec_hdr
363 exec @status = sp_sysmon_index @NumElapsedMs, @NumXacts
364 if @status = 1 /* fatal error - abort run */
365 begin /* { */
366 /* Subordinate stored procedures print their own
367 messages.*/
368 return 1
369 end /* } */
370 else
371 begin /* { */
372 return 0
373 end /* } */
374
375 end /* } */
376 else
377 if (@section = "mdcache")
378 begin /* { */
379 print @sec_hdr
380 exec @status = sp_sysmon_mdcache @NumElapsedMs, @NumXacts, @Reco
381 if @status = 1 /* fatal error - abort run */
382 begin /* { */
383 /* Subordinate stored procedures print their own
384 messages.*/
385 return 1
386 end /* } */
387 else
388 begin /* { */
389 return 0
390 end /* } */
391
392 end /* } */
393 else
394 if (@section = "locks")
395 begin /* { */
396 print @sec_hdr
397 exec @status = sp_sysmon_locks @NumElapsedMs,
398 @NumXacts, @Reco, @NumEngines
399 if @status = 1 /* fatal error - abort run */
400 begin /* { */
401 /* Subordinate stored procedures print their own
402 messages.*/
403 return 1
404 end /* } */
405 else
406 begin /* { */
407 return 0
408 end /* } */
409
410 end /* } */
411 else
412 if (@section = "dcache")
413 begin /* { */
414 print @sec_hdr
415 exec @status = sp_sysmon_dcache @NumEngines, @NumElapsedMs, @NumXacts, @Reco, @instid
416 if @status = 1 /* fatal error - abort run */
417 begin /* { */
418 /* Subordinate stored procedures print their own
419 messages.*/
420 return 1
421 end /* } */
422 else
423 begin /* { */
424 return 0
425 end /* } */
426
427 end /* } */
428 else
429 if (@section = "pcache")
430 begin /* { */
431 print @sec_hdr
432 exec @status = sp_sysmon_pcache @NumElapsedMs, @NumXacts, @Reco
433 if @status = 1 /* fatal error - abort run */
434 begin /* { */
435 /* Subordinate stored procedures print their own
436 messages.*/
437 return 1
438 end /* } */
439 else
440 begin /* { */
441 return 0
442 end /* } */
443
444 end /* } */
445 else
446 if (@section = "memory")
447 begin /* { */
448 print @sec_hdr
449 exec @status = sp_sysmon_memory @NumElapsedMs, @NumXacts
450 if @status = 1 /* fatal error - abort run */
451 begin /* { */
452 /* Subordinate stored procedures print their own
453 messages.*/
454 return 1
455 end /* } */
456 else
457 begin /* { */
458 return 0
459 end /* } */
460
461 end /* } */
462 else
463 if (@section = "recovery")
464 begin /* { */
465 print @sec_hdr
466 exec @status = sp_sysmon_recovery @NumElapsedMs, @NumXacts
467 if @status = 1 /* fatal error - abort run */
468 begin /* { */
469 /* Subordinate stored procedures print their own
470 messages.*/
471 return 1
472 end /* } */
473 else
474 begin /* { */
475 return 0
476 end /* } */
477
478 end /* } */
479 else
480 if (@section = "diskio")
481 begin /* { */
482 print @sec_hdr
483 exec @status = sp_sysmon_diskio @NumEngines, @NumElapsedMs, @NumXacts, @Reco
484 if @status = 1 /* fatal error - abort run */
485 begin /* { */
486 /* Subordinate stored procedures print their own
487 messages.*/
488 return 1
489 end /* } */
490 else
491 begin /* { */
492 return 0
493 end /* } */
494
495 end /* } */
496 else
497 if (@section = "netio")
498 begin /* { */
499 print @sec_hdr
500 exec @status = sp_sysmon_netio @NumEngines, @NumElapsedMs, @NumXacts
501 if @status = 1 /* fatal error - abort run */
502 begin /* { */
503 /* Subordinate stored procedures print their own
504 messages.*/
505 return 1
506 end /* } */
507 else
508 begin /* { */
509 return 0
510 end /* } */
511
512 end /* } */
513
514 else
515 if (@section = "repagent")
516 begin /* { */
517 print @sec_hdr
518 exec @status = sp_sysmon_repagent
519 if @status = 1 /* fatal error - abort run */
520 begin /* { */
521 /* Subordinate stored procedures print their own
522 messages.*/
523 return 1
524 end /* } */
525 else
526 begin /* { */
527 return 0
528 end /* } */
529
530 end /* } */
531 else
532 begin
533 raiserror 18532 @msg, "sp_sysmon"
534 raiserror 18534 @msg, "sp_sysmon"
535 end
536
exec sp_procxmode 'sp_sysmon_analyze', 'AnyMode'
go
Grant Execute on sp_sysmon_analyze to public
go
