Two Tables, 1 Current Month, 1 6 Months - Filegroups, Files and Partitions?
-
Hi,
I have two tables, one is current 30 days, loaded on a daily basis, the other is a 6 month archive. There is also another table that is the archive table on a different drive. That is set up with 6 partitions per file group and file.
Every month at the end of the month, the current 30 day table will be switched for an empty partition on the 6 month table. The way I was designing this was so that the current table is its own file and the 6 month table is its own file, on the same filegroup, so I could do the partition switching. My question is whether I can still do partition switching if the partitions being swapped are in different data files, and if I can, how that works. It would seem that if the data doesn't move to the other drive, you would then have a filegroup spanning two drives. If that was to happen, could that mean that both of those filegroups are dependent on that one file staying online? That seems like it would be a messy design.
-
Having files in the same filegroup on different drives is fine. Yes, if a file is offline, then the whole filegroup is offline. However, only partitions in that filegroup will be offline. The other partitions will be fine.
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] -
There's a fundamental problem with your diagram: you've got tables tied directly to files. Tables are never assigned to files - nothing is ever assigned to files. Objects are assigned to filegroups, not files.
Specifically, in the Filegroups row, look at the Cur FG filegroup. You've got a branch going out under it with current data going to one file, and 6 month data going to another file. You don't get any control over what goes into individual files. All of the files in the same filegroup will have the same type of data. Try reworking your diagram with that in mind, and it'll make more sense.
Also keep in mind that you can't move data between filegroups by doing a partition switch operation. Partition switches have to happen inside the same filegroups. For example, I can't move January 2012 data from a FileGroup_Current to FileGroup_2012 filegroup by doing a partition switch - that would involve an index rebuild instead.
-
Switching the last partition in the 6 month filegroup to the archive filegroup will cause a complete index rebuild on the whole archive table? can i prevent that from happening? maybe what i should do is have a staging table on the archive table and load the staging table first, then perform a switch with an empty partition in that filegroup. both tables in the same file.
As far as the other two tables going to the same filegroup, I really designed it with two files because for some reason at the time I thought I could do that. But then I remembered that I am going to define the table on the partition scheme, and that the partition scheme is applied to a filegroup, which is created on a particular file. So file --> filegroup --> partition scheme --> table.
Anyways. I'm thinking the solution would be to create the 6 month table on the scheme on the cur filegroup, and create the cur table as not partitioned on the same filegroup. Load the cur table monthly and swap it out for an empty partition in the 6 month table.
I have to run for right now, but I'll check the thread for updated comments and then rework the diagram and post. I need to get the DDL for this designed by tomorrow. LOB is sending pressure. As always. Thanks for the quick replies so far by the way. I appreciate the help.
-
inteledyne (6/7/2012)
Switching the last partition in the 6 month filegroup to the archive filegroup will cause a complete index rebuild on the whole archive table? can i prevent that from happening?Sorry, I wasn't quite clear. You can't move partitions between filegroups in a metadata operation. To move a partition across filegroups, you'll need to:
1. Switch out the partition into a new table in the same filegroup
2. Copy/move the now-isolated staging table data to the new filegroup (typically via an index rebuild specifying the new filegroup)
3. Switch the partition back into the new filegroup
I'm oversimplifying here by a lot - it's trickier than it looks, especially when you have to design to accommodate future indexes. Rather than moving things around, what you really want to do is build a new filegroup for each X months of data, and leave the data there permanently. Don't think of partitioning as moving data around as it ages - think of it as having each group of historical data isolated to its own filegroup.
The SQLCAT article on top-10 warehouse practices is probably a good start: http://sqlcat.com/sqlcat/b/top10lists/archive/2008/02/06/top-10-best-practices-for-building-a-large-scale-relational-data-warehouse.aspx
And finally, I'd ask why you're partitioning. It can be a good solution to improve the speed of sliding window loads, but it's rarely an improvement in query performance. Kendra's got a great post on that here:
http://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/
-
Okay, having a group of historical data logically separated into filegroups and data files makes sense.
We are partitioning because the table has a ton of data and will continue to grow. There is not really a concept of a current working dataset. The reporting goes back 14 months, must be able to drill to the detail level, and every month is about 10 GB of data.
How do you handle nonclustered indexes for a sliding window scenario? Do they need to be on the same filegroup and database file as the actual tables involved in the sliding window scenario? I wanted to have these nonclustered indexes on their on file and drive.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply