How to allocate objects to different filegroups?
-
I haven't been able to find the specific syntax for this, if it even exists. I have a very large (1 TB+) existing database on a SQL 2000 server that we are about to rebuild on 2005. We want to segment some of the large tables and indexes into specific filegroups, that will exist on specific LUNS of our SAN cabinet. I could have swore that I had ALTER DATABASE syntax somewhere on how to do this, but I'm drawing a blank.
We are NOT ready to take the 2005 step to partitioning yet, as we need to do some more analysis on how the data in the larger tables are used. Right now we're just trying to spread out for some performance gains, and position ourselves for future partitioning.
Any help would be appreciated. Thanks in advance!
-
You can simply move (create new + drop existing) the non clustered indexes to another filegroup using on [file groupname here].
You may also do the same thing with the data by moving the clustered index to another filegroup. Post back if you need more help.
-
What you describe that you want to do is partitioning. The Alter command that I believe that you are thinking of is Alter Table ... Move To ... .
You can add or modify files and filegroups with the Alter Database commands, but you can't move objects from one group to another with Alter Database.
If you want to move an index to another filegroup, you have to drop and recreate it. You can't move it with the Alter Index command. Typically, with most objects, you can specify which filegroup to put it in as part of the Create command, such as Create Table dbo.MyTable ( ...... ) On <Filegroup Name>.
You can place different external parts of a table (index, text_image) on different filegroups, but you can only put a table on a single filegroup unless you partition it.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
I don't think there is any ALTER DATABASE commnad to move the table/index to different filegroup but you can add the filegroup using ALTER DATABASE command...
CREATE CLUSTERED INDEX mynewfilegroup ON mytable (col1, coln)
WITH DROP_EXISTING
ON filegroupname
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
MohammedU
Microsoft SQL Server MVP -
Well, thanks for agreeing with me.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
As other people have pointed out, the easiest way to move a table to another filegroup is to re-create the cluster index with
CREATE CLUSTERED INDEX ... WITH DROP_EXISTING ON filegroupname
This will copy all the data in the table to the new filegroup, and when it is safely there the old version of the table is dropped. If you want to move the non-clustered indexes these will each have to be done separately, but you can still use the DROP_EXISTING syntax.
Note that although EM achieves the same result via the GUI, behind the scenes it crates a new table, hopefully applies all the same constraints, loads it from the old one, drops the original table, then renames the new table to match the old. This sounds a more risky and longer process than running a script with a CREATE ..., and probably is.
The best advice I have seen about how to start structuring the filegroups for a DW is given below. Depending on the size of your DW and the I-O capacitity of your storage, you can set up additional filegroups.
a) Put the fact tables in their own FG
b) Put the dimension tables in their own FG
c) Consider putting the staging tables in their own FG
d) Consider putting the aggregate tables in their own FG
This should leave the initial FG containing only the system tables, however you could use the system FG to also hold one of a) to d).
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
One thing to note about using "CREATE CLUSTERED INDEX ... WITH DROP_EXISTING ON filegroupname" instead of "Alter Table ... Move To filegroupname" is that this moves the entire clustered index whereas Alter Table only moves the leaf level where the data rows are located. So if you want the entire clustered index moved, use "Create Clustered Index ...". If not, use "Alter Table".
IMHO, I can't imagine any reason to separate the levels of the clustered index, so I would agree with Ed that using "Create Clustered Index ..." is the better way to go.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
You can or you can't imagine any reason?
-
Can't
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply