1
2 create procedure sp_sysmon
3 @interval_or_option char(12) = "", /* Time interval string, option
4 ** "begin_sample" or "end_sample"
5 */
6 @section char(80) = "NULL", /* Optional section name */
7 @applmon varchar(14) = "no_appl", /* Parameter to enable/disable
8 ** application on monitoring or number of top
9 ** objects to print output in the case of
10 ** "cache wizard"
11 ** Valid values are:
12 ** "no_appl" - Application monitoring
13 ** disabled (default)
14 ** "appl_only" - Only applications monitored
15 ** "appl_and_login" - Both applications and
16 ** logins monitored
17 ** Any valid number
18 */
19 @filter varchar(255) = "NULL", /* This parameter will be used only when the
20 ** section is 'cache wizard'
21 */
22 @dumpcounters char(6) = "n",
23 @option varchar(6) = NULL /* Accepts a qualifier such as 'noclear' */
24 as
25 declare @CacheID smallint /* Cache ID to map to buffer_N group */
26 declare @CacheName varchar(255) /* Cache Name from cache id lookup */
27 declare @DiskName varchar(40) /* Disk Name */
28 declare @PhysName varchar(127) /* Physical disk Name from cache id lookup */
29 declare @grp int /* For manipulation of the disk group*/
30 declare @Seconds int /* Interval converted to seconds */
31 declare @Stat int /* status from sysconfigures */
32 declare @RunStat int /* status from sysconfigures */
33 declare @return int /* return value */
34 declare @Cache_Status char(24) /* cache status */
35 declare @Cache_Type char(24) /* cache type */
36 declare @ConfigSize char(24) /* config size */
37 declare @RunSize char(24) /* run size */
38 declare @Cfg_Cache_Repl char(24) /* config value of cache replacement policy*/
39 declare @Run_Cache_Repl char(24) /* run value of cache replacement policy */
40 declare @msg varchar(255) /* message variable */
41 declare @mda_procname char(50) /* call sp_sysmon_analyze_mda */
42 declare @rtn_status int /* hold mda sproc return status codes */
43 declare @ClrStatTime datetime /* time monitor counters cleared */
44 declare @GetStatTime datetime /* time monitor counters sampled */
45 declare @blankline char(1) /* to print blank line */
46 declare @rptline char(80) /* formatted stats line for print statement */
47 declare @sample_time datetime /* Time when sample was performed */
48 declare @clockticks int
49 declare @sample_period varchar(20) /* length of time sample lasted */
50 declare @section_line char(80)
51 declare @noclear int /* whether to operate in noclear mode*/
52 declare @clear int /* whether to operate in clear mode*/
53 declare @counters_cleared datetime /* time counters were cleared */
54 declare @start_table datetime /* time baseline table creation started */
55 declare @end_table datetime /* time baseline index creation ended */
56 declare @table_time int /* time spent creating index */
57 declare @difference_time int /* diff betwen interval and table creation */
58 declare @collect_interval char(12) /* interval parameter passed to collect */
59 declare @tempdbname varchar(30) /* assigned temporary database */
60 declare @system_view char(16), /* SDC system view */
61 @instid smallint, /* SDC instance ID */
62 @outstr varchar(255), /* for SDC dbcc set_scope */
63 @scope varchar(16) /* for SDC dbcc set_scope */
64
65 declare @header_printed int /* report header printed indicator */
66 declare @i int /* loop counter */
67 declare @active_instances int /* SDC active instance # */
68 declare @active_instance_num int /* counter for SDC active instance */
69 declare @instancename varchar(30) /* instance name */
70 declare @curpos int /* Current position while printing the version string */
71
72 select @section_line = "==============================================================================="
73 select @blankline = " "
74
75 /* If no interval or option was passed, display usage */
76 if (@interval_or_option = "")
77 begin
78 raiserror 18179, @interval_or_option
79 return 1
80 end
81
82 /*
83 ** Determine whether to operate in clear or noclear mode.
84 ** Any parameter except for @interval_or_option can specify the
85 ** these options, so each parameter needs to be checked.
86 ** If the clear or noclear option was specified, reset the
87 ** parameter to its default value if appropriate.
88 */
89 select @noclear = 0
90 select @clear = 0
91
92 if (@section = 'noclear')
93 begin
94 select @noclear = 1
95 select @section = "NULL"
96 end
97 else if (@applmon = 'noclear')
98 begin
99 select @noclear = 1
100 select @applmon = 'no_appl'
101 end
102 else if (@filter = 'noclear')
103 begin
104 select @noclear = 1
105 select @filter = "NULL"
106 end
107 else if (@option = 'noclear')
108 begin
109 select @noclear = 1
110 end
111 else if (@dumpcounters = 'noclear')
112 begin
113 select @noclear = 1
114 end
115
116 if (@section = 'clear')
117 begin
118 select @clear = 1
119 select @section = "NULL"
120 end
121 else if (@applmon = 'clear')
122 begin
123 select @clear = 1
124 select @applmon = 'no_appl'
125 end
126 else if (@filter = 'clear')
127 begin
128 select @clear = 1
129 select @filter = "NULL"
130 end
131 else if (@option = 'clear')
132 begin
133 select @clear = 1
134 end
135 else if (@dumpcounters = 'clear')
136 begin
137 select @clear = 1
138 end
139
140 select @header_printed = 0
141
142 /*
143 ** Verify that at most one of clear and noclear options has
144 ** been specified.
145 */
146 if (@noclear = 1 and @clear = 1)
147 begin
148 /*
149 ** Invalid parameters. Both noclear and clear cannot be specified
150 ** at the same time.
151 */
152 raiserror 19449
153 return 1
154 end
155
156 if (@interval_or_option = 'begin_sample'
157 or @interval_or_option = 'end_sample')
158 begin
159 /*
160 ** If begin_sample or end_sample are specified then we
161 ** must clear the counters. Make sure that the noclear
162 ** option was not specified and set the mode to clear if
163 ** it was not.
164 */
165 if (@noclear = 1)
166 begin
167 /*
168 ** sp_sysmon cannot be executed with the no clear option unless
169 ** a sample interval is specified.
170 */
171 raiserror 19361
172 return 1
173 end
174
175 select @clear = 1
176 end
177
178 /*
179 ** If we have gotten this far and neither clear nor noclear were
180 ** specified, set the mode to noclear by default
181 */
182 if (@noclear = 0 and @clear = 0)
183 begin
184 select @noclear = 1
185 end
186
187 /*
188 ** Validate the value of section
189 */
190 if (@interval_or_option = "begin_sample" and @section != "NULL")
191 begin
192 raiserror 18546 @msg, "sp_sysmon", @section
193 end
194
195 if (@section not in ("kernel", "wpm", "parallel", "taskmgmt", "appmgmt", "esp", "housekeeper", "monaccess", "xactsum", "xactmgmt", "indexmgmt", "mdcache", "locks", "dcache", "pcache", "memory", "recovery", "diskio", "netio", "repagent", "cache wizard", "NULL"))
196 begin
197 raiserror 18532 @msg, "sp_sysmon", @section
198 raiserror 18534 @msg, "sp_sysmon"
199 return 1
200 end
201
202 /* determine command scope: instance or clusterwide */
203
204 select @system_view = NULL
205 select @instid = NULL
206 select @scope = NULL
207 select @active_instances = 1
208 select @instancename = @@servername
209
210
211
212
213 /*
214 ** Operating in noclear mode. Enable monitor counters and create
215 ** baseline table to save monitor counter values from start of session.
216 ** When not in noclear mode the counters are cleared and enabled in
217 ** sp_sysmon_collect.
218 */
219 create table #temp_sysmon_baseline(
220 group_name char(25),
221 field_id smallint,
222 value bigint
223
224 )
225
226
227 if (@noclear = 1)
228 begin
229 dbcc monitor("sample", "all", "on")
230 dbcc monitor("sample", "spinlock_s", "on")
231 if (@applmon != "no_appl")
232 begin
233 dbcc monitor("sample", "appl", "on")
234 end
235
236 dbcc monitor("select", "all", "on")
237 dbcc monitor("select", "spinlock_s", "on")
238 if (@applmon != "no_appl")
239 begin
240 dbcc monitor("select", "appl", "on")
241 end
242
243 dump tran tempdb with truncate_only
244
245 /*
246 ** If assigned temporary database is different than system
247 ** tempdb, then dump that one as well.
248 */
249 select @tempdbname = db_name(@@tempdbid)
250 if (@tempdbname != "tempdb")
251 begin
252 dump tran @tempdbname with truncate_only
253 end
254
255 /*
256 ** Measure table creation time and subtract
257 ** this from the time interval specified by the user.
258 ** If processing takes longer than the specified sample
259 ** interval then raise an error and terminate.
260 */
261 select @start_table = getdate()
262
263 /*
264 ** Copy counter values at beginning of sample
265 ** into the sysmon_monitors table
266 */
267
268 insert into #temp_sysmon_baseline
269 select group_name, field_id, value
270 from master.dbo.sysmonitors holdlock
271
272
273 select @end_table = getdate()
274 select @table_time = datediff(second, @start_table, @end_table)
275
276 /*
277 ** Adjust collection interval to account for table and
278 ** index creation time. Reverse the sign on the @table_time
279 ** value to get the dateadd function to perform subtraction.
280 ** This will subtract the value of @table_time from the value
281 ** of @interval_or_option specified by the user. This will
282 ** adjust the period of time that sp_sysmon waits by subtracting
283 ** time already spent building the baseline table from the
284 ** sample interval specified by the user.
285 */
286 select @collect_interval = convert(char(12),
287 dateadd(second,
288 - (@table_time),
289 convert(datetime,
290 @interval_or_option)),
291 108)
292
293 select @difference_time = datediff(second, @collect_interval,
294 @interval_or_option)
295
296 if (@difference_time < 0)
297 begin
298 /*
299 ** The specified interval is less than the time
300 ** required to generate the baseline (%1! seconds).
301 ** Please specify a longer sample interval.
302 */
303 raiserror 19363, @table_time
304 /* restore dbcc execution scope */
305 if (@scope = "instance")
306 begin
307 dbcc set_scope_in_cluster('instance')
308 end
309 else if (@scope = "cluster")
310 begin
311 dbcc set_scope_in_cluster('cluster')
312 end
313
314 return 1
315 end
316 end
317 else
318 begin
319 select @collect_interval = @interval_or_option
320 end
321
322 /*
323 ** Execute sp_sysmon_collect
324 */
325
326 exec @return = sp_sysmon_collect @collect_interval,
327 @section,
328 @applmon,
329 @noclear
330
331 /* restore dbcc execution scope */
332 if (@scope = "instance")
333 begin
334 dbcc set_scope_in_cluster('instance')
335 end
336 else if (@scope = "cluster")
337 begin
338 dbcc set_scope_in_cluster('cluster')
339 end
340
341
342 /*
343 ** If there was an error or if the option was "begin_sample", return
344 */
345
346 if @return = 1 or @interval_or_option = "begin_sample"
347 begin
348 return @return
349 end
350
351 select @GetStatTime = getdate()
352
353 /*
354 ** Place monitors information into a temp table.
355 **
356 ** WARNING: If you modify this to make changes in table schema,
357 ** please look at 'crttemptables' file where the #tempmonitors
358 ** is created for other sub-sysmon stored procedures.
359 */
360
361 /*
362 ** Create #tempmonitors table
363 **
364 ** A create table command is used here rather than cloning the sysmonitors
365 ** table using a select-into command in order to be compatible with
366 ** the application monitor behavior. The application monitors are deleted
367 ** whenever a select is performed on the sysmonitors table. As a result
368 ** a select-into cannot be used to create the #tempmonitors table because
369 ** doing so will also delete the application monitors from the sysmonitors
370 ** table.
371 **
372 ** WARNING: The schema of the #tempmonitors table must exactly match the
373 ** schema of the sysmonitors table as defined in syscoldata.c. If the schema of
374 ** the sysmonitors table is changed the create table command below must be
375 ** updated.
376 */
377 select @i = 0
378 select @active_instance_num = 0
379
380 while @active_instance_num < @active_instances /* for each instance */
381 /* { */
382 begin
383
384
385
386 if ((select object_id("#tempmonitors")) is not NULL)
387 begin
388 drop table #tempmonitors
389 end
390
391 create table #tempmonitors(
392 field_name char(79),
393 group_name char(25),
394 field_id smallint,
395 value int,
396 description varchar(255) null,
397
398 nodeid tinyint null)
399
400
401 if (@noclear = 0)
402 begin
403 /*
404 ** OPERATING IN CLEAR THE MONITOR COUNTERS MODE
405 ** Copy sysmonitors to #tempmonitors if not operating in
406 ** no clear mode
407 */
408 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #tempmonitors
409 select master.dbo.sysmonitors.field_name, master.dbo.sysmonitors.group_name, master.dbo.sysmonitors.field_id, master.dbo.sysmonitors.value, master.dbo.sysmonitors.description, master.dbo.sysmonitors.nodeid
410 from master.dbo.sysmonitors holdlock
411
412 end
413 else
414 begin
415 /*
416 ** Create clustered index on baseline table. This is done
417 ** at this point so that this work is performed outside of the
418 ** sample interval. Work performed during index creation will
419 ** not be reflected in the sysmon reports and time required to
420 ** create the baseline table during the sample is reduced.
421 */
422 create clustered index tsbi
423 on #temp_sysmon_baseline
424
425 (group_name, field_id)
426
427
428 /*
429 ** OPERATING IN NOCLEAR MODE - Only used when a time interval
430 ** is given. Generate delta values for all monitor counters
431 ** except those for the configuration values, control
432 ** and high water marks and insert into #tempmonitors.
433 ** The case statement will handle cases of counter wrap and
434 ** situations in which counter delta's are greater than the
435 ** capacity of the value column.
436 */
437 insert into #tempmonitors
438 select s.field_name,
439 s.group_name,
440 s.field_id,
441 case
442 when (s.value >= b.value)
443 then
444 case
445 when ((convert(numeric, s.value) - convert(numeric, b.value))
446 >= convert(numeric, 2147483648))
447 /*
448 ** The end counter value is greater than the start
449 ** value, but the difference is greater than the
450 ** maximum signed integer value. In this case, set
451 ** the value of the delta to -1.
452 */
453 then (- 1)
454 else
455 /*
456 ** The common case: end counter value is greater
457 ** then start counter value and the delta is less
458 ** than or equal to the maximum signed integer value.
459 */
460 (s.value - b.value)
461 end
462 else
463 case
464 when ((convert(numeric, b.value) - convert(numeric, s.value))
465 < convert(numeric, 2147483648))
466 /*
467 ** The counter wrapped (its value exceeded the maximum
468 ** signed integer value) during the sample period and
469 ** the total change in value exceeds the maximum
470 ** signed integer value. In this case, set the
471 ** delta value to -1.
472 */
473 then (- 1)
474 else
475 /*
476 ** The counter wrapped during the sample period
477 ** and the delta is less than or equal to the
478 ** maximum signed integer value. In this case
479 ** calculate the delta by subtracting the current
480 ** value of the counter from its value at the
481 ** beginning of the sample period and then subtract
482 ** that value from the total range of the signed
483 ** integer. This gives the actual change in
484 ** value during the sample period.
485 */
486 (convert(int, (4294967295
487 - (convert(numeric, b.value)
488 - convert(numeric, s.value)))))
489 end
490 end as value,
491 s.description,
492
493 s.nodeid
494
495 from master.dbo.sysmonitors s holdlock,
496 #temp_sysmon_baseline b
497 where b.group_name = s.group_name
498 and b.field_id = s.field_id
499 and b.group_name not in ('config', 'resource_stats', 'control')
500 and field_name not like "max%"
501 and field_name not like "%hwm"
502
503
504 /*
505 ** Copy the static values into the
506 ** #tempmonitors table without performing
507 ** deltas
508 */
509 /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #tempmonitors
510 select master.dbo.sysmonitors.field_name, master.dbo.sysmonitors.group_name, master.dbo.sysmonitors.field_id, master.dbo.sysmonitors.value, master.dbo.sysmonitors.description, master.dbo.sysmonitors.nodeid
511 from master.dbo.sysmonitors
512 where (group_name in ('config', 'resource_stats', 'control')
513 or field_name like "max%"
514 or field_name like "%hwm")
515
516
517
518 /*
519 ** Check to see whether the monitor counters have been cleared
520 ** since the beginning of this session. This is not
521 ** guaranteed to detect every time that the counters have been
522 ** cleared. But if engine_0 clock_ticks is lower now than
523 ** when the sample was begun, then we know that the counters
524 ** have been cleared.
525 */
526 select @clockticks = value from
527 #tempmonitors
528 where group_name = "engine_0"
529 and field_name = "clock_ticks"
530
531 if (@clockticks <= 0)
532 begin
533 /*
534 ** It appears that the monitor counters have been cleared
535 ** since the beginning of the sample period. The
536 ** sp_sysmon report cannot be run successfully.
537 ** Please re-run the sp_sysmon report ensuring
538 ** that the monitor counters are not cleared during
539 ** the sample period.
540 */
541 raiserror 19362
542 print " "
543 return 1
544 end
545 end
546
547 /*
548 ** Create index on tempmonitors columns used in queries
549 ** during report generation
550 */
551
552 begin
553 create clustered index tmci
554 on #tempmonitors
555 (group_name, field_id)
556 end
557
558 /*
559 ** Print report header
560 */
561 if (@header_printed = 0)
562 begin
563 print @section_line
564 print " Sybase Adaptive Server Enterprise System Performance Report"
565 print @section_line
566 print @blankline
567
568 select @rptline = "Server Version:" + space(8) + substring(@@version, 1, 56)
569 print @rptline
570
571 /*
572 ** The maximum length of sysmon output is limited in 80 characters. Except
573 ** for the 23 leading characters which includes the string "Server Version"
574 ** and the 8 space characters, only 56 characters can be displayed for version
575 ** string. If version string length is longer than 56, above statement will
576 ** only print the first 56 characters and other characters will be truncated.
577 ** This would cause incomplete version string. To fix this issue, the version
578 ** string will be broken to multiple lines to display to make sure each line
579 ** would display maximum 56 characters.
580 */
581 select @curpos = 57
582 while (@curpos < len(@@version))
583 begin
584 select @rptline = space(23) + substring(@@version, @curpos, 56)
585 print @rptline
586 select @curpos = @curpos + 56
587 end
588
589 /*
590 ** Calculate length of the sample period based on engine 0 clock ticks
591 ** at start of sample period. Subtract this from the end time to
592 ** obtain the start of the sample period.
593 */
594
595 begin
596 select @Seconds = convert(int, value * (1.0 * @@timeticks / 1000000))
597 from #tempmonitors
598 where field_name = 'clock_ticks'
599 and group_name = 'engine_0'
600 end
601
602
603 select @ClrStatTime = dateadd(second, - (@Seconds), @GetStatTime)
604
605 select @sample_period = convert(char(12),
606 dateadd(second, @Seconds, "00:00:00"),
607 108)
608
609 select @rptline = "Run Date:" + space(14) +
610 convert(char(12), @GetStatTime, 107)
611 print @rptline
612
613 if (@interval_or_option != "end_sample")
614 begin
615 select @rptline = "Sampling Started at: " +
616 space(1) + convert(char(12), @ClrStatTime, 107) +
617 space(1) + convert(char(8), @ClrStatTime, 108)
618 end
619 else
620 begin
621 select @rptline = "Statistics Cleared at:" +
622 space(1) + convert(char(12), @ClrStatTime, 107) +
623 space(1) + convert(char(8), @ClrStatTime, 108)
624 end
625 print @rptline
626
627 if (@interval_or_option != "end_sample")
628 begin
629 select @rptline = "Sampling Ended at: " +
630 space(1) + convert(char(12), @GetStatTime, 107) +
631 space(1) + convert(char(8), @GetStatTime, 108)
632 end
633 else
634 begin
635 select @rptline = "Statistics Sampled at:" +
636 space(1) + convert(char(12), @GetStatTime, 107) +
637 space(1) + convert(char(8), @GetStatTime, 108)
638 end
639 print @rptline
640
641 select @rptline = "Sample Interval: " + space(1) + @sample_period
642 print @rptline
643
644 if (@noclear = 1)
645 begin
646 select @rptline = "Sample Mode: " + space(1) + "No Clear"
647 end
648 else
649 begin
650 select @rptline = "Sample Mode: " + space(1) + "Reset Counters"
651 end
652 print @rptline
653
654 if (@noclear = 1)
655 begin
656 if (@system_view is NULL or @system_view != "cluster")
657 begin
658 select @counters_cleared = dateadd(ss,
659 - (value / (1000000 / @@timeticks)),
660 getdate())
661 from master.dbo.sysmonitors
662 where group_name = 'engine_0'
663 and field_name = 'clock_ticks'
664 end
665
666
667 select @rptline = "Counters Last Cleared:" +
668 space(1) + convert(char(12), @counters_cleared, 107) +
669 space(1) + convert(char(8), @counters_cleared, 108)
670 print @rptline
671 end
672
673 select @header_printed = 1
674 end
675
676
677
678 if @instancename is not null
679 begin
680 select @rptline = "Server Name:" + space(11) + @instancename
681 end
682 else
683 begin
684 select @rptline = "Server Name:" + space(11) + "Server is Unnamed"
685 end
686 print @rptline
687
688
689 begin
690 select @Seconds = convert(int, value * (1.0 * @@timeticks / 1000000))
691 from #tempmonitors
692 where field_name = 'clock_ticks'
693 and group_name = 'engine_0'
694 end
695
696 /*
697 ** End printing report header
698 */
699 /*
700 ** Create a temp table with all muxthreads information
701 */
702 if ((select object_id("#muxthreadsinfo")) is not NULL)
703 begin
704 drop table #muxthreadsinfo
705 end
706
707 create table #muxthreadsinfo(
708 enginename varchar(15),
709 engineid int,
710 threadid int,
711 tpname varchar(80))
712
713 insert into #muxthreadsinfo
714 select "engine_" + convert(varchar(4), me.EngineNumber),
715 me.EngineNumber, mt.ThreadID, mt.ThreadPoolName
716 from master.dbo.monEngine me, master.dbo.monThread mt
717 where me.ThreadID = mt.ThreadID
718
719 /*
720 ** create a temp table for cache information
721 */
722 if ((select object_id("#cachemap")) is not NULL)
723 begin
724 drop table #cachemap
725 end
726
727 create table #cachemap(
728 cid smallint,
729 name varchar(255),
730 group_name varchar(255),
731 cache_status char(24) null,
732 cache_type char(24) null,
733 cache_config_size char(24) null,
734 cache_run_size char(24) null,
735 config_replacement char(24) null,
736 run_replacement char(24) null)
737
738 /*
739 ** fetch all cache related information
740 */
741 if (@system_view is NULL or @system_view != "cluster")
742 begin
743 declare cache_info cursor for
744 select convert(varchar(30), co.name), convert(char(24), co.value),
745 co.status, cu.status,
746 convert(char(24), cu.value)
747 from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu
748 where parent = 19
749 and co.config = 19
750 and co.config = cu.config
751 and co.name = cu.comment
752 order by co.name
753 for read only
754 end
755
756
757 open cache_info
758 fetch cache_info into @CacheName, @ConfigSize, @Stat, @RunStat, @RunSize
759 while (@@sqlstatus = 0)
760 begin
761 select @CacheID = config_admin(15, 0, 0, 0, NULL, @CacheName)
762
763 if (@Stat & 2 = 2)
764 begin
765 select @Cache_Type = "Mixed"
766 end
767
768 if (@Stat & 4 = 4)
769 begin
770 select @Cache_Type = "Log Only"
771 end
772
773 if (@Stat & 1 = 1)
774 begin
775 select @Cache_Type = "Default"
776 end
777
778 if (@Stat & 32 = 32)
779 begin
780 select @Cache_Status = "Active"
781 end
782
783 if (@Stat & 64 = 64)
784 begin
785 select @Cache_Status = "Pend/Act"
786 end
787
788 if (@Stat & 128 = 128)
789 begin
790 select @Cache_Status = "Act/Del"
791 end
792
793
794 /*
795 ** Determine configured cache replacement policy
796 */
797 if (@Stat & 256 = 256)
798 begin
799 select @Cfg_Cache_Repl = "relaxed LRU"
800 end
801 else
802 begin
803 select @Cfg_Cache_Repl = "strict LRU"
804 end
805
806 /*
807 ** Determine running cache replacement policy
808 */
809 if (@RunStat & 256 = 256)
810 begin
811 select @Run_Cache_Repl = "relaxed LRU"
812 end
813 else
814 begin
815 select @Run_Cache_Repl = "strict LRU"
816 end
817
818
819 insert into #cachemap values (@CacheID, @CacheName, "buffer_" +
820 convert(varchar(3), @CacheID),
821 @Cache_Status,
822 @Cache_Type,
823 @ConfigSize,
824 @RunSize,
825 @Cfg_Cache_Repl,
826 @Run_Cache_Repl)
827 fetch cache_info into @CacheName, @ConfigSize, @Stat, @RunStat, @RunSize
828 end
829 close cache_info
830 deallocate cursor cache_info
831
832
833
834 /*
835 ** create a temp table for device information
836 **
837 ** WARNING: If you modify this to make changes in table schema,
838 ** please look at 'crttemptables' file where the #devicemap
839 ** is created for other sub-sysmon stored procedures.
840 */
841 if ((select object_id("#devicemap")) is not NULL)
842 begin
843 drop table #devicemap
844 end
845
846 create table #devicemap(name char(265),
847 phyname char(127),
848 group_name char(255))
849
850 /*
851 ** fetch all device related information
852 */
853
854 if (@system_view is NULL or @system_view != "cluster")
855 begin
856 declare disk_info cursor for
857 select name, phyname, vdevno
858 from master.dbo.sysdevices
859 where (status & 16) != 16
860 for read only
861 end
862
863
864 open disk_info
865 fetch disk_info into @DiskName, @PhysName, @grp
866 while (@@sqlstatus = 0)
867 begin
868 insert into #devicemap values (@DiskName, @PhysName, "disk_" + convert(char(10), @grp))
869 fetch disk_info into @DiskName, @PhysName, @grp
870 end
871 close disk_info
872 deallocate cursor disk_info
873
874 /*
875 ** Place config information into a temp table.
876 **
877 ** WARNING: If you modify this to make changes in table schema,
878 ** please look at 'crttemptables' file where the #tempconfigures
879 ** is created for other sub-sysmon stored procedures.
880 */
881 if ((select object_id("#tempconfigures")) is not NULL)
882 begin
883 drop table #tempconfigures
884 end
885
886 select c.name as name, convert(varchar(255), r.value) as value into #tempconfigures
887 from master..sysconfigures c, master..syscurconfigs r
888 where r.config = c.config and c.parent != 19
889 and not (c.parent in (1, 10, 14, 0) and r.value = 0)
890
891
892
893
894 /*
895 ** place pool information into a temp table
896 */
897 if ((select object_id("#pool_detail_per_cache")) is not NULL)
898 begin
899 drop table #pool_detail_per_cache
900 end
901 select name, convert(varchar(8), substring(co.comment, 1,
902 charindex("K", co.comment) - 1)) as io_size
903 into #pool_detail_per_cache
904 from master.dbo.sysconfigures co
905 where co.parent = 19
906 and co.comment like "%Buffer Pool%"
907
908
909 /*
910 ** Handle the new sections
911 */
912 if @section = "cache wizard"
913 begin
914 /*
915 ** If the user specified the new sections, and did not specify
916 ** the parameters, set them to default.
917 */
918 if @applmon = "no_appl"
919 begin
920 select @applmon = "10"
921 end
922
923 select @mda_procname = 'sp_sysmon_analyze_mda'
924
925 if exists (select 1 from sybsystemprocs.dbo.sysobjects where
926 sysstat & 7 = 4 and name = @mda_procname)
927 begin
928 exec @rtn_status = @mda_procname @Seconds, 'Y', @section,
929 @applmon,
930 @filter
931 return @rtn_status
932 end
933 end
934 else
935 begin
936
937 /*
938 ** Execute sp_sysmon_analyze
939 */
940 exec sybsystemprocs..sp_sysmon_analyze @Seconds, 'Y', @section,
941 @applmon, @instid
942 end
943
944
945
946
947 if ((select object_id("#tempmonitors.tmci")) is not NULL)
948 begin
949 drop index #tempmonitors.tmci
950 end
951 if ((select object_id("#temp_sysmon_baseline.tsbi")) is not NULL)
952 begin
953 drop index #temp_sysmon_baseline.tsbi
954 end
955
956 select @active_instance_num = @active_instance_num + 1
957 select @i = @i + 1
958 end
959 /* } */
960
961 if (@section = "NULL")
962 begin
963 print "=============================== End of Report ================================="
964 end
965
966 if (@dumpcounters = "y")
967 begin
968
969 print ""
970 print "==================== Start of Raw Monitor Counter Output ======================"
971 print ""
972 select field_name, group_name, field_id, value from #tempmonitors
973 where value != 0
974
975 end
976
977 return 0
978
exec sp_procxmode 'sp_sysmon', 'AnyMode'
go
Grant Execute on sp_sysmon to public
go
| DEFECTS |
 QJWI 5 Join or Sarg Without Index |
717 |
 MEST 4 Empty String will be replaced by Single Space |
3 |
| MEST 4 Empty String will be replaced by Single Space |
76 |
| MEST 4 Empty String will be replaced by Single Space |
969 |
| MEST 4 Empty String will be replaced by Single Space |
971 |
| MINU 4 Unique Index with nullable columns master..sysconfigures |
master..sysconfigures |
| MTYP 4 Assignment type mismatch value: bigint = int |
269 |
| MULT 4 Using literal database 'tempdb' |
243 |
| QPR1 4 Potential Recompilation: index creation after access to table |
553 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent} |
888 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {parent} |
905 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs bigint |
442 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
748 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
749 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
888 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
905 |
| TNOI 4 Table with no index master..monEngine |
master..monEngine |
| TNOI 4 Table with no index master..monThread |
master..monThread |
| TNOI 4 Table with no index master..syscurconfigs |
master..syscurconfigs |
| TNOI 4 Table with no index master..sysmonitors |
master..sysmonitors |
| VRUN 4 Variable is read and not initialized @msg |
192 |
 MGTP 3 Grant to public master..monEngine |
|
| MGTP 3 Grant to public master..monThread |
|
| MGTP 3 Grant to public master..sysconfigures |
|
| MGTP 3 Grant to public master..syscurconfigs |
|
| MGTP 3 Grant to public master..sysdevices |
|
| MGTP 3 Grant to public master..sysmonitors |
|
| MGTP 3 Grant to public sybsystemprocs..sp_sysmon |
|
| MGTP 3 Grant to public sybsystemprocs..sysobjects |
|
| MLCH 3 Char type with length>30 char(80) |
6 |
| MLCH 3 Char type with length>30 char(50) |
41 |
| MLCH 3 Char type with length>30 char(80) |
46 |
| MLCH 3 Char type with length>30 char(80) |
50 |
| MLCH 3 Char type with length>30 char(79) |
392 |
| MLCH 3 Char type with length>30 char(265) |
846 |
| MLCH 3 Char type with length>30 char(127) |
847 |
| MLCH 3 Char type with length>30 char(255) |
848 |
| MNER 3 No Error Check should check @@error after insert |
268 |
| MNER 3 No Error Check should check return value of exec |
326 |
| MNER 3 No Error Check should check @@error after insert |
408 |
| MNER 3 No Error Check should check @@error after insert |
437 |
| MNER 3 No Error Check should check @@error after insert |
509 |
| MNER 3 No Error Check should check @@error after insert |
713 |
| MNER 3 No Error Check should check @@error after insert |
819 |
| MNER 3 No Error Check should check @@error after insert |
868 |
| MNER 3 No Error Check should check @@error after select into |
886 |
| MNER 3 No Error Check should check @@error after select into |
901 |
| MNER 3 No Error Check should check return value of exec |
940 |
| MUCO 3 Useless Code Useless Brackets |
76 |
| MUCO 3 Useless Code Useless Brackets |
92 |
| MUCO 3 Useless Code Useless Brackets |
97 |
| MUCO 3 Useless Code Useless Brackets |
102 |
| MUCO 3 Useless Code Useless Brackets |
107 |
| MUCO 3 Useless Code Useless Brackets |
111 |
| MUCO 3 Useless Code Useless Brackets |
116 |
| MUCO 3 Useless Code Useless Brackets |
121 |
| MUCO 3 Useless Code Useless Brackets |
126 |
| MUCO 3 Useless Code Useless Brackets |
131 |
| MUCO 3 Useless Code Useless Brackets |
135 |
| MUCO 3 Useless Code Useless Brackets |
146 |
| MUCO 3 Useless Code Useless Brackets |
156 |
| MUCO 3 Useless Code Useless Brackets |
165 |
| MUCO 3 Useless Code Useless Brackets |
182 |
| MUCO 3 Useless Code Useless Brackets |
190 |
| MUCO 3 Useless Code Useless Brackets |
195 |
| MUCO 3 Useless Code Useless Brackets |
227 |
| MUCO 3 Useless Code Useless Brackets |
231 |
| MUCO 3 Useless Code Useless Brackets |
238 |
| MUCO 3 Useless Code Useless Brackets |
250 |
| MUCO 3 Useless Code Useless Brackets |
296 |
| MUCO 3 Useless Code Useless Brackets |
305 |
| MUCO 3 Useless Code Useless Brackets |
309 |
| MUCO 3 Useless Code Useless Brackets |
332 |
| MUCO 3 Useless Code Useless Brackets |
336 |
| MUCO 3 Useless Code Useless Brackets |
386 |
| MUCO 3 Useless Code Useless Brackets |
401 |
| MUCO 3 Useless Code Useless Brackets |
442 |
| MUCO 3 Useless Code Useless Brackets |
445 |
| MUCO 3 Useless Code Useless Brackets |
453 |
| MUCO 3 Useless Code Useless Brackets |
464 |
| MUCO 3 Useless Code Useless Brackets |
473 |
| MUCO 3 Useless Code Useless Brackets |
486 |
| MUCO 3 Useless Code Useless Brackets |
512 |
| MUCO 3 Useless Code Useless Brackets |
531 |
| MUCO 3 Useless Code Useless Brackets |
561 |
| MUCO 3 Useless Code Useless Brackets |
582 |
| MUCO 3 Useless Code Useless Brackets |
613 |
| MUCO 3 Useless Code Useless Brackets |
627 |
| MUCO 3 Useless Code Useless Brackets |
644 |
| MUCO 3 Useless Code Useless Brackets |
654 |
| MUCO 3 Useless Code Useless Brackets |
656 |
| MUCO 3 Useless Code Useless Brackets |
702 |
| MUCO 3 Useless Code Useless Brackets |
722 |
| MUCO 3 Useless Code Useless Brackets |
741 |
| MUCO 3 Useless Code Useless Brackets |
759 |
| MUCO 3 Useless Code Useless Brackets |
763 |
| MUCO 3 Useless Code Useless Brackets |
768 |
| MUCO 3 Useless Code Useless Brackets |
773 |
| MUCO 3 Useless Code Useless Brackets |
778 |
| MUCO 3 Useless Code Useless Brackets |
783 |
| MUCO 3 Useless Code Useless Brackets |
788 |
| MUCO 3 Useless Code Useless Brackets |
797 |
| MUCO 3 Useless Code Useless Brackets |
809 |
| MUCO 3 Useless Code Useless Brackets |
841 |
| MUCO 3 Useless Code Useless Brackets |
854 |
| MUCO 3 Useless Code Useless Brackets |
866 |
| MUCO 3 Useless Code Useless Brackets |
881 |
| MUCO 3 Useless Code Useless Brackets |
897 |
| MUCO 3 Useless Code Useless Brackets |
947 |
| MUCO 3 Useless Code Useless Brackets |
951 |
| MUCO 3 Useless Code Useless Brackets |
961 |
| MUCO 3 Useless Code Useless Brackets |
966 |
| MUIN 3 Column created using implicit nullability |
219 |
| MUIN 3 Column created using implicit nullability |
391 |
| MUIN 3 Column created using implicit nullability |
707 |
| MUIN 3 Column created using implicit nullability |
727 |
| MUIN 3 Column created using implicit nullability |
846 |
| QAFM 3 Var Assignment from potentially many rows |
526 |
| QAFM 3 Var Assignment from potentially many rows |
596 |
| QAFM 3 Var Assignment from potentially many rows |
658 |
| QAFM 3 Var Assignment from potentially many rows |
690 |
| QAPT 3 Access to Proxy Table master..monEngine |
716 |
| QAPT 3 Access to Proxy Table master..monThread |
716 |
| QCRS 3 Conditional Result Set |
972 |
| QCTC 3 Conditional Table Creation |
391 |
| QCTC 3 Conditional Table Creation |
707 |
| QCTC 3 Conditional Table Creation |
727 |
| QCTC 3 Conditional Table Creation |
846 |
| QCTC 3 Conditional Table Creation |
886 |
| QCTC 3 Conditional Table Creation |
901 |
| QJWT 3 Join or Sarg Without Index on temp table |
497 |
| QNAJ 3 Not using ANSI Inner Join |
495 |
| QNAJ 3 Not using ANSI Inner Join |
716 |
| QNAJ 3 Not using ANSI Inner Join |
747 |
| QNAJ 3 Not using ANSI Inner Join |
887 |
| QNUA 3 Should use Alias: Column field_name should use alias s |
500 |
| QNUA 3 Should use Alias: Column field_name should use alias s |
501 |
| QNUA 3 Should use Alias: Column parent should use alias co |
748 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: #temp_sysmon_baseline.tsbi clustered
(group_name, field_id)
Intersection: {group_name} |
499 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: #tempmonitors.tmci clustered
(group_name, field_id)
Intersection: {group_name} |
528 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: #tempmonitors.tmci clustered
(group_name, field_id)
Intersection: {group_name} |
598 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: #tempmonitors.tmci clustered
(group_name, field_id)
Intersection: {group_name} |
692 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name} |
926 |
| VUNU 3 Variable is not used @sample_time |
47 |
| VUNU 3 Variable is not used @outstr |
62 |
 CRDO 2 Read Only Cursor Marker (has for read only clause) |
744 |
| CRDO 2 Read Only Cursor Marker (has for read only clause) |
857 |
| MDYE 2 Dynamic Exec Marker exec @rtn_status |
928 |
| MRST 2 Result Set Marker |
972 |
| MSUB 2 Subquery Marker |
925 |
| MTR1 2 Metrics: Comments Ratio Comments: 38% |
2 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 76 = 83dec - 9exi + 2 |
2 |
| MTR3 2 Metrics: Query Complexity Complexity: 406 |
2 |
| PRED_QUERY_COLLECTION 2 {me=master..monEngine, mt=master..monThread} 0 |
714 |
| PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 |
744 |
| PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 |
886 |
