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).
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
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.
