Database | Proc | Application | Created | Links |
sybsystemprocs | sp_estspace ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 /* 4 ** Messages for "sp_estspace" 5 ** 17461, "Object does not exist in this database." 6 ** 18054, "Table contains text/image type columns but length for these columns not specified in the argument list. 7 */ 8 9 create procedure sp_estspace 10 11 /* A procedure to estimate the disk space requirements of a table 12 ** and its associated indexes 13 ** Written by Malcolm Colton with assistance from Hal Spitz 14 */ 15 (@table_name varchar(511) = null, /* name of table to estimate */ 16 @no_of_rows float = 0, /* number of rows in the table */ 17 @fill_factor float = null, /* the fill factor */ 18 @cols_to_max varchar(2060) = null, 19 /* variable length columns for which 20 to use the maximum rather than 50% of 21 the maximum length. It can hold 22 at most 8 column names.*/ 23 @textbin_len float = null, /* len of all text & binary data/row */ 24 @iosec float = 30, 25 @pagesize varchar(30) = null) 26 as 27 28 declare @msg varchar(120) 29 30 if @@trancount = 0 31 begin 32 set chained off 33 end 34 35 set transaction isolation level 1 36 37 /* Give usage statement if @table_name is null */ 38 39 if @table_name is null or @no_of_rows <= 0 or @fill_factor < 0 or @fill_factor > 100 40 begin 41 print 'USAGE:' 42 print 'sp_estspace table_name, no_of_rows, fill_factor, cols_to_max, textbin_len, iosec' 43 print 'where ' 44 print ' table_name is the name of the table,' 45 print ' no_of_rows is the number of rows in the table (>0),' 46 print ' fill_factor is the index fill factor. Values range from' 47 print ' 0 to 100. (default = 0,in which case internal' 48 print ' fill factor will be used)' 49 print ' cols_to_max is a list of the variable length columns for which' 50 print ' to use the maximum length instead of the average' 51 print ' (default = null)' 52 print ' textbin_len is length of all the text and binary fields per' 53 print ' row ( default = 0).' 54 print ' iosec is the number of I/Os per second on this machine' 55 print ' (default = 30)' 56 print 'Examples: sp_estspace titles, 10000, 50, "title, notes", null, 25' 57 print ' sp_estspace titles, 50000' 58 print ' sp_estspace titles, 50000, null, null, null, 40' 59 return 60 end 61 62 declare @sum_fixed int, 63 @sum_var int, 64 @sum_avgvar int, 65 @table_id int, 66 @num_var int, 67 @data_pages float, 68 @sysstat smallint, 69 @temp float, 70 @temp1 float, 71 @index_id int, 72 @last_id int, 73 @i int, 74 @level_pages float, 75 @key varchar(255), 76 @usertype tinyint, 77 @type tinyint, 78 @level smallint, 79 @vartype smallint, 80 @more bit, 81 @next_level float, 82 @rows_per_page int, 83 @row_len int, 84 @length int, 85 @index_name varchar(255), 86 @page_size int, 87 @page_K int, 88 @index_type varchar(20), 89 @mrpg smallint, 90 @factor float, 91 @status2 int, 92 @sysstat2 int, 93 @table_type varchar(3), 94 @maxcols_in_key int, 95 @status int, 96 @unit_loc int /* The position of the unit in the 97 ** pagesize parameter string. 98 */ 99 100 select @sum_fixed = 0, 101 @sum_var = 0, 102 @sum_avgvar = 0, 103 @table_id = 0, 104 @num_var = 0, 105 @data_pages = 0, 106 @row_len = 0, 107 @sysstat = 0, 108 @sysstat2 = 0, 109 @maxcols_in_key = 31 /* max. columns allowed in an index */ 110 111 set nocount on 112 113 /* set the default fill_factor */ 114 if @fill_factor is null 115 select @fill_factor = 0 116 117 /* Make sure table exists */ 118 119 select @sysstat = sysstat, 120 @table_id = id, 121 @sysstat2 = sysstat2 122 from sysobjects where id = object_id(@table_name) 123 124 if @sysstat & 7 not in (1, 3) 125 begin 126 /* 17461, "Object does not exist in this database." */ 127 raiserror 17461 128 return (1) 129 end 130 131 /* Get the lock scheme for the table */ 132 if @sysstat & 15 in (1, 3) 133 begin 134 /* 135 ** the bits 0x2000, 0x4000 & 0x8000 represents any 136 ** explicit lock scheme bits that can be set, so 137 ** get them out ( 0x2000 + 0x4000 + 0x8000 = 57344) 138 */ 139 select @sysstat2 = (@sysstat2 & 57344) 140 141 if (@sysstat2 in (0, 8192, 16384, 32768)) 142 begin 143 if (@sysstat2 = 8192 or @sysstat2 = 0) 144 begin 145 /* Lock scheme is Allpages */ 146 select @table_type = "AP" 147 end 148 if (@sysstat2 = 16384 or @sysstat2 = 32768) 149 begin 150 /* Lock scheme is Data only */ 151 select @table_type = "DOL" 152 end 153 end 154 else 155 begin 156 /* 17579, Lock scheme Unknown or Corrupted */ 157 raiserror 17579 158 return (1) 159 end 160 end 161 162 /* If the user provided a pagesize, use it */ 163 if (@pagesize is not NULL) 164 begin 165 /* 166 ** Did the user provide a unit with the pagesize parameter? 167 ** The units accepted are kilobytes, anything else is a usage error. 168 */ 169 select @unit_loc = patindex("%[kK]%", @pagesize) 170 171 if (@unit_loc = 0) 172 begin 173 select @page_size = convert(int, @pagesize) 174 select @page_K = @page_size / 1024 175 end 176 else 177 begin 178 exec @status = sp_aux_getsize @pagesize, @page_K output 179 180 if (@status = 0) 181 begin 182 /* 183 ** Invalid syntax 184 ** 18940 "Error: Specified pagesize '%1!' is 185 ** invalid. Valid values are '2048', '4096' '8192', 186 ** '16384', or '2k', '4k', '8k', '16k'. Unit 187 ** specifiers can also be 'K'." 188 */ 189 raiserror 18940, @pagesize 190 return (1) 191 end 192 193 /* sp_aux_getsize returns value in K unit. */ 194 select @page_size = @page_K * 1024 195 196 end 197 198 if (@page_size != 2048 and @page_size != 4096 and 199 @page_size != 8192 and @page_size != 16384) 200 begin 201 202 /* 203 ** Invalid syntax 204 ** 18940 "Error: Specified pagesize '%1!' is 205 ** invalid. Valid values are '2048', '4096' '8192', 206 ** '16384', or '2k', '4k', '8k', '16k'. Unit 207 ** specifiers can also be 'K'." 208 */ 209 raiserror 18940, @pagesize 210 return (1) 211 end 212 213 end 214 else 215 begin 216 217 /* Get machine page size if user did not specify one */ 218 select @page_size = low 219 from master.dbo.spt_values 220 where type = 'E' 221 and number = 1 222 select @page_K = @page_size / 1024 223 end 224 225 226 if @table_type = "AP" 227 select @page_size = @page_size - 32 228 else 229 select @page_size = @page_size - 44 230 231 if @fill_factor != 0 232 select @fill_factor = @fill_factor / 100.0 233 234 /* Get the max_rows_per_page value */ 235 select @mrpg = maxrowsperpage 236 from sysindexes 237 where id = @table_id and indid in (0, 1) 238 239 /* Allpages tables are limited to 255 rows per page */ 240 if @table_type = 'AP' and (@mrpg = 0 or @mrpg > 255) 241 select @mrpg = 255 242 243 /* max_rows_per_page option is not valid for DOL tables */ 244 if (@table_type = 'DOL') 245 begin 246 select @mrpg = 0 247 end 248 249 /* Create tables for results */ 250 251 create table #results 252 (name varchar(255), 253 type varchar(12), 254 idx_level smallint, 255 pages float, 256 Kbytes float) 257 258 create table #times 259 (name varchar(255), 260 type varchar(12) null, 261 tot_pages float null, 262 time_mins float null) 263 264 265 /* Create table of column info for the table to be estimated */ 266 267 select length, type, name 268 into #col_table 269 from syscolumns 270 where id = @table_id 271 272 273 /* check if text/image lenth specified if these fields exist in the table */ 274 if (@textbin_len is null) 275 begin 276 if exists (select name from syscolumns where id = @table_id and 277 (type = 35 or type = 34)) 278 279 begin 280 /* 18054, "Table contains text/image type columns but length for 281 ** these columns not specified in the argument list. 282 */ 283 raiserror 18054 284 return (1) 285 end 286 end 287 288 289 /* Look up the important values from this table */ 290 291 /* get fixed length columns (those with types other than 39, 37 (varbinary)) */ 292 select @sum_fixed = isnull(sum(length), 0) 293 from #col_table 294 where type not in (37, 39) 295 296 /* get var. length columns that have to use the entire size */ 297 select @num_var = isnull(count(*), 0), @sum_var = isnull(sum(length), 0) 298 from #col_table 299 where type in (37, 39) 300 and charindex(name, @cols_to_max) > 0 301 302 /* get var. length columns for which average size is used */ 303 select @num_var = @num_var + isnull(count(*), 0), 304 @sum_avgvar = isnull(sum(length / 2), 0) 305 from #col_table 306 where type in (37, 39) 307 and charindex(name, @cols_to_max) = 0 308 309 /* Calculate the data page requirements */ 310 set arithabort numeric_truncation off 311 312 /* variables used for DOL tables */ 313 declare @data_fixed_ovhd smallint, 314 @data_var_ovhd smallint, 315 @data_len int, /* length of the data portion */ 316 @dol_ind_fixed_ovhd smallint, 317 @dol_ind_var_ovhd smallint, 318 @dol_ind_data_len int, /* length of row in index*/ 319 @next_level_rows float 320 321 select @data_len = @sum_fixed + @sum_var + @sum_avgvar 322 /* If the table is DOL */ 323 if (@table_type = "DOL") 324 begin 325 /* 326 ** Overhead if there are no variable length columns: 327 ** 2 bytes each for row number, status, varcount, row offset table 328 ** entry. 329 ** 330 ** Overhead if variable length columns are present: 331 ** 2 bytes each for row number, status, varcount, row offset table 332 ** entry and rowlength. 333 ** offset table size is 2 bytes per varcol, no adjust table. 334 */ 335 select @data_fixed_ovhd = 8.0, 336 @data_var_ovhd = 10.0 + (2 * @num_var) 337 end 338 else 339 begin 340 /* 341 ** Overhead if there are no variable length columns: 342 ** 1 byte each for varcount and row number and 2 bytes for row 343 ** offset table entry. 344 ** 345 ** Overhead if variable length columns are present: 346 ** 1 byte each for varcount and row number, and 2 bytes for 347 ** row offset table entry and rowlength. 348 ** offset table size is 1 byte per varcol + 1 byte for end of 349 ** data offset. Adjust table size is rowlength/256 + 1. 350 */ 351 select @data_fixed_ovhd = 4.0, 352 @data_var_ovhd = 6.0 + (@num_var + 1) + (@data_len / 256 + 1) 353 end 354 if @num_var = 0 355 select @row_len = @data_fixed_ovhd + @data_len 356 else 357 select @row_len = @data_var_ovhd + @data_len 358 /* 359 ** For tables marked for 'incremental transfer', add additional 8 bytes 360 ** for the per row timestamp used for the transfer. 361 */ 362 declare @xfer_stat int 363 select @xfer_stat = number 364 from master.dbo.spt_values 365 where type = 'O3' and name = "incremental transfer on" 366 367 if exists (select 1 from sysobjects 368 where id = @table_id and (sysstat3 & @xfer_stat = @xfer_stat)) 369 begin 370 select @row_len = @row_len + 8 371 end 372 373 /* 374 ** For DOL datapages, the min. row length should be 10 (2bytes each for row 375 ** number and status + 6 bytes of space for (possible) forwarding address) 376 ** In the above computation of rowlength we have included the row offset 377 ** table entry of 2 bytes, we need 10 bytes minimum length excluding these 378 ** 2 bytes. 379 */ 380 if @table_type = "DOL" 381 begin 382 if (@row_len - 2) < 10 383 select @row_len = 12 384 end 385 386 /* Allow for fill-factor if set to other than zero */ 387 388 if @fill_factor = 0 389 begin 390 select @temp = convert(float, @no_of_rows) * 391 (convert(float, @row_len) / convert(float, @page_size)) 392 end 393 else 394 begin 395 select @temp = convert(float, @no_of_rows) / 396 (convert(float, @page_size) * convert(float, @fill_factor)) 397 select @temp = convert(float, @row_len) * @temp 398 end 399 400 /* 401 ** If there is maxrowsperpage and it creates less number of pages than 402 ** without it, then use it. 403 */ 404 if @mrpg != 0 405 begin 406 select @temp1 = convert(float, @no_of_rows) / 407 convert(float, @mrpg) 408 if @temp < @temp1 409 select @temp = @temp1 410 end 411 412 /* Now add in allocation pages */ 413 select @temp = @temp + (@temp / 256.0) 414 select @data_pages = @temp + 1.0 415 if @data_pages < 8.0 416 select @data_pages = 8.0 417 418 insert #results values 419 (@table_name, 'data', 0, @data_pages, @data_pages * @page_K) 420 421 /* calculate the pages for text and length */ 422 423 /* text/bin pages write multiples of 450 bytes per page */ 424 if (@textbin_len is not null) 425 begin 426 select @temp = ceiling(@textbin_len / (floor(@page_size / 450.0) * 450.0)) 427 select @temp = @temp * @no_of_rows 428 insert #results values 429 (@table_name, 'text/image', 0, @temp, @temp * @page_K) 430 end 431 432 /* See if the table has any indexes */ 433 434 select @index_id = min(indid) 435 from sysindexes 436 where id = @table_id 437 and indid > 0 and indid < 255 438 439 if @index_id is null /* We've finished if there are no indexes */ 440 begin 441 select @msg = @table_name + ' has no indexes' 442 print @msg 443 444 select name, type, idx_level, 'Pages' = str(pages, 12, 0), 'Kbytes' = str(Kbytes, 12, 0) 445 into #results_1 446 from #results 447 448 exec sp_autoformat @fulltabname = #results_1 449 450 select Total_Mbytes = str(sum(Kbytes) / 1024.0, 17, 2) 451 from #results 452 453 drop table #results_1 454 drop table #results 455 return 456 end 457 458 select @sum_fixed = 0, 459 @sum_var = 0, 460 @num_var = 0, 461 @temp = 0 462 463 /* For each index, calculate the important variables 464 ** use them to calculate the index size, and print it */ 465 466 while @index_id is not null 467 begin 468 select @index_name = name, @mrpg = maxrowsperpage, 469 @status2 = status2 470 from sysindexes 471 where id = @table_id 472 and indid = @index_id 473 if @index_id = 1 474 begin 475 select @index_type = 'clustered' 476 end 477 else 478 if @index_id > 1 479 begin 480 if (@status2 & 512 = 512) 481 begin 482 select @index_type = 'clustered' 483 end 484 else 485 begin 486 select @index_type = 'nonclustered' 487 end 488 end 489 490 select @num_var = 0, 491 @sum_var = 0, 492 @sum_fixed = 0 493 494 if (@table_type = "DOL") 495 begin 496 select @mrpg = 0 497 end 498 499 select @i = 1 500 501 /* Look up each of the key fields for the index */ 502 503 while @i <= @maxcols_in_key 504 begin 505 select @key = index_col(@table_name, @index_id, @i) 506 507 if @key is null 508 break 509 else /* Process one key field */ 510 begin 511 select @type = type, @length = length, @vartype = offset 512 from syscolumns 513 where id = @table_id 514 and name = @key 515 516 if @vartype < 0 517 select @num_var = @num_var + 1 518 else 519 select @sum_fixed = @sum_fixed + @length 520 521 /* varchar, varbinary: check if in @cols_to_max */ 522 if (@type = 37 or @type = 39) 523 begin 524 if charindex(@key, @cols_to_max) = 0 525 select @sum_var = @sum_var + (@length / 2) 526 else 527 select @sum_var = @sum_var + @length 528 end 529 end 530 531 select @i = @i + 1 /* Get next key field in this index */ 532 end 533 534 /* Calculate the space used by this index */ 535 536 /* 537 ** For indexes of DOL TABLES, we assume the following: 538 ** No suffix compression is done, Assumes that all the 539 ** keys in the index are unique and there is no difference 540 ** between clustered and nonclustered index 541 */ 542 select @dol_ind_fixed_ovhd = 9 543 select @dol_ind_var_ovhd = 9 + (2 * @num_var) 544 select @dol_ind_data_len = @sum_fixed + @sum_var 545 if @table_type = "DOL" 546 begin 547 if @num_var = 0 548 select @row_len = @dol_ind_fixed_ovhd + @dol_ind_data_len 549 else 550 select @row_len = @dol_ind_var_ovhd + @dol_ind_data_len 551 end 552 else /* AP locked object */ 553 begin 554 if @index_id = 1 555 begin 556 if @num_var = 0 557 select @row_len = 5 + @sum_fixed 558 else 559 select @row_len = @sum_fixed + @sum_var + @num_var + 8 560 end 561 else 562 begin 563 if @num_var = 0 564 select @row_len = 7 + @sum_fixed 565 else 566 select @row_len = @sum_fixed + @sum_var + @num_var + 11 567 end 568 end 569 570 /* Allow for fill-factor if set to other than zero */ 571 572 if @fill_factor = 0 or @index_id = 1 573 begin 574 select @rows_per_page = @page_size / @row_len - 2 575 end 576 else 577 begin 578 select @rows_per_page = @page_size / @row_len * @fill_factor 579 end 580 581 /* at lease one row per page */ 582 if @rows_per_page < 1 583 select @rows_per_page = 1 584 585 /* Prestage index building information */ 586 select @more = 1, @level = 0 587 588 /* For DOL indexes, estimate the #levels and #pages */ 589 if @table_type = "DOL" 590 begin 591 while @more = 1 592 begin 593 if @level = 0 594 begin 595 if @mrpg != 0 596 select @rows_per_page = @mrpg 597 select @level_pages = @no_of_rows / 598 convert(float, @rows_per_page) 599 select @next_level_rows = @level_pages 600 end 601 else if @level > 255 602 begin 603 /* This index is too big to build */ 604 raiserror 19414, @index_name 605 goto kill_proc 606 end 607 else 608 begin 609 select @level_pages = @next_level_rows / 610 convert(float, @rows_per_page) 611 select @next_level_rows = @level_pages 612 end 613 614 /* Account for allocation/oam pages */ 615 select @level_pages = @level_pages + 616 (@level_pages / 256.0) + 1.0 617 /* Insert the row into the results table */ 618 insert #results values 619 (@index_name, @index_type, @level, 620 @level_pages, 621 floor(@level_pages) * @page_K) 622 select @level = @level + 1 623 if @next_level_rows <= @rows_per_page 624 select @more = 0 625 end 626 /* Account for the root page */ 627 insert #results values 628 (@index_name, @index_type, @level, 1, @page_K) 629 end 630 else 631 begin /* AP locked object */ 632 if @rows_per_page > 256 633 select @rows_per_page = 256 634 /* 635 ** For clustered indexes, the first level of index is 636 ** based on the number of data pages. 637 ** For nonclustered, it is the number of data rows 638 */ 639 640 if @index_id = 1 641 select @next_level = @data_pages 642 else 643 select @next_level = @no_of_rows 644 while @more = 1 645 begin 646 if @index_id != 1 and @level = 0 and @mrpg != 0 647 select @rows_per_page = @mrpg 648 649 /* 650 ** No fill factor for non-leaf nodes, 651 ** recalculate rows/page 652 */ 653 if @index_id != 1 and @level = 1 654 begin 655 select @row_len = @row_len + 4 656 select @rows_per_page = @page_size / @row_len - 2 657 if @rows_per_page > 256 658 select @rows_per_page = 256 659 end 660 661 if @level > 255 662 begin 663 /* This index is too big to build */ 664 raiserror 19414, @index_name 665 goto kill_proc 666 end 667 668 /* 669 ** calculate the number of pages at a single index 670 ** level 671 */ 672 select @temp = @next_level / 673 convert(float, @rows_per_page) 674 675 /* Add in a factor for allocation pages */ 676 select @temp = @temp + (@temp / 256.0) + 1.0 677 678 select @level_pages = @temp 679 680 insert #results values 681 (@index_name, @index_type, @level, @level_pages, 682 floor(@level_pages) * @page_K) 683 684 select @next_level = @level_pages 685 select @level = @level + 1 686 687 /* see if we can fit the next level in 1 page */ 688 if @rows_per_page >= @next_level 689 select @more = 0 690 end 691 /* Account for single root page */ 692 if @level_pages > 1 693 insert #results values 694 (@index_name, @index_type, @level, 1, @page_K) 695 end 696 697 698 /* Now look for next index id for this table */ 699 700 select @last_id = @index_id 701 select @index_id = null 702 select @index_id = min(indid) 703 from sysindexes 704 where id = @table_id 705 and indid > @last_id 706 and indid < 255 707 708 end 709 710 select name, type, idx_level, 'Pages' = str(pages, 12, 0), 'Kbytes' = str(Kbytes, 12, 0) 711 into #results_2 712 from #results 713 714 exec sp_autoformat @fulltabname = #results_2 715 716 drop table #results_2 717 718 select Total_Mbytes = str(sum(Kbytes) / 1024.0, 17, 2) 719 from #results 720 721 /* Get ready to calculate creation times for the indexes */ 722 723 insert #times(name, tot_pages) 724 select name, sum(pages) 725 from #results 726 where type != 'data' 727 group by name 728 729 /* Get index type from #results */ 730 update #times 731 set #times.type = #results.type 732 from #times, #results 733 where #times.name = #results.name 734 735 /* Add data pages to size of clustered index */ 736 update #times 737 set tot_pages = tot_pages + @data_pages 738 where type = 'clustered' 739 740 /* Calculate for clustered index */ 741 742 select @factor = ceiling((log(@data_pages) / 2.07944) - 1.8813) 743 update #times 744 set time_mins = ((2 * @factor) + 1) * @data_pages / 60 / @iosec 745 where type = 'clustered' 746 747 /* Calculate for non-clustered index */ 748 749 update #times 750 set time_mins = ((2 * @factor * tot_pages) + @data_pages) 751 / 60 / @iosec 752 where time_mins is null 753 754 select name, type, 'total_pages' = str(tot_pages, 12, 0), 'time_mins' = str(time_mins, 12, 0) 755 into #times_1 756 from #times 757 758 exec sp_autoformat @fulltabname = #times_1 759 760 drop table #times_1 761 762 kill_proc: 763 764 drop table #results 765 drop table #col_table 766 drop table #times 767 768 return 769
exec sp_procxmode 'sp_estspace', 'AnyMode' go Grant Execute on sp_estspace to public go
RESULT SETS | |
sp_estspace_rset_002 | |
sp_estspace_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED writes table tempdb..#results_2 (1) reads table sybsystemprocs..syscolumns ![]() reads table sybsystemprocs..sysobjects ![]() reads table master..spt_values (1) ![]() read_writes table tempdb..#col_table (1) calls proc sybsystemprocs..sp_autoformat ![]() reads table tempdb..systypes (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..syscolumns (1) ![]() reads table tempdb..syscolumns (1) ![]() calls proc sybsystemprocs..sp_namecrack ![]() reads table master..systypes (1) ![]() read_writes table tempdb..#colinfo_af (1) writes table tempdb..#results_1 (1) read_writes table tempdb..#results (1) reads table sybsystemprocs..sysindexes ![]() read_writes table tempdb..#times (1) writes table tempdb..#times_1 (1) calls proc sybsystemprocs..sp_aux_getsize ![]() |