Need ammunition against "clustered index hampers performance"

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

Re: Need ammunition against "clustered index hampers performance"

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

Same guideline applies in SQL Server as applies in Oracle.

Build a test environment ... and test.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Re: Need ammunition against "clustered index hampers performance"

On Jul 16, 4:00 am, alexander.arvids...@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' (seehttp://sqlforums.windowsitpro.com/web/forum/messageview. aspx?catid=25...
> 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

In some that's correct. Refer to a series of excelletn articles by
Greg Linwood, SQL Server MVP:
http://blogs.sqlserver.org.au/blogs/Greg_Linwood/

Note that you can create a clustered index and drop it immediately -
that will take care of your fragmentation.

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

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

Re: Need ammunition against "clustered index hampers performance"

Alex: Yep, I've outlined that as a solution to my customer; I'm fairly
certain the problem will be back, though.

I'll take a look at the links you posted, thank you very much.

Gert-Jan: The developers in this case has shown an interesting
inability to comprehend the concept of pages in itself... The reason I
was given over the phone was 'Nah, over 50 rows in a table, the time
it takes to insert a row is too great. Nonclustered is SOO much
faster'. This was quickly followed by an angry retort about that I
could use DBCC DBREINDEX to sort out the problem with the UNUSED
space. It goes without saying that DBCC DBREINDEX don't do very much
good in this case...

Re: Need ammunition against "clustered index hampers performance"

(alexander.arvidsson@gmail.com) writes:
> 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.

SQL Server MVP Greg Linwood has argued fiercely for heaps, but it is
obvious that heaps require much more manual management. Else, you end
up with badly fragmented tables, as in your customer's case.

But if the developers think that clustered index is worse than sin, then
just set up a job that adds a clustered index to the table and then
drops it. It will run for a longer time than a regular reindexing,
as all non-clustered indexes will have to be rebuilt. Twice. Or drop
the non-clustered indexes first, and then add them back at the end.

Hopefully, someone will object to this and ask "isn't there a
simpler way?", whereupon you answer "sure, we could use a clustered
index instead, but it takes price to be on top".

If you can find the resources to set up a parallel environment as
DA suggested, then it should be an easy game.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Need ammunition against "clustered index hampers performance"

A parallel environment would be the best, but in this case I'll have
no such luck. I was a bit surprised to see that there is some dissent
as to what type of indexes to use. Don't get me the the wrong way; I
certainly understand that everything has its time and place, but I've
been fed that clustered indexes is the way to go, all the way, every
day, practically since I started with SQL Server. I'll burrow down in
Greg's blog and probably pick up Kalen Delaney's book as well.

A huge thanks to all of you for giving me perhaps not the answer I was
expecting, but instead something to ponder for quite a while :)

Re: Need ammunition against "clustered index hampers performance"

(alexander.arvidsson@gmail.com) writes:
> A parallel environment would be the best, but in this case I'll have
> no such luck. I was a bit surprised to see that there is some dissent
> as to what type of indexes to use. Don't get me the the wrong way; I
> certainly understand that everything has its time and place, but I've
> been fed that clustered indexes is the way to go, all the way, every
> day, practically since I started with SQL Server. I'll burrow down in
> Greg's blog and probably pick up Kalen Delaney's book as well.

Let me put it this way: a formula one race car is much faster than a
standard car. But if you want to go from Stockholm to Malmö, you may
still make that trip faster with a standard car if you travel along.
The F1 car needs much more support and maintenance.

Greg has a very strong experience in the perf-tuning field, but his
advice is not for every one.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx