Using sp_estspace to estimate object size

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:

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.

NoteThe index creation times printed by sp_estspace do not factor in the effects of parallel sorting.