DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitorconfig  14 déc. 14Defects Propagation Dependencies

1     /*
2     ** Messages for "sp_monitorconfig"
3     **
4     ** 17260, "Can't run %1! from within a transaction."
5     **
6     ** 18283, "Configuration parameter '%1!' is not supported in this system
7     **	  stored procedure."
8     **
9     ** 18292, "An internal error occurred while accessing
10    **        monitor information."
11    **
12    ** 17977, "'%1!' does not exist."
13    */
14    
15    /*
16    ** Syntax:
17    **
18    ** sp_monitorconfig "configname" [, "result_tbl_name"] [, "full"] or
19    ** sp_monitorconfig "all" [, "result_tbl_name"] [, "full"] 
20    **
21    ** This stored procedure provides statistics/monitor related information
22    ** on a configuration parameter. This data could be extracted from
23    ** monitor counters or from other places where statistics are maintained.
24    **
25    ** For example, if the config name is 'open objects', then the monitor 
26    ** information is number of open objects, number of them free, percentage 
27    ** of open objects currently being used, etc. The actual information may be 
28    ** printed in stored procedure(s) invoked from here. Other config parameters 
29    ** can be added here.
30    **
31    ** Note: The server collects these resource stats by default, however,
32    ** you can turn off the activity by boot the server with trace flag 3631.
33    **
34    ** Design:
35    ** 1. There are two temporary table created in this stored procedure:
36    **
37    ** -- #resource_monitor_tbl is used for setting up the resource
38    **	to be monitored. It has 7 columns:
39    **
40    **	create table #resource_monitor_tbl(id int, confignum int,
41    **					   	counter1 varchar(40) NULL,
42    **					   	counter2 varchar(40) NULL,
43    **					   	counter3 varchar(40) NULL,
44    **					  	counter4 varchar(40) NULL,
45    **					 	is_perprocess int,
46    **						multiplier int)
47    **
48    **		o id		-- a unique numbering of the resource
49    **				   to be monitored
50    **		o confignum	-- the configure number of the resource
51    **				   to be added
52    **		o counter1	-- the first monitor counter to be
53    **				   retrieved, usually it is the current
54    **				   active value. 
55    **		o counter2	-- the second monitor counter to be retrieved,
56    **				   usually it is the high watermark value.
57    **		o counter3	-- the third monitor counter to be retrieved,
58    **				   usually it is reused count.
59    **		o counter4	-- the fourth monitor counter to be retrieved,
60    **				   it is reserved for future use, not used now.
61    **		o is_perprocess	-- it designate whether the configure
62    **				   parameter is related to number of pss or
63    **				   worker process:
64    **				   Value 0 - means it is not a per
65    **					     process value.
66    **				   Value 1 - means it is a per process value.
67    **				   value 2 - means it is a per worker
68    **				  	     process value. 
69    **		o multiplier	-- used to calculate the max value of a
70    **				   certain parameter.
71    **
72    ** -- #resource_result_tbl is used to store the monitoring
73    **	results for each resource whose monitoring information being
74    **	collected, and output them once all the collections are done.
75    **	It has 9 columns. Each is self explanatory by its name. 
76    **
77    **	create table #resource_result_tbl(Name varchar(25),
78    **						Config_val int,
79    **						System_val int,
80    **						Total_val int,
81    **						Num_free int,
82    **						Num_active int, 
83    **						Pct_act char(6),
84    **						Max_Used int, 
85    **						Reuse_cnt int)
86    **
87    **
88    ** To add a new monitoring resource, you only need to insert a new row
89    ** into #resource_monitor_tbl, indicating the configure number
90    ** of the resource, the monitor counter you try to retrieve and whether
91    ** it is a per process value.
92    **
93    **
94    ** Note, if you want to save the result into your own specified table,
95    ** provide the table name as the second parameter of this stored procedure. 
96    ** The table if exists should be in the following format:
97    **
98    **		create table table_name(
99    **				Name varchar(35),
100   **				Config_val int, System_val int,
101   **				Total_val int, Num_free int,
102   **				Num_active int, Pct_act char(6),
103   **				Max_Used int, Reuse_cnt int,
104   **				Date varchar(30), Instance_Name varchar(30) NULL)
105   **
106   ** This table is a bit different from #resource_result_tbl. Note the
107   ** Name field is 10 bytes wider.If the table specified as second parameter 
108   ** doesn't exist it will be created with same format as above. You may specify
109   ** fully qualified table name if you want to save the information in a table
110   ** in database other than current one.
111   **
112   ** 2. API for getting monitoring stats:
113   ** --  config_admin() is used to get monitoring stats from the server.
114   **     Following values are passed into this API to indicate the specific
115   **     info we are trying to gather:
116   **		o cmd 		- Set to be 22 (CFG_RESOURCEMONITOR_VALUE) 
117   **				  indicating we are gathering statistics.
118   **		o value1	- Set to the @confignum of the resource
119   **				  we are interested in.
120   **		o value2	- Set to one of following values:
121   **				  1 - gathering adjusment value 
122   **				  2 - gathering the active value
123   **				  3 - gathering the HWM value
124   **				  4 - gathering the reused value
125   **		o value3	- not used
126   **		o value4	- Set to the monitor counter name of
127   **				  the resource we are gathing, if 
128   **				  we get the stats from monitor counters
129   ** 
130   ** If you are adding a new resource to be monitored, you need to add the
131   ** memory pool name used by this resource(if there is one) in 
132   ** function cfg__resmonitor_value() of utils/cfg_mgr.c to calculate the
133   ** actual value, max value and the adjustable value. 
134   **
135   **
136   ** Parameters (for the stored procedure):
137   **	configname - configuration parameter name.
138   **	result_tbl_name - Optional, the name for a table where to store 
139   **			  the results. If the table already exists it should 
140   **			  be in specified format. If the table does not exist
141   ** 			  it will be created in specified format and result 
142   ** 			  will be inserted into this table. It could be
143   **			  in the format owner.tablename with 2 * 255 + 1
144   **			  length.	
145   **	option -	print option. Default is NULL. If the value is 'full'
146   **			the columns Config_val, System_val and Total_val are
147   **			added to the output.
148   **
149   ** Result output:
150   **	If result_tbl_name is not provided, the result will be printed on
151   **	standard output. 
152   **	If result_tbl_name is provided, the result will be inserted into
153   **	the table with result_tbl_name and will not be printed on standard
154   **	output.
155   **
156   ** Returns:
157   **	1 - if error.
158   **	0 - if no error.
159   **
160   */
161   create or replace procedure sp_monitorconfig
162   (@configname varchar(255),
163       @result_tbl_name varchar(511) = NULL,
164       @option varchar(5) = NULL)
165   as
166   
167       declare @confignum int
168       declare @return_value int
169       declare @fullconfigname varchar(255)
170   
171       declare @config_runval int
172       declare @temp_var2 int
173       declare @perct_active float
174       declare @num_active int
175       declare @max_active int
176       declare @reuse_reqs int
177       declare @num_free int
178       declare @msg varchar(1024)
179       declare @curdate varchar(30)
180       declare @counter int
181       declare @total_items int
182       declare @item_id int
183       declare @syst_value int
184       declare @num_wkpss int
185       declare @num_pss int
186       declare @perprocess int
187       declare @insert_add_tbl int
188       declare @procval int
189   
190       declare @reuse_str varchar(10)
191       declare @cmd_str varchar(700) /*
192       ** this string is used for sql
193       ** commands for creating and inserting
194       ** into @result_tbl_name
195       ** the size of cmd_str should be
196       ** atleast size of @result_tbl_name
197       ** additional space for remaining
198       ** parts of the sql command strings
199       */
200       declare @num_free_str varchar(10)
201       declare @num_active_str varchar(10)
202       declare @per_active_str varchar(10)
203       declare @max_used_str varchar(7)
204   
205       declare @counter1 varchar(40)
206       declare @counter2 varchar(40)
207       declare @counter3 varchar(40)
208       declare @counter4 varchar(40)
209   
210       declare @multiplier int
211       declare @user_conn int
212       declare @online_engines int
213       declare @max_value int
214       declare @tot_value int
215       declare @mode int
216       declare @whoami varchar(30) /* stores name of this proc */
217       declare @nullarg char(1)
218       declare @dummy int
219       declare @status1 int
220       declare @status2 int
221       declare @gp_enabled int
222   
223       select @status1 = 1
224       select @status2 = 1
225   
226       select @whoami = object_name(@@procid, db_id('sybsystemprocs'))
227   
228       if @@trancount > 0
229       begin
230           /* 17260, "Can't run %1! from within a transaction." */
231           raiserror 17260, "sp_monitorconfig"
232           return (1)
233       end
234       else
235       begin
236           set chained off
237       end
238   
239       set transaction isolation level 1
240   
241       /* we don't want too much of output */
242       set nocount on
243   
244       /* 
245       ** If granular permissions is not enabled then either mon_role or sa_role
246       ** is required. If granular permissions is enabled then either mon_role or 
247       ** the permission 'manage server' is required.  proc_role and proc_auditperm 
248       ** will also do auditing if required. Both will also print error message if 
249       ** required.
250       */
251   
252       select @nullarg = NULL
253       execute @status1 = sp_aux_checkroleperm "mon_role", "mon_role",
254           @nullarg, @gp_enabled output
255   
256       if (@status1 != 0)
257       begin
258           if (@gp_enabled = 0)
259           begin
260               execute @status2 = sp_aux_checkroleperm "sa_role",
261                   @nullarg, @nullarg, @gp_enabled output
262               if (@status2 != 0)
263               begin
264                   select @dummy = proc_role("sa_role")
265                   select @dummy = proc_role("mon_role")
266                   return (1)
267               end
268               else
269               begin
270                   /* For Auditing */
271                   select @dummy = proc_role("sa_role")
272               end
273   
274           end
275           else
276           begin
277               /* Granular permissions is enabled */
278               execute @status2 = sp_aux_checkroleperm @nullarg,
279                   "manage server", @nullarg, @gp_enabled output
280               select @dummy = proc_auditperm("manage server", @status2)
281               if (@status2 != 0)
282               begin
283                   select @dummy = proc_role("mon_role")
284                   return (1)
285               end
286           end
287       end
288       else
289       begin
290           /* User has mon_role. Audit it. */
291           select @dummy = proc_role("mon_role")
292       end
293   
294       /* Validate config name */
295       if @configname != "all"
296       begin
297           /*
298           ** Validate the configname and get the corresponding config number,
299           ** and the full name of the config option for printing messages.
300           */
301           exec @return_value = sp_validateconfigname @configname,
302               @confignum output,
303               @fullconfigname output
304           if @return_value != 0
305               return @return_value
306   
307       end
308   
309       if (lower(@option) = "full")
310       begin
311           select @mode = 1
312       end
313       else
314       begin
315           select @mode = 0
316       end
317   
318       /*
319       ** Create the control table to hold all the info about the
320       ** resource we want to monitor.
321       */
322       create table #resource_monitor_tbl(id int, confignum int,
323           counter1 varchar(40) NULL,
324           counter2 varchar(40) NULL,
325           counter3 varchar(40) NULL,
326           counter4 varchar(40) NULL,
327           is_perprocess int,
328           multiplier int)
329   
330       /*
331       ** Get the run value of number of user connections and
332       ** number of online engines.
333       */
334       select @user_conn = value
335       from master.dbo.syscurconfigs
336       where config = 103
337   
338   
339       select @online_engines = value
340       from master.dbo.syscurconfigs
341       where config = 126
342   
343   
344       /* -----------------------------------------------------------
345       ** Now insert all the information about the resource that we
346       ** would like to retrieve their monitoring information. 
347       */
348       /* number of open databases */
349       insert into #resource_monitor_tbl
350       values (1, 105, null, null, "open_database_reuse_requests", null, 0, 1)
351   
352       /* number of open objects */
353       insert into #resource_monitor_tbl
354       values (2, 107, null, null, "open_object_reuse_requests", null, 0, 1)
355   
356       /* procedure cache size */
357       insert into #resource_monitor_tbl
358       values (3, 146, "active_procedure_cache", "hwm_procedure_cache",
359           "procedure_cache_reuse_requests", null, 0, 1)
360   
361       /* number of open indexes */
362       insert into #resource_monitor_tbl
363       values (4, 263, null, null, "open_index_reuse_requests", null, 0, 1)
364   
365       /* number of aux scan descriptors */
366       insert into #resource_monitor_tbl
367       values (5, 266, null, null, null, null, 0, 1)
368   
369       /* number of large i/o buffers */
370       insert into #resource_monitor_tbl
371       values (6, 301, "active_dskbufs", "hwm_dskbufs", null, null, 0, 1)
372   
373       /* txn to pss ratio */
374       insert into #resource_monitor_tbl
375       values (7, 185, null, null, null, null, 1, @user_conn)
376   
377       /* number of dtx participants */
378       insert into #resource_monitor_tbl
379       values (8, 347, null, null, "dtxp_reuse_reqs", null, 0, 1)
380   
381       /* number of user connections */
382       insert into #resource_monitor_tbl
383       values (9, 103, "active_connections", "hwm_connections", null, null, 0, 1)
384   
385       /* number of worker processes */
386       insert into #resource_monitor_tbl
387       values (10, 267, "active_worker_process", "hwm_worker_process",
388           null, null, 0, 1)
389   
390       /* partition groups */
391       insert into #resource_monitor_tbl
392       values (11, 242, null, null, null, null, 0, 1)
393   
394       /* number of devices */
395       insert into #resource_monitor_tbl
396       values (12, 116, "active_devices", "hwm_devices", null, null, 0, 1)
397   
398       /* size of global fixed heap */
399       insert into #resource_monitor_tbl
400       values (13, 355, null, null, null, null, 0, 1)
401   
402       /* size of process object heap */
403       insert into #resource_monitor_tbl
404       values (14, 340, null, null, null, null, 0, 1)
405   
406       /* size of shared class heap */
407       insert into #resource_monitor_tbl
408       values (15, 341, null, null, null, null, 0, 1)
409   
410       /* size of unilib cache */
411       insert into #resource_monitor_tbl
412       values (16, 331, null, null, null, null, 0, 1)
413   
414       /* number of java sockets */
415       insert into #resource_monitor_tbl
416       values (17, 395, null, null, null, null, 0, 1)
417   
418       /* number of remote connections */
419       insert into #resource_monitor_tbl
420       values (18, 120, "active_remote_connections", "hwm_remote_connections",
421           null, null, 0, 1)
422   
423       /* number of remote logins */
424       insert into #resource_monitor_tbl
425       values (19, 118, "active_remote_logins", "hwm_remote_logins",
426           null, null, 0, 1)
427   
428       /* number of remote sites */
429       insert into #resource_monitor_tbl
430       values (20, 119, "active_remote_sites", "hwm_remote_sites",
431           null, null, 0, 1)
432   
433       /* audit queue size */
434       insert into #resource_monitor_tbl
435       values (21, 136, null, null, null, null, 0, 1)
436   
437       /* permission cache entries */
438       insert into #resource_monitor_tbl
439       values (22, 186, "active_perm_cache_entries", "hwm_perm_cache_entries",
440           "perm_cache_entries_reuse_requests", null, 1, 1)
441   
442       /* additional network memory */
443       insert into #resource_monitor_tbl
444       values (23, 137, null, null, null, null, 0, 1)
445   
446       /* number of mailboxes */
447       insert into #resource_monitor_tbl
448       values (24, 171, null, null, null, null, 0, 1)
449   
450       /* number of messages */
451       insert into #resource_monitor_tbl
452       values (25, 172, null, null, null, null, 0, 1)
453   
454       /* number of sort buffers */
455       insert into #resource_monitor_tbl
456       values (26, 181, "active_sort_buffers", "hwm_sort_buffers",
457           null, null, 0, 1)
458   
459       /* heap memory per user */
460       insert into #resource_monitor_tbl
461       values (27, 399, null, null, "heap_mem_waits",
462           null, 0, 1)
463   
464       /* max memory. */
465       insert into #resource_monitor_tbl
466       values (28, 396, "hwm_maxmem", "hwm_maxmem", null, null, 0, 1)
467   
468       /* number of locks */
469       insert into #resource_monitor_tbl
470       values (29, 106, null, null, null, null, 0, 1)
471   
472       /* number of alarms */
473       insert into #resource_monitor_tbl
474       values (30, 173, null, null, null, null, 0, 1)
475   
476       /* max cis remote connections */
477       insert into #resource_monitor_tbl
478       values (31, 277, null, null, null, null, 0, 1)
479   
480       /* memory per worker process */
481       insert into #resource_monitor_tbl
482       values (32, 268, null, null, null, null, 2, 1)
483   
484       /* max online engines */
485       insert into #resource_monitor_tbl
486       values (33, 126, "active_online_engines", "hwm_online_engines",
487           null, null, 0, 1)
488   
489       /* max number network listeners */
490       insert into #resource_monitor_tbl
491       values (34, 156, "active_network_listeners", "hwm_network_listeners",
492           null, null, 0, 1)
493   
494       /* disk i/o structures */
495       insert into #resource_monitor_tbl
496       values (35, 150, null, null, null, null, 0, 1)
497   
498       /* number of open partitions */
499       insert into #resource_monitor_tbl
500       values (36, 408, null, null, "open_partition_reuse_requests", null,
501           0, 1)
502   
503       /* kernel resource memory */
504       insert into #resource_monitor_tbl
505       values (37, 514, null, null, null, null, 0, 1)
506   
507       /* CIPC regular message pool size */
508       if (@@clustermode = "shared disk cluster")
509           insert into #resource_monitor_tbl
510           values (38, 481, null, null, null, null,
511               0, 1)
512   
513       /* CIPC large message pool size */
514       if (@@clustermode = "shared disk cluster")
515           insert into #resource_monitor_tbl
516           values (39, 465, null, null, null, null,
517               0, 1)
518   
519       /* compression info pool size */
520       insert into #resource_monitor_tbl
521       values (40, 519, null, null, null, null,
522           0, 1)
523   
524       /* ------------------------end of insertion------------------------ */
525   
526   
527       /*
528       ** Check whether the specified configure parameter is in the control
529       ** table(resource_monitor_tbl)
530       */
531       if (@configname != "all")
532       begin
533   
534           if not exists (select id
535                   from #resource_monitor_tbl
536                   where (confignum = @confignum))
537           begin
538               raiserror 18283, @fullconfigname
539               return (1)
540           end
541       end
542   
543       /* Create a temp table #resource_result_tbl to store all the result. */
544       create table #resource_result_tbl(Name varchar(25),
545           Config_val int, System_val int,
546           Total_val int, Num_free int,
547           Num_active int, Pct_act char(6),
548           Max_Used int, Reuse_cnt int,
549           Instance_Name varchar(30) NULL)
550       select @insert_add_tbl = 0
551       if (@result_tbl_name is not NULL)
552       begin
553           if (object_id(@result_tbl_name) is NULL)
554           begin
555               /* 
556               ** since table doesn't exist create it
557               ** if creation is successful, set @insert_add_tbl to 1
558               ** otherwise  keep it to @insert_add_tbl = 0
559               */
560   
561               select @cmd_str = "create table "
562                   + @result_tbl_name
563                   + "("
564                   + "Name varchar(35),"
565                   + "Config_val int,"
566                   + "System_val int,"
567                   + "Total_val int,"
568                   + "Num_free int,"
569                   + "Num_active int,"
570                   + "Pct_act char(6),"
571                   + "Max_Used int,"
572                   + "Reuse_cnt int,"
573                   + "Date varchar(30),"
574                   + "Instance_Name varchar(30) NULL)"
575   
576               exec @return_value = sp_exec_SQL @cmd_str, @whoami
577               if (@return_value != 0)
578               begin
579                   return (1)
580               end
581               select @insert_add_tbl = 1
582           end
583           else
584           begin
585               select @insert_add_tbl = 1
586           end
587       end
588   
589       /* Check how many items we need to retrieve */
590       if (@configname = "all")
591       begin
592           set @item_id = 1
593   
594           -- We need to loop through finding all rows starting from id=1
595           -- through the max id. This is because some items maybe inserted
596           -- in SDC mode only, with sequentially increasing id. So, if we
597           -- use count(*), on SMP, the # of rows might be 38, but the max ID
598           -- might be higher. (The SDC-only items are inserted in the middle.
599           -- See entries for 'CIPC regular message pool size' onwards as an
600           -- example of how this might occur.).
601           --
602           select @total_items = max(id) from #resource_monitor_tbl
603       end
604       else
605       begin
606           select @total_items = 1
607               , @item_id = id from #resource_monitor_tbl
608           where (confignum = @confignum)
609       end
610   
611       select @counter = 1
612   
613       /*
614       ** Now loop through the monitoring list to get information for
615       ** each monitoring items.
616       */
617       while (@counter <= @total_items)
618       begin -- {
619   
620           /* Get configure parameter info from the control table. */
621           select @confignum = confignum, @counter1 = counter1,
622               @counter2 = counter2, @counter3 = counter3,
623               @counter4 = counter4, @perprocess = is_perprocess,
624               @multiplier = multiplier
625           from #resource_monitor_tbl
626           where (id = @item_id)
627   
628           -- Due to the items inserted mid-way for SDC entries, we might
629           -- not find a matching entry (when executing this in SMP ASE).
630           -- If so, skip this id and continue.
631           --
632           if (@confignum IS NULL)
633               goto next_item
634   
635           /*
636           ** Get the run value for the config variable
637           */
638           select @config_runval = value
639           from master.dbo.syscurconfigs
640           where config = @confignum
641   
642   
643           /* 
644           ** Retrieve info for the 4 counters. Pass flag 2 as the third
645           ** parameter of config_admin() to get the active value
646           */
647           select @num_active = config_admin(22, @confignum, 2, 0, @counter1, NULL)
648   
649           /*
650           ** Pass flag 3 as the third parameter of config_admin() to get
651           ** the max value. 
652           */
653           select @max_active = config_admin(22, @confignum, 3, 0, @counter2, NULL)
654   
655           /*
656           ** Pass flag 4 as the third parameter of config_admin() to get
657           ** reused value
658           */
659           if (@counter3 is not NULL)
660           begin
661               select @reuse_reqs = config_admin(22, @confignum, 4, 0, @counter3, NULL)
662           end
663           else
664           begin
665               select @reuse_reqs = 0
666           end
667   
668   
669           /* 
670           ** Check the return results: null is unexpected for these
671           ** values. We will tolerate these errors by resetting
672           ** the values to some appropriate numbers and continuing 
673           ** collecting other stats. 
674           */
675           if ((@num_active is null) or (@max_active is null) or
676                   (@reuse_reqs is null))
677           begin
678               /*
679               ** 18292, "An internal error occurred while accessing
680               **	  monitor information." 
681               */
682               raiserror 18292
683               select @num_active = 0
684                   , @max_active = 0
685                   , @reuse_reqs = - 1
686           end
687   
688           /*
689           ** If the return value of @num_active and @max_active is negative,
690           ** reset them to 0 and continue.
691           */
692           if (@num_active < 0)
693           begin
694               select @num_active = 0
695           end
696           if (@max_active < 0)
697           begin
698               select @max_active = 0
699           end
700   
701           /* Get the configure parameter's name. */
702           if (@fullconfigname is NULL)
703               select @fullconfigname = name from master..sysconfigures
704               where config = @confignum
705   
706   
707           /* 
708           ** The adjustment value is the size of the resource-pool when the
709           ** actual configured value for the parameter is set to 0.
710           ** Pass flag 1 as the third parameter of config_admin() to get
711           ** the adjustment value. 
712           */
713           select @syst_value = config_admin(22, @confignum, 1, 0, NULL, NULL)
714   
715           /* 
716           ** Per pss resource, "permission cache entries" needs to be divided 
717           ** by number of psses.
718           */
719           if (@perprocess = 1)
720           begin
721               /* Get cfgpss. */
722               select @num_pss = config_admin(22, 190, 0, 0, NULL, NULL)
723   
724               select @num_active = @num_active / @num_pss
725                   , @max_active = @max_active / @num_pss
726               select @reuse_reqs = @reuse_reqs / @num_pss
727           end
728   
729           /* Per worker process resource, such as "memory per worker process" */
730           if (@perprocess = 2)
731           begin
732               /* Get worker processes. */
733               select @num_wkpss = value
734               from master.dbo.syscurconfigs
735               where config = 267
736   
737   
738               if (@num_wkpss != 0)
739               begin
740                   select @num_active = @num_active / @num_wkpss
741                       , @max_active = @max_active / @num_wkpss
742               end
743               else
744               begin
745                   select @num_active = 0
746                       , @max_active = 0
747               end
748           end
749   
750           /*
751           ** Convert in 2k pagesize the values for these config parameters:
752           **   - 'size of global fixed heap'
753           **   - 'size of shared class heap'
754           **   - 'compression info pool size'
755           **
756           ** because they are measured in bytes. Note that the configure
757           ** parameter 'size of process object heap' must not be converted
758           ** here because it is measured in 2k pagesize value already.
759           */
760           if (@confignum IN (355 -- size of global fixed heap
761                       , 341 -- size of shared class heap
762                       , 519 -- compression info pool size
763                   ))
764           begin
765               select @num_active = (@num_active + 2047) / 2048
766                   , @max_active = (@max_active + 2047) / 2048
767           end
768   
769           /*
770           ** Depending on the type of parameter we need to take
771           ** the multiplier to correctly calculate the max possible
772           ** value. Some config options are per user connection
773           ** or per engine.
774           */
775           if (@config_runval = 0 and @syst_value = 0)
776           begin
777               /*
778               ** As there're no resources available, it's
779               ** not possible to show any active numbers.
780               */
781               select @tot_value = 0
782                   , @perct_active = 0
783                   , @num_active = 0
784                   , @num_free = 0
785                   , @max_active = 0
786                   , @reuse_reqs = 0
787           end
788           else
789           begin
790               /*
791               ** As we don't use synchronization methods for
792               ** monitor counters, it's possible some numbers
793               ** are off. We adjust when necessary.
794               */
795               if (@num_active > @max_active)
796               begin
797                   select @num_active = @max_active
798               end
799   
800               /*
801               ** Are we using a pool configured by the user
802               ** or a pool allocated by the server ? We first
803               ** test the config value as this takes precedence
804               ** over the adjust value.
805               */
806               if (@config_runval != 0)
807               begin
808                   select @tot_value = @config_runval
809   
810                   /*
811                   ** When we have a config value
812                   ** the adjustment value for
813                   ** max cis remote connections
814                   ** should revert back to 0.
815                   */
816                   if (@confignum = 277)
817                   begin
818                       select @syst_value = 0
819                   end
820               end
821               else
822               begin
823                   select @tot_value = @syst_value
824               end
825   
826               select @max_value = @multiplier * @tot_value
827   
828               /*
829               ** First check if the @num_active is bigger then
830               ** the @max_value. This is possible if
831               ** the config value is set to a number smaller
832               ** then to what's currently being used.
833               */
834               if (@num_active > @max_value)
835               begin
836                   select @max_value = @num_active
837   
838                   /*
839                   ** We need to adjust total also
840                   ** to prevent from having wrong
841                   ** numbers later on. As we don't
842                   ** know the previous config/system
843                   ** value we make total equal to
844                   ** @max_value (i.e. @num_active).
845                   */
846                   if (@multiplier > 0)
847                   begin
848                       select @tot_value =
849                           @max_value / @multiplier
850                   end
851               end
852   
853               /*
854               ** Make sure @max_active is not
855               ** set to a value greater then the max. possible
856               ** value based on the configured run value.
857               */
858               if (@max_active > @max_value)
859               begin
860                   select @max_active = @max_value
861               end
862   
863               /*
864               ** Percentage of resources in active state.
865               */
866               if (@multiplier > 0)
867               begin
868                   select @perct_active = (@num_active * 100.0) /
869                       @max_value
870               end
871               else
872               begin
873                   select @perct_active = 0
874               end
875   
876               /*
877               ** Number of free resources
878               */
879               select @num_free = @max_value - @num_active
880           end
881   
882           /*
883           ** Insert the results into the result table only if table name is not
884           ** provided. 
885           */
886           if (@insert_add_tbl = 0)
887           begin
888               insert into #resource_result_tbl values (
889                   convert(char(30), @fullconfigname),
890                   @config_runval,
891                   @syst_value,
892                   @tot_value,
893                   @num_free,
894                   @num_active,
895                   convert(char(6),
896                   (rtrim(str(@perct_active, 6, 2)))),
897                   @max_active,
898                   @reuse_reqs,
899                   @@instancename)
900           end
901           else
902           begin
903               select @curdate = convert(varchar, getdate())
904   
905               /*
906               ** Insert the results into user supplied table if
907               ** the table name is provided.
908               */
909               select @cmd_str = "insert into " + @result_tbl_name
910                   + "(Name, Config_val, System_val, Total_val, "
911                   + "Num_free, Num_active, Pct_act, Max_Used, "
912                   + "Reuse_cnt, Date, Instance_Name)"
913                   + " values('"
914                   + convert(varchar(35), @fullconfigname)
915                   + "',"
916                   + convert(varchar(11), @config_runval)
917                   + ","
918                   + convert(varchar(11), @syst_value)
919                   + ","
920                   + convert(varchar(11), @tot_value)
921                   + ","
922                   + convert(varchar(11), @num_free)
923                   + ","
924                   + convert(varchar(11), @num_active)
925                   + ", '"
926                   + convert(varchar(6),
927                   (rtrim(str(@perct_active, 6, 2))))
928                   + "',"
929                   + convert(varchar(11), @max_active)
930                   + ", "
931                   + convert(varchar(11), @reuse_reqs)
932                   + ", '"
933                   + convert(varchar(30), @curdate)
934                   + "' ,'"
935                   + @@instancename
936                   + "')"
937   
938               exec @return_value = sp_exec_SQL @cmd_str, @whoami
939               if (@return_value != 0)
940               begin
941                   return (1)
942               end
943           end
944   
945   next_item:
946           /* Get to next item if there are any. */
947           if (@configname = "all")
948           begin
949               select @item_id = @item_id + 1
950           end
951   
952           -- Reset @confignum to NULL so that we can check if a new row for
953           -- the next id is being correctly obtained.
954           --
955           select @fullconfigname = NULL
956               , @reuse_reqs = NULL
957               , @num_active = NULL
958               , @max_active = NULL
959               , @confignum = NULL
960               , @counter = @counter + 1
961       end -- }
962   
963       /*
964       ** Send the result to standard output only if the results are not
965       ** already inserted into a user supplied table.
966       */
967       if (@insert_add_tbl != 1)
968       begin
969           /*
970           ** 18284, "Usage information at date and time: %1!"
971           */
972           exec sp_getmessage 18284, @msg output
973           select @curdate = convert(varchar, getdate())
974   
975           /* print usage date and time */
976           print @msg, @curdate
977           print ""
978   
979           if (@mode = 1)
980           begin
981               select
982                   Name,
983                   Config_val "Configure Value",
984                   System_val "System Value",
985                   Total_val "Run Value",
986                   Num_free,
987                   Num_active,
988                   Pct_act,
989                   Max_Used,
990                   Reuse_cnt,
991                   Instance_Name
992               from #resource_result_tbl
993               order by Name
994           end
995           else
996           begin
997               select
998                   Name,
999                   Num_free,
1000                  Num_active,
1001                  Pct_act,
1002                  Max_Used,
1003                  Reuse_cnt,
1004                  Instance_Name
1005              from #resource_result_tbl
1006              order by Name
1007  
1008          end
1009      end
1010  
1011      return (0)
1012  


exec sp_procxmode 'sp_monitorconfig', 'AnyMode'
go

Grant Execute on sp_monitorconfig to public
go
RESULT SETS
sp_monitorconfig_rset_002
sp_monitorconfig_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 977
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch Instance_Name: varchar(30) = int 899
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 336
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 341
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 640
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 704
 QTYP 4 Comparison type mismatch smallint = int 704
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 735
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MAW1 3 Warning message on %name% tempdb..#resource_monitor_tbl.id: Warning message on #resource_monitor_tbl_crby_sybsystemprocs__sp_monitorconfig 534
 MAW1 3 Warning message on %name% tempdb..#resource_monitor_tbl.id: Warning message on #resource_monitor_tbl_crby_sybsystemprocs__sp_monitorconfig 602
 MAW1 3 Warning message on %name% tempdb..#resource_monitor_tbl.id: Warning message on #resource_monitor_tbl_crby_sybsystemprocs__sp_monitorconfig 607
 MAW1 3 Warning message on %name% tempdb..#resource_monitor_tbl.id: Warning message on #resource_monitor_tbl_crby_sybsystemprocs__sp_monitorconfig 626
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_monitorconfig  
 MNER 3 No Error Check should check return value of exec 278
 MNER 3 No Error Check should check @@error after insert 349
 MNER 3 No Error Check should check @@error after insert 353
 MNER 3 No Error Check should check @@error after insert 357
 MNER 3 No Error Check should check @@error after insert 362
 MNER 3 No Error Check should check @@error after insert 366
 MNER 3 No Error Check should check @@error after insert 370
 MNER 3 No Error Check should check @@error after insert 374
 MNER 3 No Error Check should check @@error after insert 378
 MNER 3 No Error Check should check @@error after insert 382
 MNER 3 No Error Check should check @@error after insert 386
 MNER 3 No Error Check should check @@error after insert 391
 MNER 3 No Error Check should check @@error after insert 395
 MNER 3 No Error Check should check @@error after insert 399
 MNER 3 No Error Check should check @@error after insert 403
 MNER 3 No Error Check should check @@error after insert 407
 MNER 3 No Error Check should check @@error after insert 411
 MNER 3 No Error Check should check @@error after insert 415
 MNER 3 No Error Check should check @@error after insert 419
 MNER 3 No Error Check should check @@error after insert 424
 MNER 3 No Error Check should check @@error after insert 429
 MNER 3 No Error Check should check @@error after insert 434
 MNER 3 No Error Check should check @@error after insert 438
 MNER 3 No Error Check should check @@error after insert 443
 MNER 3 No Error Check should check @@error after insert 447
 MNER 3 No Error Check should check @@error after insert 451
 MNER 3 No Error Check should check @@error after insert 455
 MNER 3 No Error Check should check @@error after insert 460
 MNER 3 No Error Check should check @@error after insert 465
 MNER 3 No Error Check should check @@error after insert 469
 MNER 3 No Error Check should check @@error after insert 473
 MNER 3 No Error Check should check @@error after insert 477
 MNER 3 No Error Check should check @@error after insert 481
 MNER 3 No Error Check should check @@error after insert 485
 MNER 3 No Error Check should check @@error after insert 490
 MNER 3 No Error Check should check @@error after insert 495
 MNER 3 No Error Check should check @@error after insert 499
 MNER 3 No Error Check should check @@error after insert 504
 MNER 3 No Error Check should check @@error after insert 509
 MNER 3 No Error Check should check @@error after insert 515
 MNER 3 No Error Check should check @@error after insert 520
 MNER 3 No Error Check should check @@error after insert 888
 MNER 3 No Error Check should check return value of exec 972
 MUCO 3 Useless Code Useless Brackets in create proc 162
 MUCO 3 Useless Code Useless Brackets 232
 MUCO 3 Useless Code Useless Brackets 256
 MUCO 3 Useless Code Useless Brackets 258
 MUCO 3 Useless Code Useless Brackets 262
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 281
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 508
 MUCO 3 Useless Code Useless Brackets 514
 MUCO 3 Useless Code Useless Brackets 531
 MUCO 3 Useless Code Useless Brackets 536
 MUCO 3 Useless Code Useless Brackets 539
 MUCO 3 Useless Code Useless Brackets 551
 MUCO 3 Useless Code Useless Brackets 553
 MUCO 3 Useless Code Useless Brackets 577
 MUCO 3 Useless Code Useless Brackets 579
 MUCO 3 Useless Code Useless Brackets 590
 MUCO 3 Useless Code Useless Brackets 608
 MUCO 3 Useless Code Useless Brackets 617
 MUCO 3 Useless Code Useless Brackets 626
 MUCO 3 Useless Code Useless Brackets 632
 MUCO 3 Useless Code Useless Brackets 659
 MUCO 3 Useless Code Useless Brackets 675
 MUCO 3 Useless Code Useless Brackets 692
 MUCO 3 Useless Code Useless Brackets 696
 MUCO 3 Useless Code Useless Brackets 702
 MUCO 3 Useless Code Useless Brackets 719
 MUCO 3 Useless Code Useless Brackets 730
 MUCO 3 Useless Code Useless Brackets 738
 MUCO 3 Useless Code Useless Brackets 760
 MUCO 3 Useless Code Useless Brackets 775
 MUCO 3 Useless Code Useless Brackets 795
 MUCO 3 Useless Code Useless Brackets 806
 MUCO 3 Useless Code Useless Brackets 816
 MUCO 3 Useless Code Useless Brackets 834
 MUCO 3 Useless Code Useless Brackets 846
 MUCO 3 Useless Code Useless Brackets 858
 MUCO 3 Useless Code Useless Brackets 866
 MUCO 3 Useless Code Useless Brackets 886
 MUCO 3 Useless Code Useless Brackets 896
 MUCO 3 Useless Code Useless Brackets 927
 MUCO 3 Useless Code Useless Brackets 939
 MUCO 3 Useless Code Useless Brackets 941
 MUCO 3 Useless Code Useless Brackets 947
 MUCO 3 Useless Code Useless Brackets 967
 MUCO 3 Useless Code Useless Brackets 979
 MUCO 3 Useless Code Useless Brackets 1011
 MUIN 3 Column created using implicit nullability 322
 MUIN 3 Column created using implicit nullability 544
 QAFM 3 Var Assignment from potentially many rows 334
 QAFM 3 Var Assignment from potentially many rows 339
 QAFM 3 Var Assignment from potentially many rows 606
 QAFM 3 Var Assignment from potentially many rows 621
 QAFM 3 Var Assignment from potentially many rows 638
 QAFM 3 Var Assignment from potentially many rows 703
 QAFM 3 Var Assignment from potentially many rows 733
 QCRS 3 Conditional Result Set 981
 QCRS 3 Conditional Result Set 997
 QISO 3 Set isolation level 239
 QSWV 3 Sarg with variable @confignum, Candidate Index: sysconfigures.nc2sysconfigures(config) F 704
 VNRD 3 Variable is not read @gp_enabled 279
 VNRD 3 Variable is not read @dummy 291
 VNRD 3 Variable is not read @online_engines 339
 VUNU 3 Variable is not used @temp_var2 172
 VUNU 3 Variable is not used @procval 188
 VUNU 3 Variable is not used @reuse_str 190
 VUNU 3 Variable is not used @num_free_str 200
 VUNU 3 Variable is not used @num_active_str 201
 VUNU 3 Variable is not used @per_active_str 202
 VUNU 3 Variable is not used @max_used_str 203
 MRST 2 Result Set Marker 981
 MRST 2 Result Set Marker 997
 MSUB 2 Subquery Marker 534
 MTR1 2 Metrics: Comments Ratio Comments: 47% 161
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 38 = 44dec - 8exi + 2 161
 MTR3 2 Metrics: Query Complexity Complexity: 316 161

DATA PROPAGATION detailed
ColumnWritten To
@result_tbl_namesp_exec_SQL_rset_001.sqlbNoName57

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_monitorconfig_rset_002 
calls proc sybsystemprocs..sp_exec_SQL  
   writes table sybsystemprocs..sp_exec_SQL_rset_001 
writes table sybsystemprocs..sp_monitorconfig_rset_001 
reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
read_writes table tempdb..#resource_result_tbl (1) 
calls proc sybsystemprocs..sp_validateconfigname  
   calls proc sybsystemprocs..sp_getmessage  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
   writes table sybsystemprocs..sp_validateconfigname_rset_001 
reads table master..sysconfigures (1)  
read_writes table tempdb..#resource_monitor_tbl (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  

CALLERS
called by proc sybsystemprocs..sp_sysmon_mdcache  
   called by proc sybsystemprocs..sp_sysmon_analyze  
      called by proc sybsystemprocs..sp_sysmon