Database | Proc | Application | Created | Links |
sybsystemprocs | sp_do_poolconfig ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* This stored procedure is for configuring buffer pools. This is an internal 3 ** stored procedure calle by either sp_poolconfig or from recovery process 4 ** directly. 5 ** 6 ** Messages for sp_do_poolconfig 7 ** 8 ** 17260, "Can't run %1 from within a transaction." 9 ** 10 ** 18076, "Could not set curwrite to object level. Set your maxwrite 11 ** label correctly." 12 ** 13 ** 18095, "The destination buffer pool size must be a minimum of %1! 14 ** kilobytes." 15 ** 16 ** 18138, "A cache name must be supplied." 17 ** 18 ** 18135, "The specified named cache (%1) does not exist." 19 ** 20 ** 18141, "Syntax error encountered. Specification of the wash size must be 21 ** of the form 'wash = %d[PKMG]' 22 ** 23 ** 18142, "I/O size of the memory pool is expected as the third argument." 24 ** 25 ** 18143, "The source pool (%1k buffers, total size %2k) is not large enough 26 ** to satisfy the request to move %3k of memory. 27 ** 28 ** 18144, "Source pool (%1k) and destination pool (%2k) are the same pool. 29 ** The source and destination pools must be different." 30 ** 31 ** 18147, "Invalid buffer size of %1! encountered. Valid buffer sizes are 32 ** powers of two between %2! and %3! kilobytes inclusive." 33 ** 34 ** 18148, "The specified pool (%1!) does not exist in named cache '%2!'." 35 ** 36 ** 18558, "Unable to delete the '%1!' pool in cache '%2!'." 37 ** 38 ** 18559, "This pool has been marked unavailable. You can 39 ** remove it completely by retrying sp_poolconfig when 40 ** the cache is not in use or by restarting the Server." 41 ** 42 ** 18560, "Unable to move '%1!' from the '%2!' pool to the '%3!' pool in 43 ** cache '%4!'. Only '%5!' moved." 44 ** 45 ** 18339, "The '%1!' pool has been marked as being unavailable since it is 46 ** smaller than the minimum pool size of %2! kilobytes." 47 ** 48 ** 18562, "You can size the pools correctly by retrying sp_poolconfig 49 ** when the cache is not in use or by restarting the Server." 50 ** 51 ** 19111, "Cannot change the pool configurations in the '%1!' when the server is 52 ** in recovery." 53 ** 54 ** 19595, "Instance '%1!' does not exist." 55 ** 56 ** 19973, "Configuring large I/O pools for in-memory storage cache '%1' is 57 ** not allowed." 58 */ 59 60 create procedure sp_do_poolconfig 61 @cachename varchar(255) = NULL, 62 @parm1 varchar(30) = NULL, 63 @parm2 varchar(40) = NULL, 64 @parm3 varchar(30) = NULL, 65 @is_in_recovery varchar(30) = NULL, 66 @parm4 varchar(255) = NULL 67 as 68 69 declare @equal_sign_loc int /* for parsing parms */ 70 declare @wash_or_apf_val_loc int /* for parsing parms */ 71 declare @wash_or_apf_size int /* user supplied wash size or ' 72 ** apf percent 73 */ 74 declare @wash_delta_size int /* delta of the wash size */ 75 declare @src_buf_size int /* size of bufs in src pool */ 76 declare @dest_buf_size int /* size of bufs in dest pool */ 77 declare @config_num_src int /* config # for src pool */ 78 declare @config_num_dest int /* config # for dest pool */ 79 declare @temp_size int /* temporary variable */ 80 declare @requested_size int /* requested total size of dest pool */ 81 declare @existing_pool_size int /* exisiting size of dest pool */ 82 declare @curr_pool_size int /* current exisiting size of dest pool */ 83 declare @stat int /* status of cache from sysconfigures */ 84 declare @stat1 int /* status of cache from sysconfigures */ 85 declare @stat2 int /* status of cache from sysconfigures */ 86 declare @mem_to_move int /* amount of mem to move */ 87 declare @inst_mem_to_move int /* amount of mem to move at an instance */ 88 declare @mem_moved int /* Memory that should have been moved */ 89 declare @src_pool_size int /* amount of mem in src pool */ 90 declare @dest_pool_size int /* amount of mem in dest pool */ 91 declare @ret int /* return status from sp_aux_getsize */ 92 declare @result_size int /* resulting size of dest pool after 93 ** config_admin call. 94 */ 95 declare @delete_pool int /* 1 if src pool 2 if dest pool */ 96 declare @temp_float float /* for temp float calculations */ 97 declare @temp_value int /* for temp int calculations */ 98 declare @temp_pool_size int /* for temp pool size value */ 99 declare @dest_buf_sz_str varchar(30) /* size of bufs in dest pool */ 100 declare @src_buf_sz_str varchar(30) /* size of bufs in src pool */ 101 declare @dest_pool_sz_str varchar(30) /* total mem in dest pool */ 102 declare @res_sz_str varchar(30) /* str value of result size */ 103 declare @req_sz_str varchar(30) /* str value of requested size */ 104 declare @temp_str varchar(30) /* scratch pad */ 105 declare @wash_or_apf_str varchar(255) /* wash size or apf percentage */ 106 declare @msg varchar(255) 107 declare @config_file_name varchar(255) 108 declare @min_buf_size int 109 declare @max_buf_size int 110 declare @rec_state varchar(30) 111 declare @instanceid int 112 declare @instancename varchar(255) 113 declare @cur_instanceid int 114 declare @cur_instancename varchar(255) 115 declare @instance_count int 116 declare @failure_count int 117 declare @total_pool_size int 118 declare @cache_size int 119 declare @i int /* iterator */ 120 121 /* declare and init the config_admin() commands used in this sproc */ 122 declare @cmdbindingcheck int 123 , @cmdsetwashsize int 124 , @cmdpoolconfig int 125 , @cmdsetapfsize int 126 , @cmdupdateconfigfile int 127 128 select @cmdbindingcheck = 9 129 , @cmdsetwashsize = 10 130 , @cmdpoolconfig = 11 131 , @cmdsetapfsize = 17 132 , @cmdupdateconfigfile = 32 133 134 /* declare and init the config options used in this sproc */ 135 declare @cfguserdefinedcache int 136 , @cfgbuffer2kpoolgrp int 137 , @cfgcfgfilename int 138 139 select @cfguserdefinedcache = 19 140 , @cfgbuffer2kpoolgrp = 20 141 , @cfgcfgfilename = 114 142 143 select @wash_or_apf_str = NULL 144 145 if @@trancount > 0 146 begin 147 raiserror 17260, "sp_poolconfig" 148 return 1 149 end 150 else 151 begin 152 set chained off 153 end 154 155 set transaction isolation level 1 156 157 if @cachename is NULL 158 begin 159 raiserror 18138 160 return 1 161 end 162 163 /* 164 ** If the cache name is the only parameter that's supplied just pass it 165 ** along to sp_cacheconfig and have it print out all of the configured pools 166 ** for the cache. 167 */ 168 if (@parm1 is NULL) and (@parm2 is NULL) and (@parm3 is NULL) 169 begin 170 exec sp_cacheconfig @cachename 171 return 0 172 end 173 else if (patindex("instance %", @parm1) != 0) and (@parm2 is NULL) and (@parm3 is NULL) 174 begin 175 exec sp_cacheconfig @cachename, @parm1 176 return 0 177 end 178 179 /* 180 ** check if user has sa role, proc_role will also do auditing & print error 181 ** message, if required. 182 */ 183 if (proc_role("sa_role") = 0) 184 return 1 185 186 /* 187 ** If we are in the middle of boot time recovery, and not called by recovery, 188 ** changing configuration in default data cache is not allowed. 189 */ 190 select @rec_state = @@recovery_state 191 if ((@rec_state not like "NOT_IN_RECOVERY%") 192 and (@is_in_recovery not in ("TRUE", "true"))) 193 begin 194 if (patindex("%default data cache%", @cachename) != 0) 195 begin 196 raiserror 19111, @cachename 197 return 1 198 end 199 end 200 201 select @instancename = NULL 202 select @instanceid = NULL 203 204 205 206 select @dest_pool_sz_str = @parm1 207 select @min_buf_size = @@maxpagesize / 1024 208 select @max_buf_size = @min_buf_size * 8 209 210 /* 211 ** Parm2 may be of the form "wash = %d" or %d [K | M | G] 212 ** Or 213 ** Parm2 may be of the form "local async prefetch limit=%d" 214 */ 215 if (patindex("%wash%", @parm2) != 0) or (patindex("%local async%", @parm2) != 0) 216 begin -- { 217 /* 218 ** Translate destination buffer pool into units of k 219 */ 220 exec @ret = sp_aux_getsize @dest_pool_sz_str, @dest_buf_size output 221 222 if @ret = 0 223 begin 224 raiserror 18147, @dest_pool_sz_str, @min_buf_size, @max_buf_size 225 return 1 226 end 227 228 select @equal_sign_loc = 0 229 select @equal_sign_loc = patindex("%=%", @parm2) 230 /* 231 ** proper syntax for specifying the wash is "wash = %d". Make sure 232 ** the string follows this syntax. 233 */ 234 if @equal_sign_loc = 0 235 begin 236 raiserror 18141 237 return 1 238 end 239 240 select @wash_or_apf_val_loc = patindex("%[0-9]%", @parm2) 241 select @wash_or_apf_str = convert(varchar(255), substring(@parm2, 242 @wash_or_apf_val_loc, 243 char_length(@parm2) - 244 @wash_or_apf_val_loc + 1)) 245 246 /* sp_aux_get_size will take string of the form "100 [kmpg]" */ 247 exec @ret = sp_aux_getsize @wash_or_apf_str, @wash_or_apf_size output 248 if @ret = 0 249 begin 250 raiserror 18141 251 return 1 252 end 253 254 /* 255 ** First sanity check the destination buffer size to make sure it's 256 ** within the legal range i.e., powers of 2 between a logical page 257 ** to 8 logical pages. 258 */ 259 if (@dest_buf_size < @min_buf_size) or (@dest_buf_size > @max_buf_size) 260 begin 261 raiserror 18147, @dest_pool_sz_str, @min_buf_size, @max_buf_size 262 return 1 263 end 264 265 select @temp_size = 2 266 while @temp_size < @dest_buf_size 267 select @temp_size = @temp_size * 2 268 if @temp_size != @dest_buf_size 269 begin 270 raiserror 18147, @dest_pool_sz_str, @min_buf_size, @max_buf_size 271 return 1 272 end 273 274 /* 275 ** Config numbers in sysconfigures start at 20 for the 2k buffer pool, 276 ** 21 for the 4k buffer pool, 22 for the 8k buffer pool and so on. 277 ** Basically if we add log base 2 of the destination buffer size to 278 ** the base of 20 and subtract one we should get the correct config 279 ** number to index into sysconfigures. 280 */ 281 select @config_num_dest 282 = @cfgbuffer2kpoolgrp 283 + (log10(@dest_buf_size * 1024 / @@pagesize) / log10(2)) 284 285 /* Parm3 may contain instance specific information */ 286 if (charindex("instance ", @parm3) = 1) 287 begin 288 select @instancename = substring(@parm3, 10, 289 char_length(@parm3) - 9) 290 select @instanceid = instance_id(@instancename) 291 292 if @instanceid is NULL 293 begin 294 raiserror 19595, @instancename 295 return 1 296 end 297 end 298 299 /* Check to see if the cache exists */ 300 select @stat = - 1 301 select @stat = status 302 from master.dbo.sysconfigures 303 where name = @cachename 304 and parent = @cfguserdefinedcache 305 and config = @cfguserdefinedcache 306 and status & 128 != 128 307 308 309 if @stat = - 1 310 begin 311 raiserror 18135, @cachename 312 return 1 313 end 314 315 /* Disallow configuring large I/O pool for in-memory storage cache. */ 316 if (@stat & 65536 = 65536) 317 begin 318 raiserror 19973, @cachename 319 return 1 320 end 321 322 323 324 begin 325 select @instance_count = 1 326 end 327 328 select @failure_count = 0 329 330 331 332 select @i = @instance_count 333 while (@i > 0) 334 begin -- { 335 336 337 select @temp_size = memory_used 338 from master.dbo.syscurconfigs 339 where config = @config_num_dest 340 and comment = @cachename 341 342 343 /* 344 ** Check if there is an instance specific config for instance 345 ** @cur_instanceid 346 */ 347 select @stat = - 1 348 select @stat = status 349 from master.dbo.sysconfigures 350 where name = @cachename 351 and parent = @cfguserdefinedcache 352 and config = @cfguserdefinedcache 353 354 355 /* 356 ** Skip the instance if there exists an instance specific 357 ** configuration and the requested operation is global 358 */ 359 if (@instanceid is NULL) and (@stat != 1) and (@@instanceid != NULL) 360 begin 361 select @i = @i - 1 362 continue 363 end 364 365 /* If the pool doesn't exist it's an error. */ 366 if @temp_size = 0 367 begin 368 369 raiserror 18148, @dest_pool_sz_str, @cachename 370 return 1 371 end 372 select @i = @i - 1 373 end -- } 374 375 376 377 /* if parm2 is wash related then do the following */ 378 if patindex("%wash%", @parm2) != 0 379 begin -- { 380 if @@clustermode != 'shared disk cluster' 381 begin 382 select @wash_delta_size = @wash_or_apf_size - @temp_size 383 end 384 385 /* 386 ** In syscurconfigs, we obtain the wash size in Kilobytes. 387 ** However, we store the wash size, in sysconfigures, in 388 ** kilobytes/Buffer pool size. 389 ** Note: we are reusing the variable 'temp_size' here. 390 */ 391 select @temp_size = @wash_or_apf_size / @dest_buf_size 392 393 /* 394 ** If due to round off error, we get a wash size of 0, 395 ** even if it was not specified as the input, we will pass a 396 ** non-zero invalid value to the update. This will be 397 ** caught by the config manager and this update will 398 ** rolled back later.This can happen, if the wash size 399 ** specified is not zero, but gets rounded of to 400 ** zero. The wash size 0 is a special value. 401 */ 402 if @temp_size = 0 and @wash_or_apf_size != 0 403 select @temp_size = - 1 404 405 begin tran set_wash 406 407 update master.dbo.sysconfigures 408 set value3 = @temp_size 409 where config = @config_num_dest 410 and name = @cachename 411 412 413 /* 414 ** In SDC, we consider named cache creation as success even if 415 ** some instance allocated memory partially only. Due to this 416 ** pools may have different run sizes and hence wash sizes. 417 ** Hence, we can't determine a unique value to change at stored 418 ** procedure level. Hence, we pass total wash value for SDC. 419 ** 420 ** For SMP we need to pass 'delta' value of wash size. 421 */ 422 423 select @stat = config_admin(@cmdsetwashsize, 424 @dest_buf_size, 425 @wash_delta_size, 0, 426 @instancename, @cachename) 427 428 if @stat = 0 429 begin 430 print "Failed to set '%1!' to '%2!' for '%3!' pool in cache '%4!'. Check the error logs for more information.", 'wash size', 431 432 @wash_delta_size 433 434 , @dest_pool_sz_str, @cachename 435 rollback tran set_wash 436 return 1 437 end 438 439 commit tran set_wash 440 return 0 441 end -- } 442 443 /* if parm2 is apf related do the following */ 444 if patindex("%local async%", @parm2) != 0 445 begin 446 begin tran set_apf 447 448 update master.dbo.sysconfigures 449 set value4 = @wash_or_apf_size 450 where config = @config_num_dest 451 and name = @cachename 452 453 select @stat = config_admin(@cmdsetapfsize, @dest_buf_size, 454 @wash_or_apf_size, 0, @instancename, 455 @cachename) 456 457 if @stat = 0 458 begin 459 print "Failed to set '%1!' to '%2!' for '%3!' pool in cache '%4!'. Check the error logs for more information.", 'APF size', @wash_or_apf_size, @dest_pool_sz_str, @cachename 460 rollback tran set_apf 461 return 1 462 end 463 464 commit tran set_apf 465 return 0 466 end 467 end -- } 468 469 /* 470 ** If we make it here then the command must be of the form: 471 ** sp_poolconfig "foo", "100M", "16k", "instance" 472 ** We'll check against that syntax here. 473 */ 474 if @parm2 is NULL 475 begin 476 raiserror 18142 477 return 1 478 end 479 480 select @dest_buf_sz_str = @parm2 481 482 if @parm3 is NULL 483 begin 484 select @src_buf_sz_str = "1p" 485 select @instancename = NULL 486 select @instanceid = NULL 487 end 488 else 489 begin -- { 490 /* @parm3 may contain instance specific information. */ 491 if (charindex("instance ", @parm3) = 1) 492 begin 493 select @instancename = substring(@parm3, 10, 494 char_length(@parm3) - 9) 495 select @instanceid = instance_id(@instancename) 496 497 if @instanceid is NULL 498 begin 499 raiserror 19595, @instancename 500 return 1 501 end 502 503 select @src_buf_sz_str = "1p" 504 end 505 else 506 begin 507 if (charindex("instance ", @parm4) = 1) 508 begin 509 select @instancename = substring(@parm4, 10, 510 char_length(@parm4) - 9) 511 select @instanceid = instance_id(@instancename) 512 513 if @instanceid is NULL 514 begin 515 raiserror 19595, @instancename 516 return 1 517 end 518 end 519 else 520 begin 521 select @instancename = NULL 522 select @instanceid = NULL 523 end 524 525 select @src_buf_sz_str = @parm3 526 end 527 end -- } 528 529 /* Check to see if the cache exists. */ 530 select @stat = - 1 531 select @stat = status 532 from master.dbo.sysconfigures 533 where name = @cachename 534 and parent = @cfguserdefinedcache 535 and config = @cfguserdefinedcache 536 and status & 128 != 128 537 538 539 if @stat = - 1 540 begin 541 raiserror 18135, @cachename 542 return 1 543 end 544 545 /* Disallow configuring large I/O pool for in-memory storage cache. */ 546 if (@stat & 65536 = 65536) 547 begin 548 raiserror 19973, @cachename 549 return 1 550 end 551 552 /* Convert the destination pool size to units of kilobytes.*/ 553 exec @ret = sp_aux_getsize @dest_pool_sz_str, @requested_size output 554 if @ret = 0 555 begin 556 raiserror 18147, @dest_pool_sz_str, @min_buf_size, @max_buf_size 557 return 1 558 end 559 560 /* 561 ** First sanity check the destination pool size to make sure 562 ** it is at least 256 * pagesize big. 563 */ 564 if (@requested_size != 0 and @requested_size < @@min_poolsize) 565 begin 566 raiserror 18095, @@min_poolsize 567 return 1 568 end 569 570 /* 571 ** Translate the size in K of the destination and src pools to a 572 ** config value that we can use to look up the pool in sysconfigures. 573 */ 574 exec @ret = sp_aux_getsize @dest_buf_sz_str, @dest_buf_size output 575 if @ret = 0 576 begin 577 raiserror 18147, @dest_buf_sz_str, @min_buf_size, @max_buf_size 578 return 1 579 end 580 581 exec @ret = sp_aux_getsize @src_buf_sz_str, @src_buf_size output 582 if @ret = 0 583 begin 584 raiserror 18147, @src_buf_sz_str, @min_buf_size, @max_buf_size 585 return 1 586 end 587 588 /* The source and destination pools cannot be the same.*/ 589 if @dest_buf_size = @src_buf_size 590 begin 591 raiserror 18144, @src_buf_sz_str, @dest_buf_sz_str 592 return 1 593 end 594 595 /* 596 ** Sanity check the destination and source buffer sizes to make sure it's 597 ** within the legal range (i.e. powers of 2 between a logical page and 598 ** 8 logical pages inclusive. 599 */ 600 if (@dest_buf_size < @min_buf_size or @dest_buf_size > @max_buf_size) 601 begin 602 raiserror 18147, @dest_buf_sz_str, @min_buf_size, @max_buf_size 603 return 1 604 end 605 606 if (@src_buf_size < @min_buf_size or @src_buf_size > @max_buf_size) 607 begin 608 raiserror 18147, @src_buf_sz_str, @min_buf_size, @max_buf_size 609 return 1 610 end 611 612 select @temp_size = 2 613 while @temp_size < @dest_buf_size 614 select @temp_size = @temp_size * 2 615 if @temp_size != @dest_buf_size 616 begin 617 raiserror 18147, @dest_buf_sz_str, @min_buf_size, @max_buf_size 618 return 1 619 end 620 621 select @temp_size = 2 622 while @temp_size < @src_buf_size 623 select @temp_size = @temp_size * 2 624 if @temp_size != @src_buf_size 625 begin 626 raiserror 18147, @src_buf_sz_str, @min_buf_size, @max_buf_size 627 return 1 628 end 629 630 /* 631 ** Translate buffer sizes into config numbers to index into sysconfigures 632 ** and syscurconfigs with. 633 */ 634 select @config_num_src 635 = @cfgbuffer2kpoolgrp + (log10(@src_buf_size * 1024 / @@pagesize) / log10(2)) 636 select @config_num_dest 637 = @cfgbuffer2kpoolgrp + (log10(@dest_buf_size * 1024 / @@pagesize) / log10(2)) 638 639 /* Get the list of instances where the operations needs to be run. */ 640 641 642 643 644 /* 645 ** Find out how much memory is already in the destination pool. It could 646 ** turn out that this request is for making the pool smaller. In that case 647 ** turn around the arguments (i.e. destination pool becomes source and source 648 ** pool becomes destination) and give the extra memory to the source 649 ** pool. We need to consider the config value for configuring the pools as 650 ** different instances can different run values. 651 */ 652 select @delete_pool = 0 653 select @existing_pool_size = 0 654 select @existing_pool_size = value 655 from master.dbo.sysconfigures 656 where name = @cachename 657 and config = @config_num_dest 658 659 660 select @mem_to_move = @requested_size - @existing_pool_size 661 662 /* 663 ** If there is nothing to move just return. No need to work 664 ** without a good reason. 665 */ 666 if @mem_to_move = 0 667 begin 668 return 0 669 end 670 671 begin tran pool_config 672 673 /* 674 ** If the memory to move is positive, we're moving memory from the source 675 ** pool (parm3) to the destination pool (parm2). If it is negative then 676 ** we're moving memory from the destination pool (parm2) to the source 677 ** pool (parm3). Negative memory indicates that we're making a pool smaller. 678 */ 679 if @mem_to_move > 0 680 begin -- { 681 /* 682 ** Check to see if the source pool has enough memory in it to 683 ** satisfy the request. 684 */ 685 select @src_pool_size = 0 686 select @src_pool_size = value 687 from master.dbo.sysconfigures 688 where config = @config_num_src 689 and name = @cachename 690 691 692 /* 693 ** If @src_pool_size = 0 means it has default pool size. 694 ** We need to calculate the config size this pool by 695 ** reading other pool information and cache size 696 */ 697 if @src_pool_size = 0 698 begin 699 select @total_pool_size = sum(value) 700 from master.dbo.sysconfigures 701 where parent = @cfguserdefinedcache 702 and name = @cachename 703 704 and config != @cfguserdefinedcache 705 706 select @cache_size = value 707 from master.dbo.sysconfigures 708 where parent = @cfguserdefinedcache 709 and name = @cachename 710 711 and config = @cfguserdefinedcache 712 713 if @cache_size = 0 714 begin 715 select @cache_size = value 716 from master.dbo.syscurconfigs 717 where config = @cfguserdefinedcache 718 and comment = @cachename 719 end 720 721 select @src_pool_size = @cache_size - @total_pool_size 722 end 723 724 select @temp_size = @src_pool_size - @mem_to_move 725 if (@temp_size != 0 and @temp_size < (256 * @@maxpagesize / 1024)) 726 begin 727 /* Use wash_or_apf_str to covert mem_to_move to string. */ 728 select @wash_or_apf_str 729 = rtrim(convert(varchar(20), @mem_to_move)) + "Kb" 730 raiserror 18143, @src_buf_sz_str, @src_pool_size, 731 @wash_or_apf_str 732 733 /* 734 ** We are not using a rollback tran here as it will 735 ** complain that their is nothing to rollback. The 736 ** reason being we have not done any work in this 737 ** transaction so far. 738 */ 739 commit tran poolconfig 740 return 1 741 end 742 743 if exists (select * 744 from master.dbo.sysconfigures 745 where name = @cachename 746 and config = @config_num_dest 747 748 ) 749 begin 750 /* 751 ** @temp_str contains the string equivalent of @requested_size 752 ** in @dest_pool_sz_str format. 753 */ 754 exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output, 755 @requested_size, @temp_str output 756 757 update master.dbo.sysconfigures 758 set value = @requested_size, 759 value2 = @temp_str 760 where config = @config_num_dest 761 and name = @cachename 762 763 end 764 else 765 begin 766 select @stat = status 767 from master.dbo.sysconfigures 768 where name = @cachename 769 and config = @cfguserdefinedcache 770 771 772 select @msg = rtrim(convert(varchar(255), @dest_buf_size)) 773 + "K I/O Buffer Pool" 774 775 exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output, 776 @requested_size, @temp_str output 777 778 /* 779 ** Note the last 2 values for the insert below is '0'and (-1). 780 ** This corresponds to the wash size and default apf percentage 781 ** of the newly created buffer pool. 782 ** Newly created pools get a wash size of zero which 783 ** corresponds to default wash size. 784 */ 785 insert into master.dbo.sysconfigures 786 values (@config_num_dest, @requested_size, @msg, @stat, 787 @cachename, @cfguserdefinedcache, @temp_str, 0, (- 1) 788 789 ) 790 end 791 792 if @temp_size = 0 793 begin 794 /* Flag that we should delete the source buffer pool later. */ 795 select @delete_pool = 1 796 end 797 798 /* 799 ** Update the source pool size after transfer. 800 ** If the updated value would become negative then 801 ** set it to zero. @temp_str contains the string equivalent 802 ** of the value field in @src_pool_sz_str format. 803 */ 804 select @temp_size = value - @mem_to_move 805 from master.dbo.sysconfigures 806 where config = @config_num_src 807 and name = @cachename 808 809 810 if @temp_size < 0 811 begin 812 exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output, 813 0, @temp_str output 814 update master.dbo.sysconfigures 815 set value = 0, 816 value2 = @temp_str 817 where config = @config_num_src 818 and name = @cachename 819 820 end 821 else 822 begin 823 exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output, 824 @temp_size, @temp_str output 825 update master.dbo.sysconfigures 826 set value = @temp_size, 827 value2 = @temp_str 828 where config = @config_num_src 829 and name = @cachename 830 831 end 832 end -- } 833 else 834 begin -- { 835 /* 836 ** We're actually removing memory from the destination pool since 837 ** @mem_to_move is negative. If we've deleted this pool then delete 838 ** the entry from sysconfigures. Otherwise just subtract (by adding 839 ** -@mem_to_move from the value already in sysconfigures). 840 */ 841 /* 842 ** Check to see if we are shrinking default pool 843 ** as we don't allow this 844 */ 845 if (@dest_buf_size = @min_buf_size) 846 begin 847 /* 848 ** We are not using a rollback tran here as it will 849 ** complain that their is nothing to rollback. The 850 ** reason being we have not done any work in this 851 ** transaction so far. 852 */ 853 print "You cannot make the size of the default pool of cache '%1!' smaller.", @cachename 854 commit tran poolconfig 855 return 1 856 end 857 858 /* Check to see whether we have enough space in the dest pool */ 859 select @dest_pool_size = 0 860 select @dest_pool_size = value 861 from master.dbo.sysconfigures 862 where config = @config_num_dest 863 and name = @cachename 864 865 866 select @temp_size = @dest_pool_size + @mem_to_move 867 if (@temp_size != 0 and @temp_size < (256 * @@maxpagesize / 1024)) 868 begin 869 /* Use wash_or_apf_str to covert mem_to_move to string. */ 870 select @wash_or_apf_str 871 = rtrim(convert(varchar(20), @mem_to_move)) + "Kb" 872 raiserror 18143, @dest_buf_sz_str, @dest_pool_size, 873 @wash_or_apf_str 874 875 /* 876 ** We are not using a rollback tran here as it will 877 ** complain that their is nothing to rollback. The 878 ** reason being we have not done any work in this 879 ** transaction so far. 880 */ 881 commit tran poolconfig 882 return 1 883 end 884 885 if exists (select * 886 from master.dbo.sysconfigures 887 where name = @cachename 888 and config = @config_num_src 889 890 ) 891 begin -- { 892 /* 893 ** @temp_str contains the string equivalent of value field 894 ** in @src_pool_sz_str format. 895 */ 896 select @temp_value = value 897 from master.dbo.sysconfigures 898 where config = @config_num_src 899 and name = @cachename 900 901 902 /* 903 ** If we are updating the default pool and 904 ** the size of the default pool is zero then don't add 905 ** @mem_to_move to 'value'. 906 */ 907 if (@config_num_src != 908 (@cfgbuffer2kpoolgrp 909 + (log10(@@maxpagesize / @@pagesize) / log10(2)))) 910 begin 911 select @temp_value = @temp_value + abs(@mem_to_move) 912 end 913 else 914 begin 915 if (@temp_value != 0) 916 begin 917 select @temp_value = @temp_value + abs(@mem_to_move) 918 end 919 end 920 921 exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output, 922 @temp_value, @temp_str output 923 update master.dbo.sysconfigures 924 set value = @temp_value, 925 value2 = @temp_str 926 where config = @config_num_src 927 and name = @cachename 928 929 930 end -- } 931 else 932 begin -- { 933 select @stat = status 934 from master.dbo.sysconfigures 935 where name = @cachename 936 and config = @cfguserdefinedcache 937 938 939 select @msg = rtrim(convert(varchar(255), @src_buf_size)) 940 + "K I/O Buffer Pool" 941 select @temp_value = abs(@mem_to_move) 942 exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output, 943 @temp_value, @temp_str output 944 /* 945 ** Note the last 2 values for the insert below is '0'and (-1). 946 ** This corresponds to the wash size and default apf percentage 947 ** of the newly created buffer pool. 948 ** Newly created pools get a wash size of zero which 949 ** corresponds to default wash size. 950 */ 951 insert into master.dbo.sysconfigures 952 values (@config_num_src, @temp_value, @msg, @stat, @cachename, 953 @cfguserdefinedcache, @temp_str, 0, (- 1) 954 955 ) 956 end -- } 957 958 if @temp_size = 0 959 begin 960 /* 961 ** Flag that we should delete the destination buffer pool 962 ** later. 963 */ 964 select @delete_pool = 2 965 end 966 967 /* 968 ** Update the destination pool size after transfer. 969 ** @temp_str contains the string equivalent 970 ** of the value field @dest_pool_sz_str format. 971 */ 972 exec sp_aux_getsize @dest_pool_sz_str, @temp_pool_size output, 973 @requested_size, @temp_str output 974 update master.dbo.sysconfigures 975 set value = @requested_size, 976 value2 = @temp_str 977 where config = @config_num_dest 978 and name = @cachename 979 980 981 end -- } 982 983 984 985 986 select @instance_count = 1 987 988 989 990 select @failure_count = 0 991 992 993 994 select @i = @instance_count 995 while (@i > 0) 996 begin -- { 997 998 999 1000 /* 1001 ** We need to calculate the amount of memory to be moved 1002 ** separately for each instance and use it for poolconfig 1003 ** The reason for this is, there is a possibility of having 1004 ** different run values at different instances. 1005 */ 1006 1007 select @curr_pool_size = 0 1008 select @curr_pool_size = value 1009 from master.dbo.syscurconfigs 1010 where comment = @cachename 1011 and config = @config_num_dest 1012 1013 select @inst_mem_to_move = @requested_size - @curr_pool_size 1014 1015 /* 1016 ** The amount of memory to be moved may be negative some instance which 1017 ** means not enough memory 1018 */ 1019 if (@inst_mem_to_move = 0) 1020 begin 1021 select @i = @i - 1 1022 continue 1023 end 1024 1025 1026 select @cur_instancename = NULL 1027 1028 1029 1030 begin 1031 select @stat = config_admin(@cmdpoolconfig, @src_buf_size, 1032 @inst_mem_to_move, @dest_buf_size, 1033 @cur_instancename, @cachename) 1034 end 1035 1036 if @stat = 0 1037 begin 1038 select @failure_count = @failure_count + 1 1039 end 1040 1041 select @i = @i - 1 1042 end -- } 1043 1044 1045 1046 1047 /* 1048 ** We rollback the transanction only if the operation is failed 1049 ** at all nodes. 1050 */ 1051 if @failure_count = @instance_count 1052 begin 1053 print "Pool configuration for cache '%1!' not changed. If memory distributed between pools does not sum to 100%, move all memory in the cache to the default pool and retry your command.", @cachename 1054 rollback tran pool_config 1055 return 1 1056 end 1057 1058 1059 /* 1060 ** We need to check the result size at each instance and check 1061 ** whether we satisfied the requirement at all nodes before we 1062 ** delete a pool entry if needed. 1063 */ 1064 select @result_size = value 1065 from master.dbo.sysconfigures 1066 where config = @config_num_dest 1067 and name = @cachename 1068 1069 1070 /* 1071 ** ASE cannot guarentee that all of the requested buffers can be 1072 ** moved from one pool to another. Determine if the correct number of 1073 ** buffers were moved. 1074 ** 1075 ** When moving memory, ASE cannot move fractions of MASSes. Therefore 1076 ** the actual amount of memory moved may be rounded down to a multiple 1077 ** of the greater of the source and destination MASS size. Take this 1078 ** into account when determining if the request has been fulfilled. 1079 ** For example, if a request is made to move 28K from the 2K to the 8K 1080 ** pool, then the server will only attempt to move 24K, since half a 1081 ** MASS (the remaining 4K) has no meaning to the 8K pool. 1082 ** 1083 ** NOTE: This fact creates a dependency between the server's behaviour and 1084 ** the behaviour of sp_do_poolconfig, and care should be taken in future not 1085 ** to break this. These sorts of dependencies will continue to exist until 1086 ** such time as config_admin() is modified to return good status 1087 ** information. The stored procedure needs to know whether the memory move 1088 ** succeeded or not, and should not be left to duplicate the server's 1089 ** behaviour in order to work this out. 1090 ** 1091 ** Calculate this rounded down value and save it in @mem_moved. 1092 */ 1093 if @src_buf_size > @dest_buf_size 1094 begin 1095 select @temp_value = @mem_to_move / @src_buf_size 1096 select @mem_moved = @temp_value * @src_buf_size 1097 end 1098 else 1099 begin 1100 select @temp_value = @mem_to_move / @dest_buf_size 1101 select @mem_moved = @temp_value * @dest_buf_size 1102 end 1103 1104 /* 1105 ** If the requisite number of buffers were moved successfully between 1106 ** pools, remove corresponding entries from sysconfigures if this was a 1107 ** request to delete a pool. 1108 */ 1109 if @result_size = (@mem_moved + @existing_pool_size) 1110 begin -- { 1111 if @delete_pool = 1 1112 begin 1113 delete from master.dbo.sysconfigures 1114 where config = @config_num_src 1115 and parent = @cfguserdefinedcache 1116 and name = @cachename 1117 1118 1119 select @stat = config_admin(@cmdbindingcheck, 5, 1120 @src_buf_size * 1024, 0, @instancename, 1121 @cachename) 1122 1123 if @stat = 0 1124 begin 1125 select "Warning: Failed to verify whether log I/O size 1126 was set for the deleted pool." 1127 end 1128 end 1129 if @delete_pool = 2 1130 begin 1131 delete from master.dbo.sysconfigures 1132 where config = @config_num_dest 1133 and parent = @cfguserdefinedcache 1134 and name = @cachename 1135 1136 1137 select @stat = config_admin(@cmdbindingcheck, 5, 1138 @dest_buf_size * 1024, 0, @instancename, 1139 @cachename) 1140 if @stat = 0 1141 begin 1142 select "Warning: Failed to verify whether log I/O size 1143 was set for the deleted pool." 1144 end 1145 end 1146 end -- } 1147 else 1148 begin -- { 1149 /* 1150 ** Unable to move the requested number of buffers. If this was an 1151 ** attempt to delete a pool then generate a message indicating 1152 ** the failure to fully do do. The pool will have been marked 1153 ** as unavailable by the server in such cases. 1154 */ 1155 if (@delete_pool > 0) 1156 begin 1157 if (@delete_pool = 1) 1158 begin 1159 raiserror 18558, @src_buf_sz_str, @cachename 1160 end 1161 else 1162 begin 1163 raiserror 18558, @dest_buf_sz_str, @cachename 1164 end 1165 1166 raiserror 18559 1167 end 1168 else 1169 begin -- { 1170 /* 1171 ** Unable to move the correct number of buffers, but there 1172 ** was no attempt to delete either source or destination 1173 ** pool. 1174 */ 1175 select @res_sz_str = 1176 rtrim(convert(varchar(30), abs(@mem_moved))) 1177 select @req_sz_str = 1178 rtrim(convert(varchar(30), abs(@mem_to_move))) 1179 1180 if (@mem_to_move > 0) 1181 begin 1182 raiserror 18560, @req_sz_str, @src_buf_sz_str, 1183 @dest_buf_sz_str, @cachename, @res_sz_str 1184 end 1185 else 1186 begin 1187 raiserror 18560, @req_sz_str, @dest_buf_sz_str, 1188 @src_buf_sz_str, @cachename, @res_sz_str 1189 end 1190 1191 /* 1192 ** If the destination pool has been left with a size of 1193 ** less than 256 * (logical pagesize), then the server 1194 ** will have marked this pool as unuseable. 1195 ** Print a warning in such cases. 1196 */ 1197 if (@result_size != 0 and @result_size < @@min_poolsize) 1198 begin 1199 raiserror 18339, @dest_buf_sz_str, @@min_poolsize 1200 end 1201 1202 /* 1203 ** If the source pool has been left with a size of 1204 ** less than 256 * (logical pagesize), then the server 1205 ** will have marked this pool as unuseable. Print a 1206 ** warning in such cases. 1207 */ 1208 select @result_size = value 1209 from master.dbo.sysconfigures 1210 where config = @config_num_src 1211 and name = @cachename 1212 1213 1214 if (@result_size != 0 and @result_size < @@min_poolsize) 1215 begin 1216 raiserror 18339, @src_buf_sz_str, @@min_poolsize 1217 end 1218 1219 /* 1220 ** Print a general message as to how to correctly resize 1221 ** the pools. 1222 */ 1223 raiserror 18562 1224 end -- } 1225 end -- } 1226 1227 /* 1228 ** Update the wash size for source and destination buffer pools if required. 1229 ** We do this at the end because, here the actual buffer movement 1230 ** has already been done and we will have the new wash size available 1231 ** for the affected buffer pools. 1232 ** 1233 ** NOTE: In syscurconfigs, we obtain the wash size in Kilobytes. 1234 ** However, we store the wash size, in sysconfigures, in 1235 ** kilobytes/Buffer pool size. 1236 */ 1237 1238 /* 1239 ** There are two system tables which has all the config information. 1240 ** syscurconfigs maintains current running configuration and sysconfigures 1241 ** contains the configuration user has asked through config file and 1242 ** using config releated commands. The values in these two system catalogs 1243 ** may be different because server might calculate some values for example 1244 ** if wash size is DEFAULT (i.e value3=0 in sysconfigures) server will find 1245 ** out what is the optimal wash size and set that in syscurconfigs where as 1246 ** sysconfigures will have 0 only. This is useful because when memory is 1247 ** moved between pools and if user give wash size as DEFAULT server adjusts 1248 ** to optimal value otherwise wash size might become more than the pool 1249 ** hence memory may not be allowed to move. 1250 */ 1251 1252 /* For source pool, get the current wash size from sysconfigures. */ 1253 select @temp_value = 0 1254 select @temp_value = value3 1255 from master.dbo.sysconfigures 1256 where config = @config_num_src 1257 and name = @cachename 1258 1259 1260 /* 1261 ** At this point user has created or modified a pool. We should not update the 1262 ** wash size here because it was not specified as a parameter. 1263 ** We should leave wash size as 0 which indicates a default value. 1264 ** @temp_value will not be zero if user has explicitly changed the wash size 1265 ** at some point of the time, in such cases we should go update the 1266 ** value to that of running value because server might give a diffrent wash 1267 ** size than we asked for. 1268 */ 1269 if (@temp_value != 0) 1270 begin 1271 select @wash_or_apf_size = memory_used 1272 from master.dbo.syscurconfigs 1273 where config = @config_num_src 1274 and comment = @cachename 1275 1276 1277 update master.dbo.sysconfigures 1278 set value3 = @wash_or_apf_size / @src_buf_size 1279 where config = @config_num_src 1280 and name = @cachename 1281 1282 end 1283 1284 /* Now, do the aame for destination pool.*/ 1285 select @temp_value = 0 1286 select @temp_value = value3 1287 from master.dbo.sysconfigures 1288 where config = @config_num_dest 1289 and name = @cachename 1290 1291 1292 if (@temp_value != 0) 1293 begin 1294 select @wash_or_apf_size = memory_used 1295 from master.dbo.syscurconfigs 1296 where config = @config_num_dest 1297 and comment = @cachename 1298 1299 update master.dbo.sysconfigures 1300 set value3 = @wash_or_apf_size / @dest_buf_size 1301 where config = @config_num_dest 1302 and name = @cachename 1303 1304 end 1305 1306 /* 1307 ** Write the changes to the config file. We do not want to do this when called 1308 ** by recovery during runtime buffer tuning because in case of private install, 1309 ** when the cluster is started, the first instance writing this will prevent 1310 ** the rest of the instance from joining the cluster in first attempt. 1311 */ 1312 if (@is_in_recovery not in ("TRUE", "true")) 1313 begin 1314 /* 1315 ** For SMP server the instanceid is set to NULL. In SMP we only have one 1316 ** configuration file and hence a special where clause of instanceid is 1317 ** not required. 1318 */ 1319 if (@@instanceid = NULL) 1320 begin 1321 select @config_file_name = value2 1322 from master.dbo.syscurconfigs 1323 where config = @cfgcfgfilename 1324 end 1325 else 1326 begin 1327 select @config_file_name = value2 1328 from master.dbo.syscurconfigs where 1329 1330 config = @cfgcfgfilename 1331 end 1332 1333 select @stat = - 1 1334 select @stat = config_admin(@cmdupdateconfigfile, 0, 0, 0, 1335 @instancename, @config_file_name) 1336 1337 if @stat = 0 1338 begin 1339 print "Failed to update the config file. Changes to the pool configuration for cache '%1!' cannot be undone.", @cachename 1340 rollback tran pool_config 1341 return 1 1342 end 1343 end 1344 commit tran pool_config 1345 1346 return 0 1347
exec sp_procxmode 'sp_do_poolconfig', 'AnyMode' go Grant Execute on sp_do_poolconfig to public go
RESULT SETS | |
sp_do_poolconfig_rset_002 | |
sp_do_poolconfig_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..sysconfigures (1) ![]() calls proc sybsystemprocs..sp_aux_getsize ![]() calls proc sybsystemprocs..sp_cacheconfig ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..systypes (1) ![]() reads table tempdb..syscolumns (1) ![]() read_writes table tempdb..#colinfo_af (1) calls proc sybsystemprocs..sp_autoformat ![]() reads table master..syscolumns (1) ![]() reads table tempdb..systypes (1) ![]() calls proc sybsystemprocs..sp_namecrack ![]() read_writes table tempdb..#cache_info (1) read_writes table tempdb..#pool_detail (1) reads table master..syscurconfigs (1) ![]() read_writes table tempdb..#syscacheconfig (1) calls proc sybsystemprocs..sp_aux_getsize ![]() read_writes table master..sysconfigures (1) ![]() reads table master..syscurconfigs (1) ![]() CALLERS called by proc sybsystemprocs..sp_poolconfig ![]() |