index size large than data

I've been going through my database looking for the largest tables. I use
sp_spaceused to determine the size.
Most of the tables use more space for data then for indexes, e.g. a
sp_spaceused weeegbon get's me:
name rows reserved data index_size unused
WEEGBON 638105 165984 KB 129960 KB 35848 KB 176 KB
Some of the tables however, use little space for data, and lot's of space
for indexes, e.g.
name rows reserved data index_size unused
WEEGBONVELD 46230 33352 KB 1824 KB 30968 KB 560 KB
sp_helpindex weegbonveld gives:
index_name index description index_keys
PK_WEEGBONTVELD nonclustered, unique, primary key located on PRIMARY
WEEGBON_ID, VELDSRT_ID
index_843202104 nonclustered, unique located on PRIMARY rowguid
_WA_Sys_VELDSRT_ID_5535A963 nonclustered, statistics, auto create located on
PRIMARY VELDSRT_ID
Can anyone, explain what causes the indexes to take up more space than the
data? And what is the _WA_Sys_VELDSRT_... index?
Kind regards,
Grtz,
BartI have a feeling that you are fooled by the size of the statistics. Read about CREATE STATISTICS in
Books Online for info on the statistics (...WA_SYS...). Statistics is not index, but it has to exist
in sysindexes, and sometimes SQL Server mis-represent so it looks like the statistics actually uses
a lot of space. It doesn't. You might want to double-check the space usage for statistics against
sysindexes.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"news.chello.nl" <na@.na.com> wrote in message news:idQnb.438447$lh.112925857@.amsnews02.chello.com...
> I've been going through my database looking for the largest tables. I use
> sp_spaceused to determine the size.
> Most of the tables use more space for data then for indexes, e.g. a
> sp_spaceused weeegbon get's me:
> name rows reserved data index_size unused
> WEEGBON 638105 165984 KB 129960 KB 35848 KB 176 KB
> Some of the tables however, use little space for data, and lot's of space
> for indexes, e.g.
> name rows reserved data index_size unused
> WEEGBONVELD 46230 33352 KB 1824 KB 30968 KB 560 KB
> sp_helpindex weegbonveld gives:
> index_name index description index_keys
> PK_WEEGBONTVELD nonclustered, unique, primary key located on PRIMARY
> WEEGBON_ID, VELDSRT_ID
> index_843202104 nonclustered, unique located on PRIMARY rowguid
> _WA_Sys_VELDSRT_ID_5535A963 nonclustered, statistics, auto create located on
> PRIMARY VELDSRT_ID
> Can anyone, explain what causes the indexes to take up more space than the
> data? And what is the _WA_Sys_VELDSRT_... index?
> Kind regards,
> Grtz,
> Bart
>
>|||If you download the free tool from http://www.sqlfe.com you will be able to
see the space allocated to all tables, indexes, and views. These can be
quickly sorted by space used. You will also be able to see where it is
allocated within the data files.
An index starting with _WA_Sys are statics automatically generated by SQL
server.
Barry McAuslin
"news.chello.nl" <na@.na.com> wrote in message
news:idQnb.438447$lh.112925857@.amsnews02.chello.com...
> I've been going through my database looking for the largest tables. I use
> sp_spaceused to determine the size.
> Most of the tables use more space for data then for indexes, e.g. a
> sp_spaceused weeegbon get's me:
> name rows reserved data index_size unused
> WEEGBON 638105 165984 KB 129960 KB 35848 KB 176 KB
> Some of the tables however, use little space for data, and lot's of space
> for indexes, e.g.
> name rows reserved data index_size unused
> WEEGBONVELD 46230 33352 KB 1824 KB 30968 KB 560 KB
> sp_helpindex weegbonveld gives:
> index_name index description index_keys
> PK_WEEGBONTVELD nonclustered, unique, primary key located on PRIMARY
> WEEGBON_ID, VELDSRT_ID
> index_843202104 nonclustered, unique located on PRIMARY rowguid
> _WA_Sys_VELDSRT_ID_5535A963 nonclustered, statistics, auto create located
on
> PRIMARY VELDSRT_ID
> Can anyone, explain what causes the indexes to take up more space than the
> data? And what is the _WA_Sys_VELDSRT_... index?
> Kind regards,
> Grtz,
> Bart
>
>sql