469,082 Members | 1,108 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,082 developers. It's quick & easy.

moving a heap to another filegroup

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?.
Jul 20 '05 #1
4 4191

"el emperador" <14*******@terra.es> wrote in message
news:dc**************************@posting.google.c om...
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?.


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
Jul 20 '05 #2
"Simon Hayes" <sq*@hayes.ch> wrote in message news:<41**********@news.bluewin.ch>...
"el emperador" <14*******@terra.es> wrote in message
news:dc**************************@posting.google.c om...
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?.
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


I agree.
maintain a heap table.
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.

Simon


Thanks
Jul 20 '05 #3
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" <14*******@terra.es> wrote in message
news:dc************************@posting.google.com ...
"Simon Hayes" <sq*@hayes.ch> wrote in message news:<41**********@news.bluewin.ch>...
"el emperador" <14*******@terra.es> wrote in message
news:dc**************************@posting.google.c om...
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?.


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
I agree.
maintain a heap table.


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.

Simon


Thanks

Jul 20 '05 #4
"John Bell" <jb************@hotmail.com> wrote in message news:<dB***********************@news-text.cableinet.net>...
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


Thanks John, that's perfect.
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Kevin Grigorenko | last post: by
reply views Thread by newbiegca_sqlsever2000 | last post: by
14 posts views Thread by LineVoltageHalogen | last post: by
2 posts views Thread by si.downes | last post: by
reply views Thread by raj.raghavan | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.