April 18, 2013 at 6:54 am
HI
Here is the scenario:
Data is loaded in Table "ABC" every week and size of 1 week data is around 10 GB. Table ABC is partitioned on WeekId Column.WeekID+Someother column are PK(clustered). Clustered index is also partitioned.Only 52 week data need to present in table ABC.
There is archive table ARC_ABC with same structure and is also partitioned on Weekid.Clustered index is present with same structure.
Every week, the oldest partition has to be moved out to archive table and a new week data has to be added to ABC table.
Now what will be a better approach out of these two:
1.Create 52 partitions, Take oldest partition out and merge old partitions. Split right side partition for new week data and add data.
ISSUE:We'll need to keep making changes in partition function and scheme. These are database level objects. Will it be a good idea to keep them chaging?
2.Create partition function and scheme for next 5 years (52*5 partitions) so that we don't have to touch these database objects for several years. We can then keep merging the oldest partitions and keep using the new ones week by week fo rnext 5 years.
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 18, 2013 at 6:55 am
What's the goal here? Why are you considering partitioning?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2013 at 7:05 am
Actually the real database is pretty big, around 8 TB. And some of the tables are as big as 900 GB. So to make take advantage of partition elimination ( as all our queries have weekid in them) and to make data upload/removal faster using SWITCH IN/SWITCH OUT, it is being used.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 18, 2013 at 7:07 am
Cool. Just bear in mind that performance gains are not automatic.
I'd suggest option 1. You don't have to (and in fact shouldn't) do the split/merge manually, have a job that runs at the end of every month that does that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2013 at 7:27 am
Thanks Gail. I value your opinions very much. The job actually will be run every week. I had this in my mind to automate this.
Any specific reasons you prefer this?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 18, 2013 at 7:33 am
Yes.
With the automated one, creating partitions happens and you'll never run out of partitions (unless the job fails) and you keep to a minimum the number of partitions that SQL has to look in when running some operation that can't do partition elimination.
With the one where you just create lots and lots of partitions, firstly you're potentially causing performance problems for cases where SQL has to query each partition, second you have a lot of wasted schema objects, lastly when those 5 years are up and all the current DBAs/developers are gone there's no one who will remember/realise they need to create more partitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply