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