January 4, 2011 at 1:47 pm
Hi,
I was wondering if you could please take a look at the way I'll be tackling partitioning and if it's good or not. This will be my first partitioning opportunity and I wanted to do it the best way possible with your help.
Background Information:
Over 1 TB database with existing partitioning (before my time) by month. I didn't think this is a good idea since it combines old data with new data so I wanted to re-partition it. I currently have SQL Server 2005 64-bit Enterprise Edition and will soon be migrating to SQL Server 2008 R2 64-bit Enterprise Edition. I would like to be able to backup the database by filegroup and restore using PARTIAL for quicker recoverability.
I was thinking about adding files/filegroups by year (example: FG1_2006, FG2_2007, etc). Then for the current year, partition the data by month. I was thinking about partitioning the data by week and day... Would it be feasible?
Since tables are already partitioned, I was thinking about creating copies of the tables with the new partitioning function/scheme and moving the data that way since dropping and recreating PKs would degrade performance, dropping the old tables, and renaming the copies to their original table names. Should I create the tables using the new partition schemes or just use [PRIMARY]? Thoughts?
I am planning on using "RANGE LEFT...'20091231 23:59:59.997', '20101231 23:59:59.997', etc" as my partition function. Is there a way to see what data resides on which partition? Is there a way to see what partition_id belongs to which filegroup? I'm just curious so I can double-check the partitioning.
Also, I was wondering if someone could be so kind as to explain how I can switch IN/OUT data? I've been Googling and I still don't quite understand. Here's my interpretation of it. Please correct me if I'm wrong.
1) Create a staging table with clustered PK on the partition scheme
2) If I wanted to move the recent data (let's say, partition_id 19, FG_Day) to the second recent partition (partition_id 18, FG_Week), I would execute:
ALTER TABLE [main_table] SWITCH PARTITION 19 TO [staging_table] PARTITION 19;
--remove old range
ALTER PARTITION FUNCTION PF_Partition() MERGE RANGE ('20110104 23:59:59.997')
ALTER PARTITION SCHEME PS_Partition NEXT USED [FG_Week]
ALTER PARTITION FUNCTION PF_Partition() SPLIT RANGE ('20110105 23:59:59.997')
-- This is where I'm having trouble...
ALTER TABLE [staging_table] SWITCH PARTITION 19 TO [main_table] PARTITION 18
I'm not sure if I'm explaining it thoroughly... In general, how would you move today's data into the "week" filegroup?
Thank you in advance! 😀
January 4, 2011 at 2:03 pm
I'd take a dev copy of the database, on separate hardware, and try a few different partitioning schemes on it. You'll get workability and performance answers that apply to your data that way.
When I've partitioned, I've found it better to build the table for it in the first place, rather than retrofitting it after.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2011 at 3:48 pm
1) WHY do you think you want to partition this table??
2) Partitioning is a VERY advanced topic that you have essentially no chance of doing completely optimally the first time out on anything non-trivial without assistance from an experience person. I HIGHLY recommend you get a mentor on board to keep you out of the numerous pitfalls that await you!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 4, 2011 at 4:59 pm
Why are you changing the partition function from monthly to weekly? Did the queries criteria changed?
In order to switch partitions, the source and dest must be in the same filegroup. You will not be able to perform a switch if you use 1 filegroup per partition.
This is the query I used to determine what filegroup is associated with which partiton.
select ds.name, fg.name, destination_id
from sys.data_spaces ds
join sys.destination_data_spaces dds
on (ds.data_space_id = dds.data_space_id)
join sys.partition_schemes ps
on (ps.data_space_id = dds.partition_scheme_id)
join (select name,groupname
from sys.sysfilegroups a
inner join sys.sysfiles b
on a.groupid = b.groupid) fg
on ds.name = fg.groupname
and ps.name = @PartitionSchemeName
order by destination_id DESC
January 5, 2011 at 7:13 am
Thank you for responding.
To answer your questions, I would like to partition this table because it has over 100 billion records and users do not need to use the historic data very often and for management. I really would like a mentor, but I am the only DBA here. That is why I have asked for your help.
I am now thinking of just partitioning by month and rolling the data into a separate filegroup at year-end. I am having difficulty in doing this since when I MERGE RANGE and removing the partition by month, the data is residing on the PRIMARY filegroup.
January 5, 2011 at 7:29 am
You should be able to move your partitioned table to a new filegroup by moving the clustered index (http://msdn.microsoft.com/en-us/library/ms175905%28SQL.90%29.aspx)
I like the idea of moving this to a separate filegroup, and then partitioning across old data on other filgroups. One per partition might not be the best idea (read http://msdn.microsoft.com/en-us/library/ms177411%28v=SQL.90%29.aspx ). You might stick with 2 filegroups, one for the most recent data where you would perform the switch, and one that has older data, say older than one year. The movement of data between filegroups would not be as quick as the switch in for new data, so you need to plan this out.
I do agree with Kevin that you ought to consider hiring a consultant to help with this in your production system to prevent issues. It would be worth a few days of time to make sure this is done well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply