Database | Proc | Application | Created | Links |
sybsystemprocs | sp_plan_dbccdb ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Messages for "sp_plan_dbccdb" 4 ** 5 ** 17421, "No such database -- run sp_helpdb to list databases." 6 ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." 7 ** 17260, "Can't run %1! from within a transaction." 8 ** 18454, "Recommended size for %1! database is %2!MB (data = %3!MB, log = %4!MB).", 9 ** 18456, "Recommended devices for %1! are:" 10 ** 18457, "No suitable devices for %1! in master..sysdevices." 11 ** 18458, "Recommended values for workspace size, cache size and worker process count are:" 12 ** 18459, "%1! database already exists with size %2!MB." 13 */ 14 15 create procedure sp_plan_dbccdb( 16 @dbname varchar(255) = null) 17 as 18 declare @dbid smallint, @dbid1 smallint, @dbid2 smallint 19 declare @dbcc_dbid int 20 declare @scanws_size bigint 21 declare @textws_size bigint 22 declare @cache_size bigint 23 declare @wt_count smallint 24 declare @devsize bigint 25 declare @name varchar(255) 26 declare @ldev varchar(255), @physdev varchar(255) 27 declare @dbcc_dbname varchar(255) 28 declare @dbccdb_size bigint 29 declare @log_size bigint, @data_size bigint 30 declare @size double precision 31 declare @msg varchar(1024) 32 declare @min_wssize bigint /* minimum size of workspace */ 33 declare @new_min_wssize bigint /* revised minimum size of workspace */ 34 declare @min_cachesize bigint /* minimum size of cache */ 35 declare @KB_per_page int /* number of KB per page */ 36 declare @divideby int 37 declare @displayunit char 38 declare @divideby_cm int 39 declare @displayunit_cm char 40 declare @dbo int 41 declare @dummy int 42 declare @scratchdb sysname 43 declare @qrystr varchar(312) 44 declare @adb_stat int 45 declare @comp_size bigint /* compression pool size */ 46 declare @comp_stat int /* compression status */ 47 declare @compdata_stat int /* data compression status */ 48 declare @complog_stat int /* log compression status */ 49 declare @status3 int /* status3 from sysdatabases */ 50 declare @adb_dbid int /* archive database id */ 51 52 /* 53 ** These variables are used for formatting the output 54 */ 55 declare @str1 char(30), @str2 char(8), @str3 char(8) 56 declare @str4 char(6), @str5 char(9), @str6 char(20) 57 declare @longstr varchar(255) 58 declare @tempcol varchar(30) 59 60 /* 61 ** Disallow this procedure within a transaction since it 62 ** creates temporary tables 63 */ 64 if @@trancount > 0 65 begin 66 /* 67 ** 17260, "Can't run %1! from within a transaction." 68 */ 69 raiserror 17260, "sp_plan_dbccdb" 70 return (1) 71 end 72 else 73 begin 74 set chained off 75 end 76 77 set transaction isolation level 1 78 set nocount on 79 80 /* Get the number of KB per page */ 81 select @KB_per_page = @@maxpagesize / 1024 82 83 /* Locate the archive database status */ 84 select @adb_stat = number from master.dbo.spt_values 85 where name = "archive database" and type = "D3" 86 87 select @compdata_stat = number from master.dbo.spt_values 88 where name = "compressed data" and type = "D3" 89 90 select @complog_stat = number from master.dbo.spt_values 91 where name = "compressed log" and type = "D3" 92 93 select @comp_stat = @compdata_stat | @complog_stat 94 95 /* 96 ** create tables for storing temporary results and declare 97 ** cursors on them 98 */ 99 create table #dbcc_dblist(dbid int, ldev_name varchar(30), status3 int) 100 101 declare cursor_dbcc_dblist cursor for 102 select dbid, status3 from #dbcc_dblist 103 104 /* 105 ** Insert dbid and devname for all valid normal databases in 106 ** #dbcc_dblist. The status3 is used to find whether an archive 107 ** database has compressed data or log. Set it to 0 for non-archive 108 ** databases. 109 */ 110 insert #dbcc_dblist(dbid, ldev_name, status3) 111 select distinct su.dbid, dev.name, 0 112 from master..sysdevices dev, master..sysusages su, 113 master..sysdatabases sd 114 where su.dbid = sd.dbid and dev.cntrltype = 0 and 115 su.vdevno = dev.vdevno and 116 sd.status3 & @adb_stat = 0 117 118 /* 119 ** Add dbid and devname for all the archive databases. 120 */ 121 declare cursor_adb cursor for 122 select dbid, status3 from master.dbo.sysdatabases 123 where (status3 & @adb_stat) != 0 124 125 open cursor_adb 126 fetch cursor_adb into @adb_dbid, @status3 127 while (@@sqlstatus = 0) 128 begin 129 /* 130 ** Read the scratch database name from sysattributes. 131 */ 132 select @scratchdb = convert(sysname, char_value) 133 from master.dbo.sysattributes 134 where class = 28 135 and object_type = "D" 136 and object = @adb_dbid 137 and attribute = 0 138 139 if (db_id(@scratchdb) is not null) 140 begin 141 /* 142 ** Add dump devices (location = 5) to the list 143 ** of used devices. 144 */ 145 select @qrystr = 'insert #dbcc_dblist(dbid, ldev_name, status3) ' + 146 'select distinct @adb_dbid, dev.name, @status3 ' + 147 'from master..sysdevices dev, ' + 148 @scratchdb + '.dbo.sysaltusages sa ' + 149 'where sa.dbid=@adb_dbid and ' + 150 'sa.location = 5 and ' + 151 'dev.status & 4 = 4 and ' + 152 'sa.vdevno = dev.vdevno ' 153 exec (@qrystr) 154 end 155 fetch cursor_adb into @adb_dbid, @status3 156 end 157 close cursor_adb 158 deallocate cursor cursor_adb 159 160 create table #dbcc_devlist(ldev_name varchar(255), devsize bigint, flag int) 161 162 /* 163 ** Get logical and physical names and size in KB of all physical devices 164 ** in the system that have atleast 1MB space and set the flag to 0 165 */ 166 declare @numpages bigint 167 declare @devname char(255) 168 declare c_dbcc_devlist cursor for select name, (high - low) + 1 169 from master..sysdevices 170 where cntrltype = 0 and ((high - low) + 1) >= 512 and status & 4 = 0 171 172 open c_dbcc_devlist 173 fetch c_dbcc_devlist into @devname, @numpages 174 while (@@sqlstatus = 0) 175 begin 176 177 insert into #dbcc_devlist(ldev_name, devsize, flag) values 178 (@devname, @numpages * @KB_per_page, 0) 179 180 fetch c_dbcc_devlist into @devname, @numpages 181 182 end 183 184 close c_dbcc_devlist 185 deallocate c_dbcc_devlist 186 187 /* 188 ** For each device in #dbcc_devlist, set the flag to 1 if the device 189 ** is being used by a database in #dbcc_dblist 190 */ 191 update #dbcc_devlist set flag = 1 192 from #dbcc_dblist dl, #dbcc_devlist dev 193 where dl.ldev_name = dev.ldev_name 194 195 /* Validate the given database */ 196 select @dbo = 0 197 if (@dbname is not null) 198 begin 199 select @dbid = dbid 200 from master..sysdatabases where name = @dbname 201 202 if (@dbid is null) 203 begin 204 /* 205 ** 17421, "No such database -- run sp_helpdb to list databases." 206 */ 207 raiserror 17421 208 return (1) 209 end 210 211 delete from #dbcc_dblist where dbid != @dbid 212 exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, @dbname 213 end 214 215 /* 216 ** Only the Database Owner (DBO) or 217 ** Accounts with SA role can execute it. 218 */ 219 if ((@dbo != 1) and charindex("sa_role", show_role()) < 1) 220 begin 221 /* 222 ** Call proc_role() to generate the appropriate 223 ** audit record. 224 */ 225 select @dummy = proc_role("sa_role") 226 if (@dbname is null) 227 /* 228 ** 18990, "You must be the System Administrator (SA) 229 ** to execute this procedure with a NULL database name 230 ** (dbname) parameter. 231 */ 232 raiserror 18990 233 else 234 /* 235 ** 17230, "You must be the System Administrator (SA) 236 ** or the Database Owner (dbo) to execute this 237 ** procedure." 238 */ 239 raiserror 17230 240 241 return (1) 242 end 243 244 /* 245 ** The minimum size of a workspace is 24 pages 246 */ 247 select @min_wssize = 24 248 249 /* 250 ** Create a temporary table for storing the configuration information 251 */ 252 create table #dbcc_config(dbid int, dbname varchar(30) null, 253 scanws_size bigint null, textws_size bigint null, 254 wt_count smallint null, cache_size bigint null, 255 comp_size bigint null) 256 declare cursor_dbcc_config cursor for 257 select dbname, scanws_size, textws_size, wt_count, 258 cache_size, comp_size 259 from #dbcc_config 260 261 /* 262 ** The data_size determines the amount of space set to store fault data 263 ** and statistics data (50KB + 500KB) for each checkstorage run. 264 ** We multiply this by 5 so that there enough space to store 5 instances 265 ** of results. If the dbcc database is dbccalt, we need space only for 266 ** storing results from dbccdb, otherwise we need space to store results 267 ** from all databases in master..sysdatabase except model, tempdb and 268 ** sybsystemprocs. log_size is set to 2MB (This is an arbitrary size.) 269 */ 270 if (@dbname = "dbccdb") 271 begin 272 select @dbcc_dbname = "dbccalt" 273 select @log_size = 2048 274 select @data_size = 3000 275 end 276 else 277 begin 278 select @dbcc_dbname = "dbccdb" 279 select @log_size = 2048 280 /* 281 ** Select all databases except model, tempdb and sybsystemprocs 282 */ 283 select @data_size = 3000 * (count(*) - 3) 284 from master..sysdatabases 285 end 286 287 /* 288 ** For each dbid in #dbcc_dblist table, calculate the configuration parameters 289 */ 290 open cursor_dbcc_dblist 291 fetch cursor_dbcc_dblist into @dbid, @status3 292 while (@@sqlstatus = 0) 293 begin 294 295 /* 296 ** Get the database name 297 */ 298 select @name = db_name(@dbid) 299 300 /* 301 ** check if there is already an entry for this database. 302 ** If so, continue with the next entry. The table #dbcc_dblist 303 ** can have multiple rows for a database if that databse resides 304 ** on multiple devices. We need to calculate the parameters 305 ** only once. 306 */ 307 if exists (select 1 from #dbcc_config where dbid = @dbid) 308 begin 309 fetch cursor_dbcc_dblist into @dbid, @status3 310 continue 311 end 312 313 /* 314 ** Get the number of devices used by this database 315 ** Scale the number of worker processes based on the device count 316 */ 317 select @wt_count = count(*) from #dbcc_dblist 318 where #dbcc_dblist.dbid = @dbid 319 320 if (@wt_count < 1) 321 select @wt_count = 1 322 else 323 if (@wt_count > 128) 324 select @wt_count = 128 325 326 /* 327 ** Minimum cache size is 640K * @wt_count 328 */ 329 select @min_cachesize = 640 * @wt_count 330 331 /* 332 ** The minimum workspace size depends on the number of worker 333 ** processes too. So adjust the value. 334 */ 335 select @new_min_wssize = ((@wt_count + 1) * 8) 336 if (@new_min_wssize < @min_wssize) 337 select @new_min_wssize = @min_wssize 338 339 /* 340 ** Get the scanws size for this database which is 1.2% of 341 ** database size. It is in units of pages. 342 ** If it's an archive database, the disk map has to be 343 ** found in sysaltusages in the scratch database. 344 */ 345 if exists (select * from master.dbo.sysdatabases 346 where dbid = @dbid 347 and (status3 & @adb_stat) != 0) 348 begin 349 /* 350 ** The original diskmap is stored in the sysaltusages catalog 351 ** in the scratch database with a location = 4. 352 ** Read the scratch database name from sysattributes first. 353 */ 354 select @scratchdb = convert(sysname, char_value) 355 from master.dbo.sysattributes 356 where class = 28 357 and object_type = "D" 358 and object = @dbid 359 and attribute = 0 360 361 select @qrystr = 362 'select @scanws_size=ceiling(sum(size) * 0.012) from ' + 363 @scratchdb + '.dbo.sysaltusages ' + 364 'where dbid=@dbid and location = 4' 365 exec (@qrystr) 366 367 /* 368 ** Set recommended compression pool size to 4MB per thread 369 ** if database is compressed. 370 */ 371 if ((@status3 & @comp_stat) != 0) 372 begin 373 select @comp_size = 4096 * @wt_count 374 end 375 else 376 begin 377 select @comp_size = 0 378 end 379 end 380 else 381 begin 382 select @scanws_size = ceiling(sum(size) * 0.012) 383 from master..sysusages 384 where dbid = @dbid 385 select @comp_size = 0 386 end 387 388 /* 389 ** set the size to the next multiple of 8 which is the unit 390 ** of allocation 391 */ 392 select @scanws_size = ((@scanws_size / 8) + 1) * 8 393 /* 394 ** Set the text workspace size to 25% of scan workspace and round 395 ** it off to a multiple of 8. 396 */ 397 select @textws_size = ceiling(@scanws_size * 0.25) 398 select @textws_size = ((@textws_size / 8) + 1) * 8 399 400 /* 401 ** The minimum size required for workspaces are @new_min_wssize pages. 402 ** Set the sizes to @new_min_wssize if the current value is less 403 ** than this. 404 */ 405 if (@scanws_size < @new_min_wssize) 406 begin 407 select @scanws_size = @new_min_wssize 408 end 409 if (@textws_size < @new_min_wssize) 410 begin 411 select @textws_size = @new_min_wssize 412 end 413 414 /* 415 ** For small databases (size < 20MB), increase the size of 416 ** scanws by 8 pages. This is because checkstorage uses one 417 ** full extent (8 pages) for page mapping. As a result 1.2% 418 ** of database size may not be enough for scanws if database is 419 ** small. 420 */ 421 if (@scanws_size <= 128) 422 select @scanws_size = @scanws_size + 8 423 /* 424 ** convert workspace sizes into KB 425 */ 426 select @scanws_size = @scanws_size * @KB_per_page 427 select @textws_size = @textws_size * @KB_per_page 428 429 /* 430 ** Set the cache size to 20% of total workspace size. 431 ** The cache size is actually the size of 16K(or 64K) buffer 432 ** pool. The actual cache size must be @cache_size + 512K 433 ** 434 ** NOTE: since scanws ~ Sum(max two db size) * 1.2% 435 ** textws ~ scanws/4 436 ** then these quantities are comparable in size. 437 ** Cache will be set to 20% of the sum, so it 438 ** will always be less magnitude than these 439 ** quantities. So later, we can safely use 440 ** a single scale factor, divideby, so scale the 441 ** display unit. If however, these units are allowed 442 ** in the future to become asymmetric (eg vastly 443 ** different in magnitude) then one should consider 444 ** using the approach in sp_dbcc_evaluatedb to 445 ** scale the quantities individually. 446 */ 447 select @cache_size = 448 ceiling((@scanws_size + @textws_size) * 0.2) 449 if (@cache_size < @min_cachesize) 450 begin 451 select @cache_size = @min_cachesize 452 end 453 454 /* 455 ** Insert the values calculated for this database into 456 ** #dbcc_config table. This table is used for report generation 457 */ 458 insert into #dbcc_config(dbid, dbname, scanws_size, textws_size, 459 wt_count, cache_size, comp_size) 460 values (@dbid, @name, @scanws_size, @textws_size, 461 @wt_count, @cache_size, @comp_size) 462 463 fetch cursor_dbcc_dblist into @dbid, @status3 464 end 465 466 close cursor_dbcc_dblist 467 468 print "" 469 470 /* 471 ** Calculate (in MB) the size of dbccdb database. Even though 472 ** checkstorage can be run concurrently on multiple databases, 473 ** it is highly unlikely that users will run checkstorage on 474 ** more than two databases concurrently. So determine the size of 475 ** dbccdb based on the two largest databases in the server. 476 */ 477 478 select @dbid1 = dbid from #dbcc_config 479 group by dbid having sum(scanws_size) = max(sum(scanws_size)) 480 481 select @dbid2 = dbid from #dbcc_config where dbid != @dbid1 482 group by dbid having sum(scanws_size) = max(sum(scanws_size)) 483 484 select @size = (sum(scanws_size) + sum(textws_size)) 485 from #dbcc_config 486 where dbid = @dbid1 or dbid = @dbid2 487 488 /* 489 ** calculate the total size for data and log (in MB). The workspace size 490 ** is increased by 20% to accommodate future expansion. 491 ** NOTE: ceiling needs a float type to work. Add decimal point to 492 ** achieve this. 493 */ 494 select @data_size = ceiling((@data_size + (@size * 1.2)) / 1024) 495 select @log_size = ceiling(@log_size / 1024.0) 496 select @dbccdb_size = @data_size + @log_size 497 498 /* 499 ** 18454, "Recommended size for %1! database is %2!MB (data = %3!MB, log = %4!MB).", 500 */ 501 exec sp_getmessage 18454, @msg output 502 print @msg, @dbcc_dbname, @dbccdb_size, @data_size, @log_size 503 504 print "" 505 506 /* 507 ** If @dbcc_dbname exists, report so. Otherwise recommend a device 508 ** for @dbcc_dbname 509 */ 510 select @dbcc_dbid = db_id(@dbcc_dbname) 511 if (@dbcc_dbid is not null) 512 begin 513 select @dbccdb_size = (sum(size) * @KB_per_page) / 1024 514 from master..sysusages 515 where dbid = @dbcc_dbid 516 /* 517 ** 18459, "%1! database already exists with size %2!MB" 518 */ 519 exec sp_getmessage 18459, @msg output 520 print @msg, @dbcc_dbname, @dbccdb_size 521 end 522 else 523 begin 524 525 /* 526 ** Delete all devices which are being used by databases. 527 ** This is indicated by flag > 0 528 */ 529 delete from #dbcc_devlist where flag != 0 530 531 /* 532 ** Get the total size of all free devices in MB. If the total 533 ** size is atleast @dbccdb_size, these devices can be used for 534 ** dbcc database. 535 */ 536 select @size = (sum(devsize) / 1024) from #dbcc_devlist 537 538 if (@size >= @dbccdb_size) 539 begin 540 /* 541 ** 18456, "Recommended devices for %1! are:" 542 */ 543 exec sp_getmessage 18456, @msg output 544 print @msg, @dbcc_dbname 545 print "" 546 select "Logical Device Name" = ldev_name, 547 "Device Size (KB)" = devsize 548 from #dbcc_devlist 549 end 550 else 551 begin 552 /* 553 ** 18457, "No suitable devices for %1! in master..sysdevices" 554 */ 555 exec sp_getmessage 18457, @msg output 556 print @msg, @dbcc_dbname 557 end 558 end 559 print "" 560 561 /* 562 ** Format and print the configuration parameters 563 */ 564 select @str1 = "dbname" 565 select @str2 = "scan ws" 566 select @str3 = "text ws" 567 select @str4 = "cache" 568 select @str5 = "comp mem" 569 select @str6 = "process count" 570 /* 571 ** 18458, "Recommended values for workspace size, cache size and worker process count are:" 572 */ 573 exec sp_getmessage 18458, @msg output 574 print @msg 575 print "" 576 print "%1! %2! %3! %4! %5! %6!", 577 @str1, @str2, @str3, @str4, @str5, @str6 578 579 open cursor_dbcc_config 580 fetch cursor_dbcc_config into @name, @scanws_size, @textws_size, @wt_count, 581 @cache_size, @comp_size 582 583 584 while (@@sqlstatus = 0) 585 begin 586 /* 587 ** sizes are in KB. Find out the appropriate unit to use for 588 ** displaying these values. 589 ** NOTE: problem if size gets larger than MAXINT. This 590 ** represents 2 TB. Change input parameter and sproc 591 ** sp_dbcc_scale_factor if this limit is reached. 592 */ 593 execute sp_dbcc_scale_factor @cache_size, 594 @scale_factor = @divideby output, 595 @scale_letter = @displayunit output 596 597 execute sp_dbcc_scale_factor @comp_size, 598 @scale_factor = @divideby_cm output, 599 @scale_letter = @displayunit_cm output 600 601 /* 602 ** Convert all values to varchar for formatting purpose 603 */ 604 select @str1 = @name 605 select @str2 = convert(varchar(8), ceiling(1.0 * @scanws_size / @divideby)) 606 + @displayunit 607 select @str3 = convert(varchar(8), ceiling(1.0 * @textws_size / @divideby)) 608 + @displayunit 609 select @str4 = convert(varchar(8), ceiling(1.0 * @cache_size / @divideby)) 610 + @displayunit 611 select @str5 = convert(varchar(9), ceiling(1.0 * @comp_size / @divideby_cm)) 612 + @displayunit_cm 613 select @str6 = convert(char(3), @wt_count) 614 print "%1! %2! %3! %4! %5! %6!", 615 @str1, @str2, @str3, @str4, @str5, @str6 616 fetch cursor_dbcc_config into @name, @scanws_size, 617 @textws_size, @wt_count, @cache_size, @comp_size 618 end 619 print "" 620 621 close cursor_dbcc_config 622 623 /* 624 ** Clean-up all temporary tables 625 */ 626 deallocate cursor cursor_dbcc_dblist 627 deallocate cursor cursor_dbcc_config 628 629 drop table #dbcc_dblist 630 drop table #dbcc_devlist 631 drop table #dbcc_config 632 633 return (0) 634 635
exec sp_procxmode 'sp_plan_dbccdb', 'AnyMode' go Grant Execute on sp_plan_dbccdb to public go
RESULT SETS | |
sp_plan_dbccdb_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table tempdb..#dbcc_devlist (1) reads table master..sysusages (1) ![]() reads table master..spt_values (1) ![]() reads table master..sysdatabases (1) ![]() read_writes table tempdb..#dbcc_config (1) reads table master..sysdevices (1) ![]() calls proc sybsystemprocs..sp_is_valid_user ![]() reads table master..sysloginroles (1) ![]() reads table master..sysdatabases (1) ![]() read_writes table tempdb..#t (1) reads table master..sysattributes (1) ![]() calls proc sybsystemprocs..sp_getmessage ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..sysmessages (1) ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() calls proc sybsystemprocs..sp_dbcc_scale_factor ![]() read_writes table tempdb..#dbcc_dblist (1) |