Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Unanswered: Index - high space used within LOB Structure

    I have a table on ASE/12.0.0.6/ with the following structure

    Column_name Type Length Identity
    ------------------------------ ------------------------------ ------ --------
    actv_act_ident numeric 6 1
    activity_num int 4 0
    action_dt datetime 8 0
    resource_id char 25 0
    resource_type char 2 0
    activity_action_id char 25 0
    system_action bit 1 0
    note text 16 0
    value_id varchar 50 0
    value_dt datetime 8 0
    action_time char 8 0
    action_hours float 8 0
    bill_hours float 8 0
    bill_rate money 8 0
    bill_reference varchar 50 0
    initial_action bit 1 0
    recipient varchar 50 0
    send_history varchar 255 0
    print_history varchar 255 0
    form_column01 text 16 0
    form_column02 text 16 0
    form_column03 text 16 0
    form_column04 text 16 0
    form_column05 text 16 0
    form_column06 text 16 0
    form_column07 text 16 0
    form_column08 text 16 0
    last_mod_user varchar 30 0
    last_mod_date datetime 8 0

    Object is not partitioned.
    Lock scheme Datarows

    ==END sp_help <obj> ================================================== ===========================


    this is the size of the table:

    name rowtotal reserved data index_size unused
    -------------------- ----------- --------------- --------------- --------------- ---------------
    activity_action_note 4624607 6894964 KB 959076 KB 5678730 KB 257158 KB

    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'

    table_name index_name reserved_size data_size index_size
    ------------------------------ ------------------------------ ------------- --------------- ----------
    activity_action_note tactivity_action_note 4897200 KB 0 KB 4860056 KB


    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 ?

    really appreciate any comment.

    Regards,
    Helio

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •