sp_estspace

Adaptive Server version 12.5 adds the pagesize parameter to sp_estspace. The full syntax for sp_estspace is:

sp_estspace table_name, no_of_rows, fill_factor,
cols_to_max, textbin_len, iosec, page_size

pagesize – allows you to estimate the space required for a given table – and all of its indexes – if you migrate the table to a server of the specified page size. You can either specify a page size (2, 4, 8, or 16K) or NULL to use your current page size. Because page allocation allocates the same size page for various objects, the page_size value applies to all page types (index, data, text and so on).

Example 1

declare @i int
select @i = avg(datalength(copy)) from blurbs
exec sp_estspace blurbs, 6, null, null, 16, @i, "16k"
name                     type        idx_level Pages        Kbytes
------------------------ ----------- --------- ---------    -------
blurbs                   data                0        8        128
blurbs                   text/image          0        6         96
blurbs_ind               clustered           0        1         16
blurbs_ind               clustered           1        1         16

Total_Mbytes
-----------------
0.25

name                     type         total_pages  time_mins
------------------------ ------------ ------------ ------------
blurbs_ind               clustered             10            0
blurbs                   data                   6            0

This example is run after adding a clustered index to the blurbs table.

This example is run on a 2K server, and indicates that the blurbs table would require .25MB after it is migrated to a 16K server. Below is the same query run on a 16K server, which verifies the .25MB space requirement:

declare @i int
select @i = avg(datalength(copy)) from blurbs
exec sp_estspace blurbs, 6, null, null, 16, @i, "16k"

name                     type         idx_level Pages     Kbytes
------------------------ ------------ --------- --------- ------
blurbs                   data                 0         8    128
blurbs                   text/image           0         6     96
blurbs_ind               clustered            0         1     16
blurbs_ind               clustered            1         1     16

Total_Mbytes
-----------------
0.25

name                    type         total_pages  time_mins
----------------------- ------------ ------------ ----------
blurbs_ind              clustered              10          0
blurbs                  data                    6          0

Example 2

declare @i int
select @i = avg(datalength(copy)) from blurbs
exec sp_estspace blurbs, 1000, null, null, 16, @i, "2k"

name                    type         idx_level Pages        Kbytes
----------------------- ------------ --------- ------------ ------
blurbs                  data                 0     16           32
blurbs                  text/image           0   1000         2000
blurbs_ind              clustered            0      1            2
blurbs_ind              clustered            1      1            2

Total_Mbytes
------------
1.99

name                    type         total_pages  time_mins
----------------------- ------------ ------------ ---------
blurbs_ind              clustered             18          0
blurbs                  data                1000          0

This example estimates that, if the blurbs table had a thousand rows in it on a 2K server, it would require 1.99MB of space.