Performance With Table Partition in SQL 2008

Question

Post reply

Performance With Table Partition in SQL 2008

CrazyMan

SSChampion

Points: 12560
More actions
November 11, 2010 at 6:16 am

#226252

Hi All
Currently I am having a table with nearly a billion records, this table has got an ID column which is primary key clustered, I am thinking to create a partition on this tables based on Month, each month data will be changed to a new file group,
There is a process that will insert thousands of rows into this table every minutes, then there is a job which takes ID into consideration and calculates all information. My doubt is, if we implement this partition, can we still hold the primary key clustered index? will there be any performance degradation when considering the volume of data into account?
The main reason for this partition is to archive the data.
Thanks in advance for all your hell
Cheers
🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

Haven't tested this lately but I did an article a while ago

http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/

The partitioning column was included in the clustered index automatically which can have odd effects.

It would be worth testing to see if this is still the case.

Cursors never.
DTS - only when needed and never to control.

nigelrivett SSCertifiable Points: 5472 More actions · Answer 2

Just tested it and in 2008 the partitioning column is added to the clustered index automatically

Unique indexes need to include the partitioning column so everything will be done just on the partition that is affected.

The only overhead should be the partitioning function.

Cursors never.
DTS - only when needed and never to control.

CrazyMan SSChampion Points: 12560 More actions · Answer 3

Thanks mate, just have a small doubt, i am partitioning the table with date, if this is clustered automatically does that mean i cant have duplicate dates??

Can you please let me know what problems have you faced, it will help me a lot 🙂

cheers

🙂

nigelrivett SSCertifiable Points: 5472 More actions · Answer 4

A clustered index isn't necessarily unique.

If it is unique then it will need to include the partitioning column.

Means that your id will no longer have a unique constraint but id, date will be unique.

You could create a nin-clustered unique index on id but that would add some overhead.

Just noticed it's your PK so would need to have the date added.

Try using this for testing - it comes from the article mentioned earlier - the article has the switch partition testing too.

CREATE PARTITION FUNCTION MyPartitionRange (INT)

AS RANGE LEFT FOR VALUES (1,2,3)

CREATE PARTITION SCHEME MyPartitionScheme AS

PARTITION MyPartitionRange

ALL TO ([PRIMARY])

CREATE TABLE MyPartitionedTable

(

i INT ,

j INT ,

s VARCHAR(MAX) ,

PartCol INT

)

ON MyPartitionScheme (PartCol)

CREATE CLUSTERED INDEX cl_ix ON MyPartitionedTable (j)

drop table MyPartitionedTable

Cursors never.
DTS - only when needed and never to control.

CrazyMan SSChampion Points: 12560 More actions · Answer 5

true , Currently I am having a Primary key Clustered, if I change this to primary key non clustered index what will be the performance impact on this, if we use this ID on quires, i haven't tried this before 🙁

🙂