Improving performance of cluster index GUID primary key

Question

Hi, I've a table with large number of rows (10K+) and it primary key is GUID. The primary key is clustered. The query performance is quite low on this table. Please provide suggestions to make it efficient.

Answer 1

You can try sequential GUIDS, which will make the index more effective. Info here.

Answer 2

A clustered index on GUID is not a good design. The very nature of GUID is that it's random, while a clustered index physically orders the records by the key. The two things are completely at odds. For every insert SQL has to reorder the records on disk! Remove clustering from this index!

The time to use clustering is when you have a "natural" order to the data: time inserted, account number, etc. For time fields, clustering is almost free. For account number, it might be free or cheap (when account numbers are assigned sequentially).

While there may be technical ways around the GUID issue, the best idea is to understand when to use clustering.

Answer 3

You need to use newsequentialid() instead see here Some Simple Code To Show The Difference Between Newid And Newsequentialid

Answer 4

Please avoid creating clustered index for lenghty string columns. GUID will have 36 char. It will reduce the query performance even you have created as clustered index. for better practice, use integer identity columns.

Answer 5

You need to analyze your query. We can only guess why your queries perform badly without viewing the execution plan (which you can get quiet easily from SQL Server or Oracle).

Considering that a GUID is a 128-bit value (if stored raw), a GUID cuts the density of the data and index blocks by as much as 50% (in the case of the primary key index) so make sure GUID is appropriate.

But that might not be the problem, so review the query plan. It could be several other issues.

Answer 6

There is no problem with using a GUID as the primary key. Just make sure that when you actually set the GUID to be the primary key then set the index it automatically creates to be of type Non-clustered. A lot of people forget (or dont know) to do this in SQL Server.

NEVER use a clustered index on a GUID. This will cause a physical ordering around the GUID on disk, which is obviously pointless (as others have already pointed out)