How To Test GUID Primary Key Perfomance in SQL Server

Last Post 05 Mar 2008 04:42 AM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

04 Mar 2008 05:47 AM
What is the best Method or Methods to test for GUID primary key performance vs identity column Primary key performance in SQL Server 2005. I am planing Merge replication an i want to compare GUID column performance against indenity (int) column performance.

I want to find out what the perfromance will be when inserting, updating and select from tables. I also want to compare the performance difference when Joining multiple tables using GUID vs Identity columns.

Does anyone have a detailed explanation or method to carry out the performance test. Thanks

New Member
New Member

05 Mar 2008 12:18 AM
Page splits, Increase in the size of non clustered indexes are the disadvantages i can think of if you use guid instead of int as a clustered pk.
New Member
New Member

05 Mar 2008 04:42 AM
It is nothing short of a colossal disaster to use GUID for primary key as opposed to IDENTITY (int, 1,1) function.

The INT IDENTITY is 4 bytes and sequential when new rows are inserted.

GUID is 16 bytes (larger index tree) and RANDOM for new row insertion. That means quickly trashes the index tree.

For testing, just test for index fragmentation after certain number of inserts which would be typical in your environment.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: The Best SQL Server 2005 Training in the World!

Acceptable Use Policy