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

Question

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?

Answer 1

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

Answer 2

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