Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxA 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 :)|||(alexander.arvidsson@.gmail.com) writes:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment