File Groups

Hi Guys,
We have a database that is 750GB, 1 primary file group and 4 data files. The 4 data files is spread out on 4 disks.
To increase performance we would like to do the following:
- Archive old data.
- Isolate heavily used tables.
So my question is how should we go about this?
Should we use create more file groups, one for old data and let the newest data stay in the primary file group.
How to isolate heavily used tables, should we put them in filegroups as well?
We haven't tried this before so a detailed description would be appreciated.
Thanks a lot
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

ste5anSenior DeveloperCommented:
How do you want to hanlde the old data logically? Differently named tables or partitioned tables? Also do you want to have different backup plans for those different tables? You can always backup files and/or file groups.

What does "isolate heavily used tables" mean? Higher backup frequency? Or only placing them on their own spindles for faster access?
ColoplastAuthor Commented:
I think we will go with partitioned tables. For now I think the backup plan will be the same.
Isolate heavily used tables means placing them on their own spindles for faster access.
Is it possible to isolate the heavily used tables while they are also partitioned?
Your help is much appreciated.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Partitioning is always a good idea if the table size is at minimum 20GB.
Data files should be at least 1 by processor.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ste5anSenior DeveloperCommented:
Is it possible to isolate the heavily used tables while they are also partitioned?
Sure. You can even use partitioning to separate the hot from the cold data in your tables and store these on different spindles, when you have a good partitioning function.

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
ColoplastAuthor Commented:
Sounds great.
So how do we get started in seperating the hot and cold data with a good partitioning function, if we plan always to have 6 months of hot data in 1 partition and whatever is older is cold data?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you only want to have 2 partitions? You can have more partitions so each one will have less data.
For example, a partition for today's data, another one for last week data, then another for the last month and so on. Don't forget that your partition function should deal with time periods.
ColoplastAuthor Commented:
I think we would like to have 6 partitions (Todays orders, last week, last month, last three months, last 6 months and one for rest).
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good. You'll need to automatize the sliding window process. Here's a good MSDN article about it.
ColoplastAuthor Commented:
When partitioning what about the indexes?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Whenever you can, use the same partition function on indexes. Will speed up your queries.
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 2008

From novice to tech pro — start learning today.