March 16, 2010 at 11:10 pm
Hi ,
Why is it so always the index size is doubled or more than the actual table data size?
What could be reason for this ???
I used sp_spaceused 'tname'.
Thanks in Advance.
March 16, 2010 at 11:32 pm
sp_spaceused - index size give you total size of all indexes.
It seems that you have many indexes with more than one columns in each index (Composite Index) or the many included columns in the indexes on the table.
Also it may happen that-
If you have many large indexes total number of columns in all indexes is seems to me more than table columns as same column is duplicated in multiple indexes.
If you have compact or optimal/only required indexing then index size should not be more than table size.
Database tunning advisor can suggest you many large and different indexes for different queries. Do not create all indexes suggested by DTA in different scenario's. Only create most useful indexes which will help different tye of queries.
March 17, 2010 at 4:06 am
for index size
select
i.name as index_name,
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
order by
p.partition_number
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply