Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partitioned compound clustered indexes Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 10:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 6:02 PM
Points: 4, Visits: 41
Hi

I have a table with 125 columns, and 250 million rows. Unfortunately the ID column is NVARCHAR(15), and is non-unique. The table undergoes 000's of updates and inserts every day. The clustered index is on fields which assist the reads, however it rapidly gets fragmented and inserts are slow.

I'm attempting to fix the clustered index, and like the idea of partitioning the table...

I considered placing the clustered index on ID & InsertedLogID to make it unique, and would also support partitioning on ranges of InsertedLogID. However 34 (potential) bytes fails the 'Narrow' key objective.

Then I began to think a new Identity column may be a better choice, as it achieves the objective of Ever-Increasing, and reduces page splits. However partitioning on this Identity column wouldn't be very useful.

Could I make the clustered index a compound index of Identity and InsertedLogID columns, and still achieve the Ever-Increasing objective?

Also does UNIQUE need to be specified in the clustered index declaration so SQL knows the key is unique (and doesn't add the uniquifier)?

Thanks in advance,
Dave

Post #1384979
Posted Wednesday, November 14, 2012 11:54 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 954, Visits: 2,633
A Clustered Index doesnt need to be unqiue, in fact the only case where you have to have the Unique index (to my knowledge) is on a PK which by default shuold be unique.

I'm never sure about partitioned tables and clustered Index, Instinct tells me to use the column that is specified in the Partition schema as the Primary column on the cusltered index as, with all other columns in the index as subordinates, but I might be wrong on this, please correct me.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1384986
Posted Thursday, November 15, 2012 2:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:23 AM
Points: 2,841, Visits: 3,984
Dave-644455 (11/14/2012)
However partitioning on this Identity column wouldn't be very useful.
Why you think that partitioning wouldnt be useful here ? i think it will help you here as you are handling high volume of data


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1385026
Posted Thursday, November 15, 2012 7:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 954, Visits: 2,633
Bhuvnesh (11/15/2012)
Dave-644455 (11/14/2012)
However partitioning on this Identity column wouldn't be very useful.
Why you think that partitioning wouldnt be useful here ? i think it will help you here as you are handling high volume of data


Why would you want to partition data on an Identity column best practice for partitioning is to ensure like data is grouped together, eg Time sliced data, divisional groupings, or something similar.



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1385142
Posted Thursday, November 15, 2012 1:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 6:02 PM
Points: 4, Visits: 41
I understand the clustered index doesn't need to be unique, however the column chosen for partitioning (InsertedLogId) is very dense (only about 300 values so far) so wouldn't make a good clustered index on its own.

The reason for choosing InsertedLogId as the partitioning column is because A) it is static, never updated, and B) it is often used in WHERE clauses and could hopefully allow partition elimination.

Does anyone agree this leads to needing a compound clustered index? If so, any suggestions on which fields, and what order they should go in?
Post #1385353
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse