moving a heap to another filegroup

[el emperador]

I have a big table (heap)... well, not so big, I have a small server
and I want to spread access to it across several new disks dedicated
only to that table.

I known its possible to do that creating a clustered index with "ON
filegroup" option but I want to maintain it as a heap, is there any
way to do this without dropping indexes/references - bulk unload -
create table - bulk load - create indexes?.

[Simon Hayes]

Re: moving a heap to another filegroup


"el emperador" <1492a2001@terr a.es> wrote in message
news:dc979468.0 407291227.11f42 272@posting.goo gle.com...[color=blue]
> I have a big table (heap)... well, not so big, I have a small server
> and I want to spread access to it across several new disks dedicated
> only to that table.
>
> I known its possible to do that creating a clustered index with "ON
> filegroup" option but I want to maintain it as a heap, is there any
> way to do this without dropping indexes/references - bulk unload -
> create table - bulk load - create indexes?.[/color]

As far as I know, you have to drop everything and recreate it, as you have
described. But in general, in MSSQL it's a good idea to have a clustered
index on all tables, so it would be interesting to know why you prefer to
maintain a heap table.

Simon

[el emperador]

Re: moving a heap to another filegroup

"Simon Hayes" <sql@hayes.ch > wrote in message news:<410999b5$ 1_3@news.bluewi n.ch>...[color=blue]
> "el emperador" <1492a2001@terr a.es> wrote in message
> news:dc979468.0 407291227.11f42 272@posting.goo gle.com...[color=green]
> > I have a big table (heap)... well, not so big, I have a small server
> > and I want to spread access to it across several new disks dedicated
> > only to that table.
> >
> > I known its possible to do that creating a clustered index with "ON
> > filegroup" option but I want to maintain it as a heap, is there any
> > way to do this without dropping indexes/references - bulk unload -
> > create table - bulk load - create indexes?.[/color]
>
> As far as I know, you have to drop everything and recreate it, as you have
> described. But in general, in MSSQL it's a good idea to have a clustered
> index on all tables, so it would be interesting to know why you prefer to[/color]

I agree.
[color=blue]
> maintain a heap table.[/color]

Well, I have found that after define a clustered index some queries
changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
Lookup" to a "Clustered Index Seek") and surprisingly got a little
slower, that's suggar for the query optimizer so it thinks its better
to use it, I have to investigate why this happens before define "the
correct" clustered index, a not easy task as you know. Right now I
only want to improve "bookmark lookup" on this table that is 60%-80%
of the weigth of the queries.
[color=blue]
>
> Simon[/color]

Thanks

[John Bell]

Re: moving a heap to another filegroup

Hi

Adding the clustered index in the new filegroup and then dropping it will
leave the data in the new filegroup. Your other indexes will be rebuilt
twice though, so it may be quicker to drop and re-create them yourself.

John


"el emperador" <1492a2001@terr a.es> wrote in message
news:dc979468.0 407301415.28c2c 4@posting.googl e.com...[color=blue]
> "Simon Hayes" <sql@hayes.ch > wrote in message[/color]
news:<410999b5$ 1_3@news.bluewi n.ch>...[color=blue][color=green]
> > "el emperador" <1492a2001@terr a.es> wrote in message
> > news:dc979468.0 407291227.11f42 272@posting.goo gle.com...[color=darkred]
> > > I have a big table (heap)... well, not so big, I have a small server
> > > and I want to spread access to it across several new disks dedicated
> > > only to that table.
> > >
> > > I known its possible to do that creating a clustered index with "ON
> > > filegroup" option but I want to maintain it as a heap, is there any
> > > way to do this without dropping indexes/references - bulk unload -
> > > create table - bulk load - create indexes?.[/color]
> >
> > As far as I know, you have to drop everything and recreate it, as you[/color][/color]
have[color=blue][color=green]
> > described. But in general, in MSSQL it's a good idea to have a clustered
> > index on all tables, so it would be interesting to know why you prefer[/color][/color]
to[color=blue]
>
> I agree.
>[color=green]
> > maintain a heap table.[/color]
>
> Well, I have found that after define a clustered index some queries
> changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
> Lookup" to a "Clustered Index Seek") and surprisingly got a little
> slower, that's suggar for the query optimizer so it thinks its better
> to use it, I have to investigate why this happens before define "the
> correct" clustered index, a not easy task as you know. Right now I
> only want to improve "bookmark lookup" on this table that is 60%-80%
> of the weigth of the queries.
>[color=green]
> >
> > Simon[/color]
>
> Thanks[/color]

[el emperador]

Re: moving a heap to another filegroup

"John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<dBPOc.120 29$vm4.12255350 7@news-text.cableinet. net>...[color=blue]
> Hi
>
> Adding the clustered index in the new filegroup and then dropping it will
> leave the data in the new filegroup. Your other indexes will be rebuilt
> twice though, so it may be quicker to drop and re-create them yourself.
>
> John
>[/color]

Thanks John, that's perfect.