0

We have an MSSQL database in which all the primary keys are GUIDs (uniqueidentifiers). The GUIDs are produced on the client (in C#), and we are considering changing the client to generate sequential (comb) GUIDs instead of just using Guid.NewGuid(), to improve db performance.

If we do this, how will this affect installations that already have data with "random" GUIDs as clustered PKs? Can anything be done (short of changing all the PK values) to rebuild the indexes to avoid further fragmentation and bad insert performance?

Please give explicit and detailed answers if you can; I am a C# developer at heart and not all too familiar with all the intricacies of SQL Server.

Thanks!

Eyvind
  • 101
  • 1

1 Answers1

2

Quick comment: GUID + Clustered Index? This sounds bad - Clustered index means the table is physically on the disk in the order of the index - so an insert has to potentially rewrite page(s) of the database to be in order. Change this to a nonclustered index and performance will improve.

Sequential GUIDs have plenty of problems, they really aren't guaranteed unique if sequential, somewhere if you scale a piece to running in multiple places the sequence is duplicated potentially.

Try dropping your clustered index and creating the same index nonclustered - You'll almost certainly fix any problems - The random GUID is NOT a performance problem, Guid.NewGuid takes no time (PLENTY of other things will slow down before that routine is a problem).

Clustered Indices aren't perfect for everything.

Steve Radich-BitShop.com
  • 573
  • 3
  • 7
  • Yes, I know it is bad, and that is why we want to change it. It seems to me that there are two possible options: 1) Keep the primary key as a "random" GUID, and create a different clustered index, for instance an IDENTITY field in SQL Server. I am a little concerned if this might hurt select performance though, since the main lookup key (the GUID PK) will no longer be used as the physical order on disk, but perhaps this is not really an issue? (continued in next comment) – Eyvind Apr 09 '10 at 07:51
  • 2) The other option would be to generate sequential GUIDs, aka COMB GUIDs. The benefit of this would be that all existing indexes could be kept, and the tables would be physically ordered by the PK. However, as the question states, how will this affect installations that already have lots of data with random GUIDs? – Eyvind Apr 09 '10 at 07:55
  • Try what I said, just drop the clustered and create a nonclustered index on the GUID - Unless you're heavily reading all records sequentially (in which case what is the GUID doing?) your worry about select peformance is minimal. You can only make performance better/worse changing indices, you shouldn't be able to break your application (unless it's enforcing distinctness). – Steve Radich-BitShop.com Apr 10 '10 at 06:23