Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Fragmentation and Clustered Primary Keys


Dear All,

We have a table with over 20 million records that is constantly been
fragmented. I believe that the problem is due to the cardinality of our
indexes.

For instance the primary key for the table is comprised of 5 columns say C1,
C2, C3, C4 and C5, in that order.

The Cardinality for these columns goes something like
C1 - 200 unique entries in C1
C2 - 250 unique entries in C2
C3 - 70 unique entries in C3
C4 - 5000 unique entries in C4
C5 - 300 unique entries in C5

What I think is that the clustered index should be removed and recreated as
C3, C1, C2, C5 and C4

Can anyone tell me if I am wrong here ?

If I am right, can someone please point me to an external resource that
talks about this ?

If I am right, can someone tell me if its the same with a non Clustered
index ?

Thanks for your help

Consider adding a surrogate key, based on an identity and cluster on it.
The clustered index - and hence, the data, will not fragment, since you are
constantly adding data to the "end" of the table.  That said, if you delete
data, then you can get fragmentation.

Once that is done, you can use a UNIQUE constraint for your current primary
key.  Typically, I choose the columns with the most selectivity as the
left-most columns.  In your case, that would be (C4, C5, C2, C1, C3).  You
should pick a fill factor of < 100.  How big/small that is really depends on
how frequently you defrag.  Defragging a nonclustered 5-column key is less
work for the server than for a 5-column nonclustered key.

That said, do you really need 5 columns to make things unique?

Depending on how your users access your data, you may need to add other
nonclustered indexes on one or more of these columns.

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Patricia" <Patri@discussions.microsoft.com> wrote in message

news:D8674333-4D39-41FA-AAB1-37BE891E15FE@microsoft.com...
Dear All,

We have a table with over 20 million records that is constantly been
fragmented. I believe that the problem is due to the cardinality of our
indexes.

For instance the primary key for the table is comprised of 5 columns say C1,
C2, C3, C4 and C5, in that order.

The Cardinality for these columns goes something like
C1 - 200 unique entries in C1
C2 - 250 unique entries in C2
C3 - 70 unique entries in C3
C4 - 5000 unique entries in C4
C5 - 300 unique entries in C5

What I think is that the clustered index should be removed and recreated as
C3, C1, C2, C5 and C4

Can anyone tell me if I am wrong here ?

If I am right, can someone please point me to an external resource that
talks about this ?

If I am right, can someone tell me if its the same with a non Clustered
index ?

Thanks for your help

-----------------------------------------------Reply-----------------------------------------------

Hi

According to Microsoft clustered index creation recommendation, it will not
be a good idea to create clustered index this way,
what Microsoft recommend is, you should define the clustered index key with
as few columns as possible.
What I would do in this situation is, add an extra column and make it as an
IDENTITY column

In clustered index column ordering, the column with most unique value should
come first, i your case
c4,c5,c2,c1,c3 would be the recommended order.

see this link for more information on clustered index creation
http://msdn2.microsoft.com/en-us/library/ms190639(SQL.90).aspx

SQL Query performance turing from Apress ,take a look this book as well

Regards
VT
Knowledge is power, share it....
http://oneplace4sql.blogspot.com/

"Patricia" <Patri@discussions.microsoft.com> wrote in message

news:D8674333-4D39-41FA-AAB1-37BE891E15FE@microsoft.com...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc