Sql Server 2008 R2 Partitioning

Question

Sql Server 2008 R2 Partitioning

Chris Kitchen

Hall of Fame

Points: 3090
More actions
August 14, 2013 at 5:33 am

#296986

Hi,
I have a Sql Server 2008 R2 instance and in one of my databases I have a table which has >100 million records and we have decided to partition this table as the volumes of data is set to increase dramatically over the next 6 months.
Due to certain business rules the "Date" column in the table alone could not be used as the partition key, it had to also include a value which relates to a "BusinessLine".
Therefore I created a new [INT] column on the table which will be the [PartitionKey] column and now holds a combination of the data in the [Date] and [BusinessLine] columns. For example, for the "Date" 01/01/2013 and for the "BusinessLine" 1 the value in the [PartitionKey] cell for that row is 201301011.
The table already has a Primary Key covering certain columns required for the front end application to search on. The system is highly transactional and I am a little reluctant to just remove the existing Primary Key.
So basically my question is...
Is it good practise to add the [PartitionKey] column to the existing Primary Key?
Thanks in advance.
www.sqlAssociates.co.uk

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

Technet topic "Partitioning Unique Indexes" states:

When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen from among those used in the unique index key.
NoteNote
This restriction enables SQL Server to investigate only a single partition to make sure no duplicate of a new key value already exists in the table.
If it is not possible for the partitioning column to be included in the unique key, you must use a DML trigger instead to enforce uniqueness.

have a look at http://technet.microsoft.com/en-us/library/ms187526%28v=sql.105%29.aspx

On top of that, it seems your current primary key is a multi column unique key to comply a business rule.

Dropping that constraint would cause your data model to violate that rule, so you must implement it ( and add the partitioning column due to design of SQLServer ) !

If it is not needed as Primary Key, implement it as a Unique key ( which is different on a data concept basis ).

( By concept, Primary keys are intended to be used for relationships with child tables )

ALTER TABLE dbo.PartTest ADD CONSTRAINT

PK_PartTest PRIMARY KEY NONCLUSTERED

(

PkColumn

)

/*

Msg 1908, Level 16, State 1, Line 1

Column 'Crea' is partitioning column of the index 'PK_PartTest'. Partition columns for a unique index must be a subset of the index key.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

*/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 2

Hi Johan,

Thanks for your response. I've previously read through that article a few times, and it doesn't really answer my question.

I'm basically curious as to whether or not it would be good practise to add the [PartitionKey] column to the existing Primary Key, or should I leave the Primary Key as it is and create a seperate non-unique/non-clustered index on the [PartitionKey] column.

Thanks.

www.sqlAssociates.co.uk

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 3

Thanks Johan for the updated post.

www.sqlAssociates.co.uk

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

What's the goal of the partitioning? What are you expecting to achieve?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 5

Hi Gail,

We are hoping to achieve (

# Faster data searches.

# Be able to mange our data in smaller more manageable chunks.

# Be able to manage index maintenance on the table more efficiently.

# Archive data more efficiently.

# Prepare for a massive data volume increase (for which the business cannot be specific in terms of number of rows).

Thanks.

www.sqlAssociates.co.uk

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

2, 3, 4 and 5, fine, all achievable

1 - https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 7

Thanks Gail,

Completely agree with your comment on #2, #3 #4 and #5. I have see in the past some performance improvements following a bit of clever partitioning on extremely large data sets, but I think in this instance where I'm only looking at ~100 million rows, growing to ~200 million rows, any performance improvements will be through good luck rather than anything else and very hit and miss.

Can I pick your brains with regards to indexing the [PartitionKey]? As this table is accessed by thousands of SPs/queries and hundreds of upstream/downstream systems, I'm extremely reluctant to make any modifications to the existing Primary Key. Therefore I'm wondering if it's worth creating a non-unique non-clustered index on the [PartitionKey]. I'm obivously going to have to test this a lot, but I was just keen to get your thought's and experience on this.

Thanks.

www.sqlAssociates.co.uk

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

If you don't partition-align all your indexes, you cannot switch partitions out. If you don't partition the clustered index, then you haven't got a partitioned table at all.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 9

All the indexes are partition aligned.

I'm just wondering if it's worth creating a non-unique non-clustered index on the [PartitionKey] and leaving the current clustered index alone?

Or from your experience would you suggest including the [PartitionKey] in the clustered index?

www.sqlAssociates.co.uk

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

If you want to partition the clustered index (a unique one enforcing the pk), you have to include the partition column in the key, it's not optional. If you don't partition the clustered index then you don't have a partitioned table at all and you won't be able to partition switch, nor will you be able to break index maintenance up, since the clustered index is the one that needs that most.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 11

Thanks.

Just to clarify, from what you have said, I take it that you are suggesting that I don't create a non-clustered index on the [PartitionKey] column as this defeats the purpose of having table partitions.

Instead, I must include the [PartitionKey] column in the unique clustered index which inforces the integrity of the Primary Key. If I don't do this, then I don't have a partitioned table at all.

By adding the [PartitionKey] column to the unique clustered index, I can break up the index maintenance and switch partitions.

www.sqlAssociates.co.uk