dave-L (11/14/2012)
Hello.I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes.
Later the clustered indexes were rebuilt on an INT identity column to boost the performance of the index. Because the schema (and code base) reference the UUIDs as primary keys everywhere this was never changed.
I'm now concerned that we are doing an extraordinary number of key lookups. For example, when ever a table is joined on the UUID primary key, no additional data is in the index so it must be looked up using the new INT.
My question is: is it better to have all of these key lookups happening or would it be better to just build the clustered indexes on the UUIDs? Alternatively I could include an inordinate amount of columns making the primary key index wide, but at that point wouldn't it be similar to reorganizing my clustered index on the UUID?
Any thoughts would be appreciated.
Thanks, Dave
You might be able to fix this. I haven't tested it but it might work.
{EDIT} My apologies for posting a guess instead of a tested solution. This "hack" doesn't work. Please see my post further down for the test code.
Make the clustered index on the IDENTITY and the GUID column and then change the criteria in the queries to be where SomeIdentityColumn > 0 AND SomeGuidColumn = SomeGuidValue (or whatever). It's a nasty hack but might work.
The advantage here is that the IDENTITY column is first in the index and would virtually eliminate page splits. The change in criteria I mention would cause the clustered index to still be used thereby eliminating the lookups even when you lookup a GUID.
Like I said, not sure it'll work but it seems very likely that it would.
--Jeff Moden
Change is inevitable... Change for the better is not.