|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > reclaiming space on non-clustered tables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
reclaiming space on non-clustered tables
|
|
| Paul Cahill 2002-10-31, 5:23 am |
| We have a very large table > 10 million rows. It gets a lot of inserts and
these must be fast. Therefore its index is not clustered. However this means
that when we reindex (which we do every night on all tables) it never
shrinks in size.
SQL server seems to treat tables without a clustered index as heaps. Is
there a way of reorganising them to reclaim space?
One way is to drop the index, create a clustered index, drop the cluster,
create the non-cluster. This could be done at night.
Is there a more generic way to do this so a proc can be written that deals
with all non clustered tables?
Paul Cahill
| |
| Greg Linwood 2002-10-31, 6:23 am |
| Hi Paul..
Have you considered looking into using a (well tuned) fillfactor on a
clustered index? As long as the fill factor was well tuned, this could work
well as inserts would be fast and the non-clustered index would not need to
be dropped during reindexing.
Cheers,
Greg Linwood
Search the Google archive before re-posting:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul Cahill" <paul.cahill@cableinet.co.uk> wrote in message
news:uLxXbsMgCHA.1960@tkmsftngp12...
> We have a very large table > 10 million rows. It gets a lot of inserts and
> these must be fast. Therefore its index is not clustered. However this
means
> that when we reindex (which we do every night on all tables) it never
> shrinks in size.
>
> SQL server seems to treat tables without a clustered index as heaps. Is
> there a way of reorganising them to reclaim space?
> One way is to drop the index, create a clustered index, drop the cluster,
> create the non-cluster. This could be done at night.
>
> Is there a more generic way to do this so a proc can be written that deals
> with all non clustered tables?
>
> Paul Cahill
>
>
>
>
| |
| Wayne Snyder 2002-10-31, 7:23 am |
| If you are trying to optimize for inserts, you might try making a
clustered index on the PK.
The definition of a heap table is 'a table without a clustered index'.
And lastly, you could make a generic proc that loops through the heaps in a
cursor and creates strings to do the work and exec's the strings.
to get the heap tables list select * from sysindexes where id > 100 and
indid = 0
Hope this helps.
By the way, a dbccs shrinkfile, or shrinkdb will defrag and clean up heaps
as well.
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Paul Cahill" <paul.cahill@cableinet.co.uk> wrote in message
news:uLxXbsMgCHA.1960@tkmsftngp12...
> We have a very large table > 10 million rows. It gets a lot of inserts and
> these must be fast. Therefore its index is not clustered. However this
means
> that when we reindex (which we do every night on all tables) it never
> shrinks in size.
>
> SQL server seems to treat tables without a clustered index as heaps. Is
> there a way of reorganising them to reclaim space?
> One way is to drop the index, create a clustered index, drop the cluster,
> create the non-cluster. This could be done at night.
>
> Is there a more generic way to do this so a proc can be written that deals
> with all non clustered tables?
>
> Paul Cahill
>
>
>
>
| |
| Paul Cahill 2002-10-31, 8:23 am |
| Cheers guys
"Wayne Snyder" <wsnyder@ikon.com> wrote in message
news:#OtI$tNgCHA.2324@tkmsftngp08...
> If you are trying to optimize for inserts, you might try making a
> clustered index on the PK.
>
> The definition of a heap table is 'a table without a clustered index'.
>
> And lastly, you could make a generic proc that loops through the heaps in
a
> cursor and creates strings to do the work and exec's the strings.
>
> to get the heap tables list select * from sysindexes where id > 100
and
> indid = 0
>
> Hope this helps.
>
> By the way, a dbccs shrinkfile, or shrinkdb will defrag and clean up heaps
> as well.
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Computer Education Services Corp (CESC), Charlotte, NC
> (Please respond only to the newsgroups.)
>
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Paul Cahill" <paul.cahill@cableinet.co.uk> wrote in message
> news:uLxXbsMgCHA.1960@tkmsftngp12...
> > We have a very large table > 10 million rows. It gets a lot of inserts
and
> > these must be fast. Therefore its index is not clustered. However this
> means
> > that when we reindex (which we do every night on all tables) it never
> > shrinks in size.
> >
> > SQL server seems to treat tables without a clustered index as heaps. Is
> > there a way of reorganising them to reclaim space?
> > One way is to drop the index, create a clustered index, drop the
cluster,
> > create the non-cluster. This could be done at night.
> >
> > Is there a more generic way to do this so a proc can be written that
deals
> > with all non clustered tables?
> >
> > Paul Cahill
> >
> >
> >
> >
>
>
|
|
|
|
|