sp_estspace

Estimates the amount of space required for a table and its indexes, and the time needed to create the index.

Syntax

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

Parameters

table_name – is the name of the table. It must already exist in the current database.
no_of_rows – is the estimated number of rows that the table contains.
fill_factor – is the index fillfactor. The default is null, which means that the SAP ASE server uses its default fillfactor.
cols_to_max – is a comma-separated list of the variable-length columns for which you want to use the maximum length instead of the average. The default is the average declared length of the variable-length columns.
textbin_len – is the length, per row, of all text and image columns. The default value is 0. You need to provide a value only if the table stores text or image data. text and image columns are stored in a separate set of data pages from the rest of the table’s data. The actual table row stores a pointer to the text or image value. sp_estspace provides a separate line of information about the size of the text or image pages for a row.
iosec – is the number of disk I/Os per second on this machine. The default is 30 I/Os per second.
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 (2048, 4096, 8192, 16384, or 2K, 4K, 8K, 16K) or NULL to use your current page size. If you do not use “K” as a unit specifier, the default for pagesize is bytes. 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).

Examples

Example 1 – Calculates the space requirements for the titles table and its indexes, and the time required to create the indexes. The number of rows is 10,000, the fillfactor is 50 percent, two variable-length columns are computed using the maximum size for the column, and the disk I/O speed is 25 I/Os per second:

sp_estspace titles, 10000, 50, "title,notes", 0, 25

name             type         idx_level Pages        Kbytes
---------------- ------------ --------- ------------ ------------
titles           data                 0         3364         6728
titles           text/image           0            0            0
titleidind       clustered            0           21           43
titleidind       clustered            1            1            2
titleind         nonclustered         0         1001         2002
titleind         nonclustered         1           54          107
titleind         nonclustered         2            4            8
titleind         nonclustered         3            1            2

Total_Mbytes
-----------------
             8.68
 
name               type         total_pages  time_mins
------------------ ------------ ------------ ------------
titleidind         clustered            3386           13
titleind           nonclustered         1060            5
titles             data                    0            2

Example 2 – Uses the average length of existing image data in the au_pix table to calculate the size of the table with 1000 rows. You can also provide this size as a constant:

declare @i int
select @i = avg(datalength(pic)) from au_pix
exec sp_estspace au_pix, 1000, null, null, 16, @i

au_pix has no indexes
 name            type         idx_level Pages     Kbytes
 --------------- ------------ --------- --------- ---------
 au_pix          data                 0        31        63
 au_pix          text/image           0     21000     42000
 
 Total_Mbytes
 -----------------
             41.08

Example 3 – Calculates the size of the titles table with 50,000 rows, using defaults for all other values:

sp_estspace titles, 50000

name             type         idx_level Pages        Kbytes
--------------- ------------ --------- ------------ ------------
titles          data                 0         4912         9824
titleidind      clustered            0           31           61
titleidind      clustered            1            1            2
titleind        nonclustered         0         1390         2780
titleind        nonclustered         1           42           84
titleind        nonclustered         2            2            4
titleind        nonclustered         3            1            2

Total_Mbytes
-----------------
            12.46

name               type         total_pages  time_mins
------------------ ------------ ------------ ------------
titleidind         clustered            4943           19
titleind           nonclustered         1435            8

Example 4 – Runs after adding a clustered index to the blurbs table:

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 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 5 – Estimates that, if the blurbs table had a thousand rows in it on a 2K server, it would require 1.99MB of space:

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

Usage

To estimate the amount of space required by a table and its indexes:

Create the table.
Create all indexes on the table.
Run sp_estspace, giving the table name, the estimated number of rows for the table, and the optional arguments, as needed.

For information about tables or columns, use sp_help tablename.

See also create index, create table in Reference Manual: Commands.

You do not need to insert data into the tables. sp_estspace uses information in the system tables—not the size of the data in the tables—to calculate the size of tables and indexes.

Permissions

Any user can execute sp_estspace. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect