0

I've red a couple of blogs/stackoverflow posts about not using a Guid as primary clustered key. Unfortunately we have at the moment and due to this we're experiencing performance degradation (with 900.000 rows in the database).

Our table looks like this:

| ID (PK)         | PARENTID (FK)    | CONTENT       | TYPE         | ROWVERSION |
| UNIQUEIDENIFIER | UNIQUEIDENTIFIER | NVARCHAR(MAX) | NVARCHAR(25) | rowversion |

In this table we're building up a tree (max 3 levels deep). The root has an empty ParentId, in other situation it's pointing to another Id (foreign key).

I want to refactor this table to the following, in order to prevent the UNIQUEIDENTIFIER to be a primary key:

| ID     | PARENTID | TECHID           | CONTENT       | TYPE         | ROWVERSION |
| BIGINT | BIGINT   | UNIQUEIDENTIFIER | NVARCHAR(MAX) | NVARCHAR(25) | rowversion |

I'm keeping the UNIQUEIDENTIFIER in the table because that's what we expose/communicate to the outside world.

The question I have specifically. Which column(s) should be handy for my primary key?

  1. The TechId as (non clustered) primary key and a clustered index on ID.

  2. The TechId as (non clustered) primary key and a clustered index on ID and ParentId.

  3. The Id as clustered primary key and a non clustered key on the TechId.

Or any other suggestions?

Thanks in advance

  • Does your content really need to be NVARCHAR(MAX)? This immediately will cause an increase in IO, even for small values. (MAX values are stored off-page) – George.Palacios Feb 6 at 13:16
  • Also, how do you know it's the GUID that is causing the performance problems? Is that an assumption at this stage? – George.Palacios Feb 6 at 13:20
  • @George.Palacios We store the content in the NVARCHAR(MAX) as a JSON. We know almost for sure that the issue is in the GUID. Because the index of our is all the team fragmented (> 99%). Removing items take a long time (15 seconds), selecting a simpel item by UNIQUEIDENTIFIER takes a lot of time as well. – mrtentje Feb 6 at 13:37
  • How do you know the fragmentation is causing the issue though? Do you have wait statistics tracked? – George.Palacios Feb 6 at 14:21
  • Having a GUID as the pirmary key would cause inserts to be slower due to page splitting. It shouldn't cause SELECTs on the primary key to be a lot slower. However, you are on the right track to getting better performance by using a BIGINT as the primary key, but we can't be sure that will solve the performance problem you're having without a lot more information. Best course of action is to repro the problem in a non-production environment, implement fixes, and confirm it addresses the issues. Performance issues could be due to blocking or other things. – Tony Hinkle Feb 6 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.