Defragment tables that have no clustered index

Hi!
We have several SQL Servers in a system that replicates
information in non-realtime between them using MSMQ and
Biztalk and to guarantee uniqueness it uses GUID:s as
primary and foreign keys. When planning this solution we
were recommended by Microsoft to use only non-clustered
indexes on these tables.
Since there are a lot of inserts and updates to the data
in this system we have now got a lot of really
fragmentated tables but since we have no clustered indexes
DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
solve this problem?
I have searched the newsgroups (and of course "Inside sQL
Server 2000", Hi Kalen! Any suggestions..?) but found no
answers that works. After reading a post here I tried both
DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
hardly effects the terrible scan density (DBCC SHOWCONTIG)
for these tables. The only other solution I have read
about is to use BCP to export and import the tables but
this quite complicated solution can't really be included
in our weekly maintenance job which is what we want.
Please, anyone, suggestions? I can't be the only one with
this problem..?
- AllanRead in "BOL - DBCC DBREINDEX" after reindexing, update
your statistics manually after the reindex.
Greg
>--Original Message--
>Hi!
>We have several SQL Servers in a system that replicates
>information in non-realtime between them using MSMQ and
>Biztalk and to guarantee uniqueness it uses GUID:s as
>primary and foreign keys. When planning this solution we
>were recommended by Microsoft to use only non-clustered
>indexes on these tables.
>Since there are a lot of inserts and updates to the data
>in this system we have now got a lot of really
>fragmentated tables but since we have no clustered
indexes
>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
>solve this problem?
>I have searched the newsgroups (and of course "Inside sQL
>Server 2000", Hi Kalen! Any suggestions..?) but found no
>answers that works. After reading a post here I tried
both
>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
>hardly effects the terrible scan density (DBCC
SHOWCONTIG)
>for these tables. The only other solution I have read
>about is to use BCP to export and import the tables but
>this quite complicated solution can't really be included
>in our weekly maintenance job which is what we want.
>Please, anyone, suggestions? I can't be the only one with
>this problem..?
>- Allan
>.
>|||There's no easy way to reorg (or perhaps "compact" is a better word as the data isn't sorted in any
way) for a heap. Two ways I can think of:
Create a clustered index and drop it.
Export/import of the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
news:03d101c38386$33166d70$a301280a@.phx.gbl...
> As far as I have understood (and tested) neither DBCC
> DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> have clustered indexes on these tables the data is not
> stored on the leaf level of the index but in a heap. What
> I want to know is how to defragment this heap..
> >--Original Message--
> >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> >your statistics manually after the reindex.
> >
> >Greg
> >
> >>--Original Message--
> >>Hi!
> >>
> >>We have several SQL Servers in a system that replicates
> >>information in non-realtime between them using MSMQ and
> >>Biztalk and to guarantee uniqueness it uses GUID:s as
> >>primary and foreign keys. When planning this solution we
> >>were recommended by Microsoft to use only non-clustered
> >>indexes on these tables.
> >>
> >>Since there are a lot of inserts and updates to the data
> >>in this system we have now got a lot of really
> >>fragmentated tables but since we have no clustered
> >indexes
> >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> to
> >>solve this problem?
> >>
> >>I have searched the newsgroups (and of course "Inside
> sQL
> >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> >>answers that works. After reading a post here I tried
> >both
> >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> it
> >>hardly effects the terrible scan density (DBCC
> >SHOWCONTIG)
> >>for these tables. The only other solution I have read
> >>about is to use BCP to export and import the tables but
> >>this quite complicated solution can't really be included
> >>in our weekly maintenance job which is what we want.
> >>
> >>Please, anyone, suggestions? I can't be the only one
> with
> >>this problem..?
> >>
> >>- Allan
> >>
> >>.
> >>
> >.
> >|||Yes Tibor has it correct. That's one of the reasons I suggest that most
tables have a clustered index.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23W979hBhDHA.616@.TK2MSFTNGP11.phx.gbl...
> There's no easy way to reorg (or perhaps "compact" is a better word as the
data isn't sorted in any
> way) for a heap. Two ways I can think of:
> Create a clustered index and drop it.
> Export/import of the data.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
> news:03d101c38386$33166d70$a301280a@.phx.gbl...
> > As far as I have understood (and tested) neither DBCC
> > DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> > have clustered indexes on these tables the data is not
> > stored on the leaf level of the index but in a heap. What
> > I want to know is how to defragment this heap..
> >
> > >--Original Message--
> > >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> > >your statistics manually after the reindex.
> > >
> > >Greg
> > >
> > >>--Original Message--
> > >>Hi!
> > >>
> > >>We have several SQL Servers in a system that replicates
> > >>information in non-realtime between them using MSMQ and
> > >>Biztalk and to guarantee uniqueness it uses GUID:s as
> > >>primary and foreign keys. When planning this solution we
> > >>were recommended by Microsoft to use only non-clustered
> > >>indexes on these tables.
> > >>
> > >>Since there are a lot of inserts and updates to the data
> > >>in this system we have now got a lot of really
> > >>fragmentated tables but since we have no clustered
> > >indexes
> > >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> > to
> > >>solve this problem?
> > >>
> > >>I have searched the newsgroups (and of course "Inside
> > sQL
> > >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> > >>answers that works. After reading a post here I tried
> > >both
> > >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> > it
> > >>hardly effects the terrible scan density (DBCC
> > >SHOWCONTIG)
> > >>for these tables. The only other solution I have read
> > >>about is to use BCP to export and import the tables but
> > >>this quite complicated solution can't really be included
> > >>in our weekly maintenance job which is what we want.
> > >>
> > >>Please, anyone, suggestions? I can't be the only one
> > with
> > >>this problem..?
> > >>
> > >>- Allan
> > >>
> > >>.
> > >>
> > >.
> > >
>