this is what call my attention , a table with 900MB using 5.7GB of index area.
Start digging the cause of high space, i found a system index that is not showed by regular sp_helpindex command
using a script to calculate index space usage, the link is "http://www.ribeiros.co.uk/scripts/sybase_table_index_size_in_kb.html"
and the result shows the hidden index name 'tactivity_action_note'
This index on sysindexes table has the attribute indid = 255
Based on Sybase docs , the definition for indid = 255 is if text, image or Java off-row structure (LOB structure),
and searching on google i found the following comment
" they are not normal indexes, as said they are system (internal) indexes. they are to keep the correlation between the table rows and the text, ntext and image data which are not inline with the rows on the same pagees, but on other pages.
you don't need to dbcc indexdefrag those, simply ignore them "
and the sybase article : "http://www.sybase.com/detail?id=1002993"
i understood this is an internal (system ) index , but why does it need to take so much space ??
is there any explanation for the high space usage ? is possible to do anything to cleanup or decrease the number of pages in use ?
based on the columns structure of the table is possible to create a formula to calculate the final value ?
Each row that has to store a text value (not null text) will allocate a 2K page
so if you expect 2 mil rows with text data that can translate to a 2 mil * 2K space requirement for the text alone (assuming your text won't span more than one 2K page). And it gets worse when you upgrade to a larger logical page size (available on 12.5 and later i.e. on a 8K server it allocates a minimum of 8K per row with text)
use varchar if possible