choice of column for clustered index

I often founds 2 recommendations that sounds contradicting to me,
regarding what types of columns should be chosen for a clustered index
(in OLTP environment with lots of inserts):
1)avoid identity column, it will cause inserts to be slowed since they
will compete the same disk area at the end of the table (hot spot).
Instead, use a column whose new value can be at any part of the table
2)use sequential column (like identity one), so that new inserts only
happen at the end of the table and don't cause row migrations (when a
row inserted forces the next rows to move to a new page)
Can anyone give me a more sounding judgement of each of these 2
choices? I'm curious to know in which scenario, which choice is better
than the other, and what the cures are. Correct me if I'm wrong, I feel
that "row migration" is more fearful than "hot spot"
thanks,
TamUnless you're writing a million new rows a day, I doubt the hot spot concern
is valid on today's hardware. Obviously there will be a threshold but for
most applications I can envision this should be a minimal concern.
I have witnessed cases where fragmentation and page splitting, on the other
hand, has caused abysmal performance.
I can't really think of a situation where you'd rather jab new data in the
middle of a page than tack it on the end, unless you were just stuffing
every single transaction on a very busy system into an audit table that you
purge regularly and that you're rarely going to query, in which case, who
cares which way you go.
A
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1125521936.674430.273880@.g49g2000cwa.googlegroups.com...
>I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||See if this helps:
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Tam Vu" wrote:

> I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||> Unless you're writing a million new rows a day,
Wow, did I really say "day" there? Eep. In my experience, a hot spot comes
at a far greater volume than that. Then again, I have been spoiled with
must faster hardware than I had access to in college. :-)