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
Regards
Rasmus
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
Regards
Rasmus
ASKER
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.
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.
Partitioning is always a good idea if the table size is at minimum 20GB.
Data files should be at least 1 by processor.
Data files should be at least 1 by processor.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.
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.
ASKER
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).
Good. You'll need to automatize the sliding window process. Here's a good MSDN article about it.
ASKER
When partitioning what about the indexes?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What does "isolate heavily used tables" mean? Higher backup frequency? Or only placing them on their own spindles for faster access?