opc.three (11/24/2012)
Jeff Moden (11/22/2012)
dave-L (11/22/2012)
Hi Jeff. That's my whole question really. Is there a good way to fix this and is it worth trying to fix.True using a UDF to generate the UUIDs will have more over head than native NEWID(), but my thought was I would save so much time on the scores of thousands of key lookups that occur because my CI is on a useless identity that this would be worth it.
Dave
I'm still confused about it all. I thought you said that the GUID from the GUI was absolutely required and has been used in thousands of places throughout the code. The only way that you'd get rid of the Key Lookups is to do one of two things. Either put the CI on the GUID with a decent FILL FACTOR to prevent rampant page splits during inserts or build a covering index with the GUID as the leading column. All of this other work will do nothing to get rid of the Key Lookups.
I think where things are going is that if the GUID is made to be sequential then the CI can be again redefined using the GUID resulting in the key lookups going away and fragmentation due to mid-page page splits also going away meaning the FILLFACTOR does not need to be modified solely to ease things up for a non-sequential GUID.
I think that using sequetial GUIDs would be a very bad idea for this because of what I said in my previous post above. Restarting Windows can cause the sequence to restart at a lower value than the previous sequence which would cause new rows to be inserted into the logical "middle" ("begining" on the first restart) of the table if the CI were on that column. Since they would again be sequential, page splits would be rampant right after the restart. I don't believe they'd settle down much after that, either. I would be an interesting test over time.
--Jeff Moden
Change is inevitable... Change for the better is not.