In addition to what Kalen was saying, the lower the index fill factor
the more space the index takes up. For example, if you've got an index
taking 100MB at 100% fill then the same index will consume about 130MB
at a 75% fill, 200MB at a 50% fill and 400MB at a 25% fill. The fill
factor is basically how full each page in the index is (a 75% fill
factor means each page in the index starts life 1/4 empty immediately
after being built or rebuilt).
So if you've got lots of nonclustered indexes on a table and their fill
factors are considerably less than the clustered index fill factor
(talking about a single table) then they will most likely take up
considerably more space on disk (unless they're really narrow and the
clustered index is really wide). So check the fill factors on your
nonclustered indexes too - there may be a lot of empty space in them.
(Use "SELECT INDEXPROPERTY(<table_id>, <index_name>, 'IndexFillFactor')"
to check the fill factor on an index.)
Of course, it could just be that your stats are a little out of whack.
sp_spaceused calculates its figures from sysindexes (in SQL Server 2000)
and the metadata can get out of date and need to be updated. If you're
using sp_spaceused to view the data/index breakdown then run it again
specifying the @updateusage=true parameter to get a more accurate
picture (although be warned: this will take much longer (than without
the parameter) and put a higher load on the server while calculating the
space stats).
--
*mike hodgson*
http://sqlnerd.blogspot.com [quoted text, click to view] Kalen Delaney wrote:
>It's very easy to have total index size exceed data size. Nonclustered
>indexes take about 20%-40% of the size of the table for each index (although
>sometimes it can be much more) and you can have lots of nonclustered indexes
>on each table. As soon as you have 3 or so, you've got as much index space
>as table space, and if you have six, you have twice as much. SQL Server
>allows up to 249 indexes on a single table.
>
>Look at some of your biggest tables, and see how many indexes they have, and
>what types of columns have the indexes.
>
>If the indexes are well planned, and are necessary for good performance of
>your queries, the impact is positive. However, they can slow down
>modification operations as each index must be maintained whenever data is
>inserted or deleted. You may also need to be aware of fragmentation.
>
>Index tuning is a BIG topic, so you should read what Books Online has to say
>about it and post back questions after you it the docs.
>
>
