October 8, 2009 at 1:22 pm
See crappy pseudo-code below for the basic idea...
PartitionTable is partitioned on a non-PK column. Yet we need the PK column to be the clustered key. Which means the PK must be part of the partitioning. BUT, we have tables that reference the PK as their FK. That doesn't work with having the partition column as part of the PK. Only options I can see are to drop the FK's (would not be a complete tragedy but not really a good idea) or not cluster on the PK, which would be really, really bad for performance. Partitioning on the PK (col_id) rather than col_status is NOT an option.
Any other ideas anyone?
Crappy pseudo-code:
PartitionTable
(Col_id int,
Col_status int,
Col_otherstuff varchar(25));
ALTER TABLE PartitionTable ADD CONSTRAINT
PK_PartitionTable PRIMARY KEY CLUSTERED
(
Col_ID
,Col_STATUS
) ON PART_SCH_STATUS(Col_STATUS)
TableLink
(link_id int,
col_id int)
ALTER TABLE TableLink WITH CHECK
ADD CONSTRAINT Partition_LINK_FK FOREIGN KEY(Col_ID)
REFERENCES [PartitionTable] (Col_ID)
October 9, 2009 at 2:11 pm
Since your "PartitionTable is partitioned on a non-PK column" your PK could not be clustered. Easy as that.
If in that scenario a non-clusterd PK proves to have a negative effect in performance that probably means partitioning strategy is not good.
Ask yourself... why I am partitioning this table this way?
... does it helps during archive and purge processes?
... does it helps during querieng?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 9, 2009 at 3:14 pm
All queries rely upon the two columns - sometimes both, sometimes just one of the two. The partitioning is needed and impacts performance. The clustering is needed and impacts performance. The partitioned key is not unique enough to be made the clustered key.
What I have done for now is create a unique key on the id column. It makes the partitioning non-storage aligned but that's OK for now as sliding/archiving is not part of the plan.
October 9, 2009 at 3:37 pm
:blink: now I'm confused.
If partitioning strategy is not designed to help archiving/purging and at the same time is hurting performance... why partitioning?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 9, 2009 at 4:17 pm
"impacts performance" = makes things run faster. So they are both good for performance.
May 28, 2020 at 10:49 am
I have done some examples related to this topic and blog posted the same. Kindly go through them if needed
https://www.chiranjeevivamsydba.com/2020/05/partitioning-with-primary-and-foreign.html
https://www.chiranjeevivamsydba.com/2020/05/partitioning-with-primary-and-foreign_28.html
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply