Moving a Large Table into New datafile vs. New database

Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only one
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VMVM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:
> VM wrote:
> > Hi,
> >
> > We have a production database of size 40GB and growing at the rate 1.5
> > GB/month and one table is occupying about 20GB and all other tables
> > are of normal size. Till now every table is in the PRIMARY group and
> > it has only one datafile. Now we would like to split that database
> > by moving that large table out.
> >
> > I was wondering if anyone could tell me what would be the best option
> > in terms of performance of SQLServer of the following options:
> >
> > 1. Create a new Filegroup and move the table into the newfile group.
> > 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> > 3. Create a new database and move the table to the new database, and
> > create a view of the table in the first database pointing to the
> > table in the new database (Assuming the foreign key constraints are
> > taken care with in application).
> >
> > Thankyou,
> > VM
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>