Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group>)
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself to
another.
Any suggestions would be appreciated.
Todd C
(Re)Create a clustered index on the table of concern and specify the
filegroup then.
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group>)
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C
|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>
|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-clustered
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>
>
|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
Aaron Bertrand
SQL Server MVP
|||Inserting/querrying/updating...not sure where the load is coming from at this
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the black
art of database performance tuning for a while...
Any good books or resources out there?
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...[vbcol=seagreen]
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issues.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C