compare clustered and unclustered index space

there are 350 table in database.
all of tables have following design:
time_fld 8 byte,date-fld 5 byte,value float(8 byte).
in each hour 60 records add to each table.
so at end of each day 24*60*23*350 byte will be added to db size.
(23 length of record in each table)
there is a unclustered index on date-fld and time-fld on each table
(so there are 350 unclustered index in db)
after 120 days my db size is 30 GB!
but 120*24*60*23*350=1.39 GB
can be the size of all unclustered index be 28.6 GB!
we need reports on tables so index is necessary.
please help me as soon as possible.
thanks of all
Message posted via http://www.sqlmonster.com
Hi
Sounds like you have very fragmented database. You can use DBCC SHOWCONTIG
to check fragmentation.You need to either run regular jobs to remove the
fragmentation. (You can use either DBCC INDEXDEFRAG and DBCC DBREINDEX to do
this, see BOL for details).
Another option, if you do not have the window to regularly rebuild or
reindex. You could change the clustered indexes to non-clustered. You can
find that tables with non-clustered indexes can perform better than highly
fragmented tables with a clustered index.
Hope this helps
John
"sepideh iranpour via SQLMonster.com" wrote:

> there are 350 table in database.
> all of tables have following design:
> time_fld 8 byte,date-fld 5 byte,value float(8 byte).
> in each hour 60 records add to each table.
> so at end of each day 24*60*23*350 byte will be added to db size.
> (23 length of record in each table)
> there is a unclustered index on date-fld and time-fld on each table
> (so there are 350 unclustered index in db)
> after 120 days my db size is 30 GB!
> but 120*24*60*23*350=1.39 GB
> can be the size of all unclustered index be 28.6 GB!
> we need reports on tables so index is necessary.
> please help me as soon as possible.
> thanks of all
> --
> Message posted via http://www.sqlmonster.com
>
|||No. They said they are unclustered. If you clustered them, they would
store the data and you would not be hit with the additional index space
overhead as the data would be stored with and be apart of the key.
But I agree that there is some sever fragmentation and/or splitting going
on. Sounds like you have a lot of free space in the data pages.
If the row length is truely only 23, the split pages shouldn't be that bad.
Take a look at sysindexes for row lengths and potentials as well as the
current consumption of data pages.
SELECT objname = OBJECT_NAME(id), *
FROM sysindexes
WHERE indid IN(0, 1)
This will be the actual data pages of the heap (indid 0) or clustered index
(indid 1).
Sincerely,
Anthony Thomas

"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:4089C340-7BF1-4798-BFD8-58B1393ABD6F@.microsoft.com...
Hi
Sounds like you have very fragmented database. You can use DBCC SHOWCONTIG
to check fragmentation.You need to either run regular jobs to remove the
fragmentation. (You can use either DBCC INDEXDEFRAG and DBCC DBREINDEX to do
this, see BOL for details).
Another option, if you do not have the window to regularly rebuild or
reindex. You could change the clustered indexes to non-clustered. You can
find that tables with non-clustered indexes can perform better than highly
fragmented tables with a clustered index.
Hope this helps
John
"sepideh iranpour via SQLMonster.com" wrote:

> there are 350 table in database.
> all of tables have following design:
> time_fld 8 byte,date-fld 5 byte,value float(8 byte).
> in each hour 60 records add to each table.
> so at end of each day 24*60*23*350 byte will be added to db size.
> (23 length of record in each table)
> there is a unclustered index on date-fld and time-fld on each table
> (so there are 350 unclustered index in db)
> after 120 days my db size is 30 GB!
> but 120*24*60*23*350=1.39 GB
> can be the size of all unclustered index be 28.6 GB!
> we need reports on tables so index is necessary.
> please help me as soon as possible.
> thanks of all
> --
> Message posted via http://www.sqlmonster.com
>