Posted by Gert-Jan Strik on 07/16/07 16:08

In the old days, before SQL Server 7.0, hotspots were a problem. The
typical example was a clustered index on an Identity column. This would
cause a hotspot, and these old versions of SQL Server could not handle
that. This problem has since been solved.

The other behavior related to (clustered) indexes is page splitting. A
heap does not have page splitting. For tables with a clustered index,
page splitting occurs if the new rows needs to go in a specific place
(dictated by the clustered index order) that does not have enough free
space. Page splitting is relatively expensive, and massive page
splitting is likely to create a lot of unused (wasted) space.

So it depends on the column you would want to cover with the clustered
index. And if you create a compound clustered index, then the order of
the columns matters too. For example, a clustered index on a GUID column
is a bad idea if the table is volatile.

So in my opinion, you need a specific case. You should say: I want a
clustered index on column x. Then the software creators should argue
which transactions or functionality would performs worse, and why. If
the argument is "it would cause excessive page splitting", and this
argument plausible, then they have a case. I would be very skeptical
about all other arguments...

Gert-Jan


alexander.arvidsson@gmail.com wrote:
>
> Hello,
>
> I'm new to this group and I sincerely hope I'm not stepping on anyones
> toes or doing something the wrong way around by beginning my time here
> by asking a question.
>
> I'm a Oracle DBA from the beginning (been one since '97) and I've been
> using SQL Server since 2001. Yesterday one of my customers (I'm a
> consultant) showed me a problem they have, and it turns out it is the
> 'Sparse Extent Scenario' (see
> http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=256&threadid=48326&enterthread=y
> and scroll down to the user cmt_SQL)
>
> The solution is hence simple; add clustered indexes to those tables
> that don't have them. But, here is the actual problem:
>
> The creators of the software that my customer uses (two different
> systems) BOTH claim that using clustered indexes hampers performance,
> each and every time. I can't find ANY resource on the internet that
> validates this, quite the opposite. I am told that the best practices
> is to always us a clustered index on a table.
> Following their own guidelines, there is no clustered index in sight,
> and hence some tables have a whopping 30GB(!) of unused space.
>
> I'm looking for ammunition to use on the abovementioned developers.
> I'm looking for detailed technical explanations why a clustered index
> is so much better than an unclustered ditto. I suspect I would find it
> in Kalen Delaney's books, but unfortunately I don't have them before
> me (although I'm looking to order them). Could anyone point me to a
> suitable usenet post, a web page or anything similar?
>
> Kind regards,
> Alexander

[Back to original message]