CODEWITHSUNDEEP
sql-serverperformanceindexingguidclustered-index
alwayslearning
alwayslearning

Reputation: 4633

How can I observe the performance overhead while inserting into a uniqueidentifier primary key column?

As has been discussed numerous times before and is a known fact - having a clustered index on a uniqueidentifier primary key column, in sql server,will affect performance.

What I would like to do is observe this issue by issuing inserts and measuring certain metrics. I have 2 tables with a uniqueidentifier PK; one with 'newid()' as the default and the other with 'newsequentialid()' as the default value. I plan to insert about a million rows in each and observe the INSERT performance.

What should I be querying for in particular? How can I best observe the performace gain of having sequential GUIDs over random GUIDs? Are there certain system views, stored procedures that give stats about the index page splits and/or other relevant information?

Upvotes: 5

Views: 203

Answers (2)

usr
usr

Reputation: 171206

Create a workload that is characteristic of your application in production. Then measure what you care about: Usually throughput and 95% latency.

Upvotes: 0

NG.
NG.

Reputation: 6063

Although the below link will give the comparision between int and guid, but you can follow the similar approach and give it a try.
https://dba.stackexchange.com/questions/9821/why-do-sequential-guid-keys-perform-faster-than-sequential-int-keys-in-my-test-c

Upvotes: 1

Related Questions