Modification in Database due to use of GUID (uniqueidentifier)

The application I have completed has gone live and we are facing some very specific problems as far as response time is concerned in specific tables.

In short, response time in some of the tables that have 5k rows is very low. And these tables will grow in size.

Some of these tables (e.g. Order Header table) have a uniqueidentifier as the P.K. We figure that this may be the reason for the low response time.

On studying the situation we have decided the following options

1. Convert the index of the primary key in the table OrderHeader to a non-clustered one.
2. Use newsequentialid() as the default value for the PK instead of newid()
3. Convert the PK to a bigint

We feel that option number 2 is ideal since option number 3 will require big ticket changes.

But to implement that we need to move some of our processing in the insert stored procedures to triggers. This is because we need to trap the PK from the OrderHeader table and there is no way we can use

Select @OrderID = newsequentialid() within the insert stored procedure.

Whereas if we move the processing to a trigger we can use

select OrderID from inserted

Now for the questions?

1. Will converting the PK from newid() to newsequentialid() result in performance gain?

2. Will converting the index of the PK to a non-clustered one and retaining both uniqueidentifier as the data type for PK and newid() for generating the PK开发者_JS百科 solve our problems?

3. If you faced a similar sort of situation please do let provide helpful advice

Thanks a tons in advance people

Romi

---

Convert the index of the primary key in the table OrderHeader to a non-clustered one.

Seems like a good option to do regardless of what you do. If your table is clustered using your pkey and the latter is a UUID, it means you're constantly writing somewhere in the middle of the table instead of appending new rows to the end of it. That alone will result in a performance hit.

Prefer to cluster your table using an index that's actually useful for sorting; ideally something on a date field, less ideally (but still very useful) a title/name, etc.

---

Move the clustered index off the GUID column and onto some other combination of columns (your most often run range search, for instance)

Please post your table structure and index definitions, and problem query(s)

Before you make any changes: you need to measure and determine where your actual bottleneck is.

One of the common reasons for a GUID Primary Key, is generating these ID's in a client layer, but you do not mention this.

Also, are your statistics up to date? Do you rebuild indexes regularly?