sp_spaceused and optdiag report on actual space usage. sp_estspace can help you plan for future growth of your tables and indexes. This procedure uses information in the system tables (sysobjects, syscolumns, and sysindexes) to determine the length of data and index rows. You provide a table name, and the number of rows you expect to have in the table, and sp_estspace estimates the size for the table and for any indexes that exist. It does not look at the actual size of the data in the tables.
To use sp_estspace:
Create the table, if it does not exist.
Create any indexes on the table.
Execute the procedure, estimating the number of rows that the table will hold.
The output reports the number of pages and bytes for the table and for each level of the index.
The following example estimates the size of the titles table with 500,000 rows, a clustered index, and two nonclustered indexes:
sp_estspace titles, 500000
name type idx_level Pages Kbytes --------------------- ------------ --------- -------- -------- titles data 0 50002 100004 title_id_cix clustered 0 302 604 title_id_cix clustered 1 3 6 title_id_cix clustered 2 1 2 title_ix nonclustered 0 13890 27780 title_ix nonclustered 1 410 819 title_ix nonclustered 2 13 26 title_ix nonclustered 3 1 2 type_price_ix nonclustered 0 6099 12197 type_price_ix nonclustered 1 88 176 type_price_ix nonclustered 2 2 5 type_price_ix nonclustered 3 1 2 Total_Mbytes ----------------- 138.30 name type total_pages time_mins --------------------- ------------ ------------ ------------ title_id_cix clustered 50308 250 title_ix nonclustered 14314 91 type_price_ix nonclustered 6190 55
sp_estspace also allows you to specify a fillfactor, the average size of variable-length fields and text fields, and the I/O speed. For more information, see in the Adaptive Server Reference Manual.
The index creation times printed by sp_estspace do
not factor in the effects of parallel sorting.
