How To Test GUID Primary Key Perfomance in SQL Server

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

--
04 Mar 2008 04: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



tom27
New Member
New Member

--
04 Mar 2008 11:18 PM
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.
SQLUSA
New Member
New Member

--
05 Mar 2008 03: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: http://www.sqlusa.com/BI2005WorkShop/ The Best SQL Server 2005 Training in the World!




Acceptable Use Policy
---