index size data large than data size

Hi all,
Question:
1. If the database index size is large than data size then what are the
impact of the database system? Data size = 12,789 MB Index size = 72,574 MB
2. What are the possiblilities can caused the index data increasing so huge?
Thank youIt'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.
HTH
Kalen Delaney, SQL Server MVP
"Sam Sim" <Sam Sim@.discussions.microsoft.com> wrote in message
news:1DCC3979-F829-4FB1-8AA5-5CAD38BD5C90@.microsoft.com...
> Hi all,
> Question:
> 1. If the database index size is large than data size then what are the
> impact of the database system? Data size = 12,789 MB Index size = 72,574
> MB
> 2. What are the possiblilities can caused the index data increasing so
> huge?
> Thank you
>|||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
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 (althoug
h
>sometimes it can be much more) and you can have lots of nonclustered indexe
s
>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, an
d
>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 sa
y
>about it and post back questions after you it the docs.
>
>