Post reply

actual size of indexes of a table

  • April 21, 2009 at 1:26 pm

    #208407

    hi,

    how do we getthe actual size of indexes of table.

    thanks

    Joseph

  • April 21, 2009 at 1:32 pm

    #981743

    I did like this article by Sanjay Mishra about calculating index size:

    download.microsoft.com/download/D/9/4/D948F981-926E-40FA-A026-5BFCF076D9B9/TEMPDB_Capacity_Planning_Index.doc

    I usually follow rule of thumb that in order to built an index, it will take 2.2 times the size of index

  • April 21, 2009 at 1:35 pm

    #981748

    Joseph (4/21/2009)

    hi,

    how do we getthe actual size of indexes of table.

    thanks

    Joseph

    sp_spaceused TableName

    will give u size of the index of the table

  • April 21, 2009 at 1:39 pm

    #981750

    thanks Mayank...apart this one (sp_spaceused)..is there any DMVs to use to retrive index space info...

  • April 21, 2009 at 1:40 pm

    #981752

    Also thanks much for the link...

  • April 21, 2009 at 2:04 pm

    #981769

    I found this on net, to calculate size of index in ur database(for all tables).

    select objectname, index_size = LTRIM (STR ((CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')

    from

    (

    SELECT

    objectname = object_name(object_id),

    reservedpages = SUM (reserved_page_count),

    usedpages = SUM (used_page_count),

    pages = SUM (

    CASE

    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

    ELSE lob_used_page_count + row_overflow_used_page_count

    END

    ),

    srowCount = SUM (

    CASE

    WHEN (index_id < 2) THEN row_count

    ELSE 0

    END

    )

    FROM sys.dm_db_partition_stats

    group by object_id

    ) a

  • April 21, 2009 at 2:16 pm

    #981784

    once again thank much Mayank..this is what I was looking for.!!!:-)

  • April 21, 2009 at 2:25 pm

    #981793

    Feel glad to be of any help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply