April 21, 2009 at 1:26 pm
hi,
how do we getthe actual size of indexes of table.
thanks
Joseph
April 21, 2009 at 1:32 pm
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
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
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
Also thanks much for the link...
April 21, 2009 at 2:04 pm
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
once again thank much Mayank..this is what I was looking for.!!!:-)
April 21, 2009 at 2:25 pm
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