how can I improve performance if I have used primary key on a guid column?

i got a table with half a million rows.

the primary key is the guid column .

I found the query select * from T wher开发者_运维问答e id ='xxxx' is very slow .

what should I do to improve performance ?

If you can, I would recommend the following:

• drop the existing primary key - especially if it's the clustering key, too (which is it by default)

• add a new INT IDENTITY column

  ALTER TABLE dbo.YourTable ADD NewID INT IDENTITY(1,1)
  

• make that INT field the primary / clustering key:

  ALTER TABLE dbo.YourTable
    ADD CONSTRAINT PK_YourTable PRIMARY KEY(NewID)
  

A primary key (or more precisely: clustering key) on a GUID column is a horrible idea, leads to massive index fragmentation, and thus kills your SELECT performance.

As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.

Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.

Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.

Quick calculation - using INT vs. GUID as Primary and Clustering Key:

• Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
• 6 nonclustered indexes (22.89 MB vs. 91.55 MB)

TOTAL: 25 MB vs. 106 MB - and that's just on a single table!

Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.

• GUIDs as PRIMARY KEY and/or clustered key
• The clustered index debate continues
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap - that's not the point!