Advantages of Moving Tables to another File?

Hi everyone,

We have a few tables in one of our databases that contain customer notes and historical product data. These tables are each in excess of 10 million rows and just shy of 2Gb in size.

Would we see any benefit if we moved these tables out of the main database file and created a new one specifically for them? I'm not too sure about the ins and outs of multiple database files/log files so if anyone could explain it too me or point me to some tutorials that would be great.

Cheers
Danny
LVL 1
LFMSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, having some big tables individually on separate files as well as having the indexes on again other files will give some performance gain, until the disk speed is the bottleneck...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LFMSupportAuthor Commented:
Once the additional files are created, how do I go about moving the tables/indexes?

Do you have links to any tutorials?
0
Aneesh RetnakaranDatabase AdministratorCommented:
Another better option is to use history tables ..
You can divide your data based on some year or something like that, to split data over different tables. This will improve the performance because, maintaining the index will be lot easier.. But you need to modify your queries ..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Once the additional files are created, how do I go about moving the tables/indexes?

to move a table, simply create / modify a clustered index into the new filegroup (ie you cannot move to a specific file, but only to filegroups, but you can always put each file into a single filegroup
0
LFMSupportAuthor Commented:
aneeshattingal: Splitting the data by year in to individual tables and/or even another database has been considered but the reporting and application that use this database are pretty closed in terms of development so it had to be ruled out unfortunately.

angelIII: So I create a new filegroup, create a new file within the new filegroup then "move" the indexes to the new filegroup?


Danny
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>angelIII: So I create a new filegroup, create a new file within the new filegroup then "move" the indexes to the new filegroup?
yes
0
imran_fastCommented:
also put the new file for indexes on seperate disk. the disk should be seperate physical disk.
0
LFMSupportAuthor Commented:
Does the data go in to a new file/filegroup or does that stay on the PRIMARY filegroup?

Danny
0
imran_fastCommented:
Data will be there on the primary file group.
0
LFMSupportAuthor Commented:
And it can't be/isn't moved on to the other file group?
0
LFMSupportAuthor Commented:
What bearing does database replication have on this?

If these changes are made at the publisher are they reflected at the subscriber or are they independent of each other?

Danny
0
imran_fastCommented:
<<And it can't be/isn't moved on to the other file group?>>
if you have sql server 2005 then you can do partition useing partition scheme and pertition function.

<<What bearing does database replication have on this?>>
this has nothing to do with replication.
<<If these changes are made at the publisher are they reflected at the subscriber or are they independent of each other?
>>

Yes, they independent of each other.
0
LFMSupportAuthor Commented:
Thanks for all the comments and pointers!

Danny
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.