Estimates the amount of space required for a table and its indexes, and the time needed to create the index.
sp_estspace table_name, no_of_rows, fill_factor, cols_to_max, textbin_len, iosec, page_size
is the name of the table. It must already exist in the current database.
is the estimated number of rows that the table will contain.
is the index fillfactor. The default is null, which means that Adaptive Server uses its default fillfactor.
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.
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.
is the number of disk I/Os per second on this machine. The default is 30 I/Os per second.
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).
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
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
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
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
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
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.
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.
If the auto identity option is set in a database, Adaptive Server automatically defines a 10-digit IDENTITY column in each new table that is created without specifying a primary key, a unique constraint, or an IDENTITY column. To estimate how much extra space is required by this column:
In the master database, use sp_dboption to turn on the auto identity option for the database.
Create the table.
Run sp_estspace on the table and record the results.
Drop the table.
Turn the auto identity option off for the database.
Re-create the table.
Rerun sp_estspace on the table, and record the results.
For information about tables or columns, use sp_help tablename.
Any user can execute sp_estspace. Permission checks do not differ based on the granular permissions settings.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
Commands create index, create table
System procedures sp_dboption, sp_help
