Re: Need ammunition against 'clustered index hampers performance'

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@xxxxxxxxx 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
.

Relevant Pages

  • Re: index fillfactor
    ... is page splitting, you use fill factor to prevent page splitting. ... example if you have a clustered index (clustered indexes are not pointing ... Streetnames that start with a-c ... > So far from reading books online, I can only assume that a fillfactor of 100% is> ok for read only tables, any other type of table and I'm unsure of what to set> the fillfactor %. ...
    (microsoft.public.sqlserver.setup)
  • Re: Keylocks and updates
    ... First off if you are being inserted into that heavily then a composite ... splitting. ... I would have a clustered index on a column with the IDENTITY ... Ray Higdon MCSE, MCDBA, CCNA ...
    (microsoft.public.sqlserver.programming)
  • Re: Checkpoint causes need for better IO subsystem?
    ... maybe it's not the IO and that maybe it's an indexing performance issue. ... Microsoft SQL Server MVP ... is this a monotonically increasing indexing scheme that I've created? ... the Clustered index is on multiple cols... ...
    (microsoft.public.sqlserver.tools)
  • Re: Indexes
    ... because we have a clustered index but I was expecting ... Only one row fit per page, so SQL Server ... You have 100000 rows in the table and 1000 rows fit per index page. ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Checkpoint causes need for better IO subsystem?
    ... excellent articles on clustered index selection and its impact on ... clustered index sql kimberly tripp ... Microsoft SQL Server MVP ... the random write capability of the drives comes into play, ...
    (microsoft.public.sqlserver.tools)