1

The large tables in our database are partitioned by an id in order to separate data and speed up IO.

The table of the data is partitioned by the partition key, but the primary key is only on an identity column, and the primary key is not partitioned. Accessing by primary key alone, or primary key + partition key result in the same execution plan. Joining to a table by it's primary key alone, or it's primary key and partition key also result in the same execution plan. However, joining two tables by a common foreign key is much faster when the join includes the partition key.

Is it best practice to include the partition key within foreign key definitions to ensure it is included in joins? We want to structure the data and relationships so that the query optimizer and Object Relational Mapping tools can use the partitioning efficiently. Please share any criticisms or recommendations that you feel are relevant.

My reservation stems from the requirement to define the unique index to document the candidate key, I'm not sure it is ever going to be used as the direct query (use case 1) indicates the primary key is favored.

Example DML is below:

CREATE TABLE dbo.BigTableA
(
BigTableAId bigint NOT NULL IDENTITY (1, 1),
PartitionId int NOT NULL,
BigTableBId bigint NOT NULL
/* Other Attributes ... */
) ON PartitionScheme(PartitionId) 
CREATE TABLE dbo.BigTableB
(
BigTableBId bigint NOT NULL IDENTITY (1, 1),
PartitionId int NOT NULL
/* Other Attributes ... */
)  ON PartitionScheme(PartitionId)

CREATE TABLE dbo.BigTableC
(
BigTableCId bigint NOT NULL IDENTITY (1, 1),
PartitionId int NOT NULL,
BigTableBId bigint NOT NULL
/* Other Attributes ... */
)  ON PartitionScheme(PartitionId)

ALTER TABLE dbo.BigTableA 
ADD CONSTRAINT BigTableA_PK 
PRIMARY KEY CLUSTERED ( BigTableAId ) ON [PRIMARY]

ALTER TABLE dbo.BigTableB 
ADD CONSTRAINT BigTableB_PK 
PRIMARY KEY CLUSTERED ( BigTableBId ) ON [PRIMARY]

ALTER TABLE dbo.BigTableC 
ADD CONSTRAINT BigTableC_PK 
PRIMARY KEY CLUSTERED ( BigTableCId ) ON [PRIMARY]

/* Option A - FKs directly to PK identities */
ALTER TABLE dbo.BigTableA 
ADD CONSTRAINT BigTableB_BigTableA_FK1 
FOREIGN KEY ( PartitionId, BigTableBId ) 
REFERENCES dbo.BigTableB ( PartitionId, BigTableBId ) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION 

ALTER TABLE dbo.BigTableC 
ADD CONSTRAINT BigTableB_BigTableC_FK1 
FOREIGN KEY ( PartitionId, BigTableBId ) 
REFERENCES dbo.BigTableB ( PartitionId, BigTableBId ) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION 

/* Option B - FKs including partition key, requiring unique constaints / indexes */
CREATE UNIQUE NONCLUSTERED INDEX UX_BigTableA_PartitionId_BigTableAId 
ON dbo.BigTableA ( PartitionId, BigTableAId ) 
ON PartitionScheme(PartitionId)

CREATE UNIQUE NONCLUSTERED INDEX UX_BigTableB_PartitionId_BigTableBId 
ON dbo.BigTableB ( PartitionId, BigTableBId )
ON PartitionScheme(PartitionId)

CREATE UNIQUE NONCLUSTERED INDEX UX_BigTableC_PartitionId_BigTableCId 
ON dbo.BigTableA ( PartitionId, BigTableCId )
ON PartitionScheme(PartitionId)

ALTER TABLE dbo.BigTableA 
ADD CONSTRAINT BigTableB_BigTableA_FK1 
FOREIGN KEY ( PartitionId, BigTableBId ) 
REFERENCES dbo.BigTableB ( BigTableBId ) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION 

ALTER TABLE dbo.BigTableC 
ADD CONSTRAINT BigTableB_BigTableC_FK1 
FOREIGN KEY ( PartitionId, BigTableBId ) 
REFERENCES dbo.BigTableB ( BigTableBId ) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION 

Execution plans of simple queries of the PK indicate no performance benefit for including the partition key in the argument list.

/* UseCase1: Simple Query Execution Plans don't shed light, both are 50% of batch
, both indicate a clustered index scan of BigTableB_PK */
SELECT * FROM BigTableB WHERE BigTableBId = 10000000 AND PartitionId = 1000
SELECT * FROM BigTableB WHERE BigTableBId = 10000000

Execution plans of FK to FK joins indicate a big performance benefit.

/* UseCase2: FK to FK join Execution Plans show a big improvement for including the partition key. 
First query is 2% of batch, Second is 98% */
SELECT A.*
FROM BigTableA A
INNER JOIN BigTableC C ON A.PartitionId = C.PartitionId AND A.BigTableBId = C.BigTableBId
WHERE A.BigTableAId = 50000000

SELECT A.*
FROM BigTableA A
INNER JOIN BigTableC C ON A.BigTableBId = C.BigTableBId
WHERE A.BigTableAId = 50000000
  • 2
    It's late so maybe I am not seeing this correctly, but it certainly looks like your tables are actually not partitioned. Since you have clustered indexes your tables are not heaps. So the main table data is not residing where the ON clause of CREATE TABLE points to, but instead where the ON clause of ALTER TABLE...PRIMARY KEY CLUSTERED points to (i.e. [PRIMARY]). If you truly want your data to be partitioned, you need to partition at least the clustered index (whether it be the PK or not). And I hope you aren't using arbitrary values for PartitionID, as that is more harm that good. – Solomon Rutzky Aug 17 '14 at 5:42
  • 1
    Also, for non-partitioned data, it is best to create a separate File Group, maybe call it Data or Tables and use that in the ON clause as well as make it the default File Group. It is best to leave [PRIMARY] for meta-data / object definitions / etc. – Solomon Rutzky Aug 17 '14 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.