Posted by Dan Guzman on 08/16/06 12:40

Index tuning is not black-and-white, especially when it comes to the
clustered index decision. It is likely than some queries will benefit by
the PK clustered index while others will not. You'll need run a mix of
queries that is representative of the actual workload mix to ascertain
overall performance impact. IMHO, an all-or-nothing clustered index
decision is naive.

It is also possible that some tables will benefit with the clustered PK and
others will not. I know that this adds a wrinkle to automated schema
generation but this is reality. You might consider using the Index Tuning
Wizard (SQL 2000) or Database Engine Tuning Advisor (SQL 2005) for index
recommendations based on workload.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"pb648174" <google@webpaul.net> wrote in message
news:1155727057.245342.208480@74g2000cwt.googlegroups.com...
> Performance was actually worse once I added the clustered index. A
> query that takes 4 seconds took 5 seconds after adding clustered
> indexes to all the tables for a particular module. I turned the actual
> execution plan display on and saw that it was using the clustered index
> instead of the non clustered. So without the clustered index the
> largest time used is an "index seek" and a "table spool/lazy spool" and
> with the clustered index the index seek just becomes a clustered index
> seek... No big difference except it takes longer!
>
> pb648174 wrote:
>> Well that makes things simpler then.. I'll try adding the clustered
>> columns to one area of the app and see if it makes a positive or
>> negative performance impact. Thanks for the info guys.
>>
>> Erland Sommarskog wrote:
>> > pb648174 (google@webpaul.net) writes:
>> > > I've been doing a bit of reading and have read in quite a few places
>> > > that an identity column is a good clustered index and that all or at
>> > > least most tables should have a clustered index. The tool I used to
>> > > generate tables made them all with non clustered indexes so I would
>> > > like to drop all of them and generate clustered indexes.
>> >
>> > Yes, having clustered indexes on all tables is a good idea, but the
>> > IDENTITY column is not always the best choice. It's a good choice if
>> > you have a high transaction rate, and you want to avoid fragmentation
>> > and page splits.
>> >
>> > But for SELECT queries it is likely that in most tables that there
>> > are better candidates for the clustered index, as you don't do
>> > range queries on ids that often. So I would suggest that you review
>> > your tables and look for better columns to cluster on.
>> >
>> > Here I had single-column PKs in mind. Clustering on a multi-column PK,
>> > or part of it is another matter. Take an OrderDetails table for
>> > instance.
>> > "SELECT ... FROM OrderDetails WHERE OrderID = @id" is a very likely
>> > query and a clustred index may be great here.
>> >
>> > Stu's suggestion of keeping the PK non-clustered, and adding a
>> > clustered
>> > index as well is not that bad. If you have a multi-column key that is 4
>> > 30 bytes long, but the first key column is four bytes, the clustering
>> > on
>> > the first columns means that the key size for the clustered index is
>> > only 8 bytes. (key col + uniquifier). Since cluster-key colunms appear
>> > in non-clustered index, this matters quite a bit.
>> >
>> > As for looking up the foreign keys, the tables are sysreferences in
>> > SQL 2000 and sys.forein_keys in SQL 2005.
>> >
>> >
>> >
>> > --
>> > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>> >
>> > Books Online for SQL Server 2005 at
>> > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> > Books Online for SQL Server 2000 at
>> > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

[Back to original message]