Table Index taking up large quantity of DB space
We are using the SAP Mobile Platform 3.0 with ASE 15.7.
We recently faced an issue were our DB got full. We were able to solve the issue by increasing the device size and altering the database to use the extra space. However, we noticed that eventually even that space was slowly consumed.
On further investigation, it was noticed that one particular table SMP_E2E_TRACE was the one taking up most of the space. Our current device size is 16GB. This table is already occupying almost 13GB. Interesting, the data only occupies 800MB, but the index occupies almost 12GB.
I just wanted to know if there is any way to reduced the size of this index.
Bret Halford replied
Is the space being used by actual indexes, or is it being used by text/image storage?
text/image allocations use indid ("index id") 255 while row data uses indid 0, so some tools
and procedures report the text/image space as index.
To get a more detailed report of how space is used, use
sp_spaceused <tablename>, 1
text/image allocations appear with the "index" name "t"+tablename.
If the space is being used by text, there are two common causes of inefficient space usage.
The first is that while a newly-inserted NULL text value is not allocated any pages, a non-null value that is updated to a NULL still retains allocation of one page by default. There is a "dealloc first text page" option that can be set at the table (and more recently database) level. Once the option is set, if you update all null values to null again, these empty pages used to store null will be freed. ASE will then deallocate any text extents which no longer have any used pages on them, but extents with at least one still-used page remain allocated. The allocations can be consolidated by extracting all rows that have a non-null text value, deleting those rows from the table, then reinserting them.
The second main cause is storing comparatively small values in text/image. Each value, even if just a single character, uses at least one page. There is another new feature that allows text/image columns to be defined or altered with an "in row (size)". This causes values smaller than the specified size to be stored in the data row itself rather than on its own text page. You can see if you have many small values with a query something like
select datalength(textcol), count(*)
group by datalength(textcol)
order by datalength(textcol)