Table Index taking up large quantity of DB space

[Former Member]

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.

[former_member188958 · Accepted solution]

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(*)

from <table>

group by datalength(textcol)

order by datalength(textcol)

-bret

[Former Member · Accepted solution]

Hi Bret,

The issue was as you mentioned. The text/image allocations were the main user of the space. However, I did set the "dealloc first text page" attribute and then updated NULL columns to NULL again to get the space to deallocate. However, it didn't seem to make any difference. I think thats because there were no NULL values in the LOGTEXT column. Running your query, I could see that there were entries in LOGTEXT with small values (8123 rows with a length of 3 for example). I couldn't find the "in row(size)" feature in the documentation for ASE 15.7 ESD1.

I checked with SAP on whether we could truncate the table without it affecting the application and they confirmed it was possible. That resolved the issue. We also turned down the logging on SMP so now the table fills in slower. But I believe that your suggestions are better long term solutions.

Thanks to Mark and Jeff for your assistence as well. I learned a lot.

Regards,

Roy

[former_member182259 · Accepted solution]

Check out the docs for:

alter table <tablename> modify <LOB_Column> in row (length)

In your case:

alter table SAPSR3.SMP_E2E_TRACE modify LOGTEXT in row (8192)

...then run a reorg rebuild on the table (unfortunately, since you are ESD #1, you are not able to run it online).

In SAP default installations, the default in row LOB size specified for migration is 2KB.   If you run sp_help on that table (e.g. sp_help 'SAPSR3.SMP_E2E_TRACE') there should be an in row size listed for it.   Check to see what size it is and let us know.   The other thing you could have done is to get an average size of the lob size using the query:

select ceiling(datalength(<LOB_col>)/100.0)*100 as LOBSize, count(*)

  from SAPSR3.<tablename>

  group by ceiling(datalength(<LOB_col>)/100.0)*100

  order by LOBSize desc

Depending on the other columns in the table, you might be able to adjust the in row lob size to 12KB or slightly higher....you need to be careful going too far above 12KB....    If (for example) the other datatypes add up to ~1KB and you extend the inrow lob size to 15KB, then later when SAP decides to add a column to the table, you will first have to decrease the inrow lob size, do a reorg and then do the upgrade that adds a column.

[former_member188958]

It occurs to me to add a little more here.  If the query for the datalengths of the text values shows that there many long text values, then the "LOB Compression" feature can help by reducing the number of pages needed to store each value once it is compressed.  See Compressed Columns with Large Objects.  "Long" meaning larger than what can be stored on one text page which is (1800/3600/7650/16200) bytes* depending on ASE's page size (2k/4k/8k/16k).  In general, the longer the value, the more compression is likely to help shrink it down and free up pages.

Compression won't help with very small values because they are still each stored on their own page.

Cheers,

-bret

*These values and other interesting static limits can be displayed using the "dbcc serverlimits" command, which requires that switch/traceflag 3604 be on:

isql -Usa -P -w999

1> set switch on 3604

2> go

Switch 3604 ('print_output_to_client') is turned on.

All supplied switches are successfully turned on.

1> dbcc serverlimits

2> go

[...]

Limits as a function of the page size:
======================================

            Item dependent on page size                             : 2048    4096    8192    16384
----------------------------------------------------------------------------------------------------------

[...]

Text-manager related limits

  Max text size available for user data                             : 1800    3600    7650    16200

[Former Member]

Hi Bret, Mark,

I tried running sp_chgattribute 'gomobile.SMP_E2E_TRACE', "dealloc_first_txtpg",1 on my database. But I receive the following error:

Msg 17782, Level 16, State 1:

Server 'LIN35001934', Procedure 'sp_chgattribute', Line 177:

You do not own a table, column or index of that name in the current database.

(return status = 1)

Any idea why?

Regards,

Roy

[former_member182259]

some of the procs don't take the 'owner.obj' format.   What I would do:

1 - make sure you are in the desired database

2 - issue setuser <ownername>

3 - issue exec sp_chgattribute 'SMP_E2E_TRACE', "dealloc_first_txtpg",1

4 - issue setuser

....then run a reorg rebuild on the table (unfortunately, since you are still at ESD 1, you can't do an online reorg).

[Former Member]

Thanks Mark and Bret.

I have just installed ASE and reproduced the error on my laptop. The idea was to have a local environment that I can make changes to without affecting any users. In my local case I have allocation 4GB to both the device and log files and given the entire 4GB to the smp3 DB. I then reproduced a large amount of traffic and sure enough the DB got full with the SMP_E2E_TRACE table index taking up most of the space.

I tried running sp_spaceused SMP_E2E_TRACE,1 like you guys suggested. However, I get the following error.

Msg 17461, Level 16, State 1:

Server 'LIN35001934', Procedure 'sp_spaceused', Line 92:

Object does not exist in this database.

(return status = 1)

The output of select @@maxpagesize, @@version is:

16384       Adaptive Server Enterprise/15.7.0/EBF 19806 SMP ESD#01 /P/X64/Windows Server/aseasap/2918/64-bit/OPT/Wed Feb 08 04:13:45 2012   

select * from systabstats where id = object_id('SMP_E2E_TRACE') returned nothing for me.

I also tried running sp_helpsegment 'default'. The output of result set 3 is attached as an .txt file. Its in a comma seperated format. So if you change the extension to .csv and open it in excel it will be much more readable.

I'm also attached an Sybase Central screenshot of the tables in the DB to show the large quantity of space occupied by the index. In Sybase Central, only one index shows for table SMP_E2E_TRACE named SMP_E2E_TR_4285255292. But the sp_helpsegment output shows 3 for the same table.

sp_helpsegment.txt.zip