|
|
Posted by pb648174 on 08/16/06 11:17
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
Navigation:
[Reply to this message]
|