Posted by Erland Sommarskog on 07/09/05 01:18

Simon Hayes (sql@hayes.ch) writes:
> I admit I haven't read your post in detail, but clustering on a GUID
> probably isn't a good idea. Since the GUID values are random, but the
> index is ordered, that means a lot of fragmentation as the index has to
> be constantly rebuilt and reordered. You might try changing to a
> nonclustered index instead, and keep the clustered index for columns
> which you often use in GROUP BY or ORDER BY clauses.

As always in the database world, it depends!

SQL Server MVP Greg Linwood taught me a great way to use GUID for a
clustered index. You start off with a fairly low fill factor, say 50%.
Now, new guids will most cases find empty holes to jump in, so there
will not be any page split. Instead, fragmentation will decrease by
time.

Until, that is, pages starts to become full, and there will be page
splitting galore. That's when you need a maintenance window so yuu
can reindex with your fill factor. Obviously, you need a good feeling
for which fill factor you need to the next maintenance window. This is
definitely not a method for the DBA on auto-pilot.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]