Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76

    Question Unanswered: [Help]Why the used space of index is much bigger than data.

    I find the indexes' used space of one table is much bigger than data's.

    Detail:
    my table structure is below:

    id int 4 NULL NULL 0 NULL
    NULL 0
    tradeid int 4 NULL NULL 1 NULL
    NULL 0
    content text 16 NULL NULL 1 NULL
    NULL 0
    servletid int 4 NULL NULL 1 NULL
    NULL 0
    status int 4 NULL NULL 1 NULL
    NULL 0
    requestid int 4 NULL NULL 1 NULL
    NULL 0
    confirmid int 4 NULL NULL 1 NULL
    NULL 0
    opinioncontent text 16 NULL NULL 1 NULL
    NULL 0
    updatedate varchar 255 NULL NULL 1 NULL
    NULL 0
    mender varchar 255 NULL NULL 1 NULL
    NULL 0
    agentid int 4 NULL NULL 1 NULL

    there is only one clustered index on the table.
    index_name index_description index_keys
    ========= ===================== ===========
    PK_CONTRACT clustered, unique located on default id

    But I find :
    1> sp_spaceused contract
    2> go
    name rowtotal reserved data
    index_size unused
    -------------------- ----------- --------------- ---------------
    --------------- ---------------
    contract 5148 45992 KB 692 KB
    42990 KB 2310 KB

    reserved size(allocated) is almost 46M,but data is only spend 692K,and the index spend 43M.It's too terrible.
    I guess the problem is the text field.But I have no idea to explain the suitation.

    Can somebody explain to me.

  2. #2
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    UP

  3. #3
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Due to the text field (indid=255)

    Execute first of all un update statistics if this table

    Code:
    create table contact (
    id int primary key,
    tradeid int ,
    content text ,
    servletid int ,
    status int ,
    requestid int ,
    confirmid int ,
    opinioncontent text ,
    updatedate varchar(255),
    mender varchar(255) ,
    agentid int
    )
    go
    sp_estspace contact, 5148, null, null, 500
    go
    
    name                           type         idx_level Pages        Kbytes
    ------------------------------ ------------ --------- ------------ ------------
    contact                        data                 0          831         1662
    contact                        text/image           0         5148        10296
    contact_id_19535235431         clustered            0            5            8
    contact_id_19535235431         clustered            1            1            2
    
    1 row affected)
    Total_Mbytes
    -----------------
                11.69
    name                           type         total_pages  time_mins
    ------------------------------ ------------ ------------ ------------
    contact_id_19535235431         clustered             837            2
    contact                        data                 5148           12
    go
    name           rowtotal    reserved        data            index_size      unused
    ------------------------------ ----------- --------------- --------------- ---------
    contact                  0            48 KB        2 KB            4 KB            42 KB
    Last edited by fadace; 01-29-04 at 06:27.

  4. #4
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    Thanks for your answer.
    Can u tell me why and how can I do to avoid the situation?
    Last edited by enhydraboy; 02-03-04 at 04:02.

  5. #5
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    UP

Posting Permissions

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