April 2, 2015 at 1:10 am
The Primary Key has to have the PartitionID column within it, as you've discovered.
I don't understand why you need to drop any Foreign Keys before the Partition Switch, or are other tables referring to this data? If that is so, then shouldn't those tables be purged beforehand?
Normally, you have as many partitions as you need plus one for any possible errors. So if you have a monthly scheme, there would be 12 - one for each month, plus another for any where the partition number calculation has got it wrong (an answer of 13, for example). In my example I didn't bother with the extra one because the calculation always produced 1, 2 or 3.
What actually happens is your main table and partition switch table have exactly the same structure, with the same number of partitions. So the main table may have three partitions and the switch table has three partitions. If I want to switch partition 2, it will change from being 'pointed' (for wont of a better phrase) to the main table to being pointed to the switch table. At the same time the empty partition that was in the switch table also gets pointed to the main table. So two areas of storage are swapped. The switch table now has the data that was in the main table and the main table now has the empty space that was in the switch table. Then you can truncate the switch table and the data is removed.
April 2, 2015 at 3:55 am
BrainDonor (4/2/2015)
I don't understand why you need to drop any Foreign Keys before the Partition Switch, or are other tables referring to this data? If that is so, then shouldn't those tables be purged beforehand?
Exactly that, and those tables will be purged before this table would be, and should be being parititoned in the same manner (currently some aren't but I'm going to be addressing that). However, because as it currently stands the primary key exists and cannot be partitioned because it doesn't contain the partitioning key, then the only way to run a switch is by dropping the primary key and recreating it afterwards. Which means all the foreign keys have to be dropped. This is only because currently the primary key is not aligned to the partition.
As I said earlier, this is a system which has already been implemented, and I'm late to the party but trying to fix the issues now whilst it's in its infancy rather than letting them become huge.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
April 2, 2015 at 4:13 am
April 2, 2015 at 4:24 am
Will do, and thanks for all the comments so far. From conversations I've been having with the dev team alongside this post, it looks like we will be restructuring all the tables to include the CreatedDate column in the primary keys, which will mean that all indexes will be aligned and the switch out should work across the board without and foreign key violations. At the moment, the table only has a couple of hundred thousand rows from half a month of running, so the change is relatively small.
Unfortunately, as the business hasn't yet decided on the number of periods they want to keep, the rolling partitions is probably going to remain based on the date rather than the partition number style you suggested as the number of partitions required is not yet know; but it's not a particularly big job to write the code to add in the new partition each quarter so I'm not too worried by that, I'll be making sure that the last partition remains empty and is split prior to any data being written to it.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
April 2, 2015 at 5:22 am
We have a couple of tables here where we create new partitions like that, but I haven't had to play with them yet.
One thing you may need to watch when using the date created (getdate() or suchlike) is transactions spanning midnight. It is entirely possible to have the main table row created a fraction before midnight and a row from another table that refers to it via FK, created just after midnight. In that case they could finish in different partitions, which could cause problems with the purge.
A lot of our databases are accessed 24/7 so little things like that have to be considered.
Of course, such a thing could never possibly happen....
April 2, 2015 at 6:07 am
That shouldn't happen, as the pk from the main table which the partitioning will be based upon will always be in the tables referencing it, so we will partition those based on that key (which will now be the CreatedDate/smsID composite key).
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
April 2, 2015 at 8:31 am
What's critical is to get the best clustering. If, to do that, you need to change the PK to match the clustering key, then you really need to do so. The best clustering is important enough to make other changes when necessary to accommodate it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply