I'm developing a database-intensive application which maintains about 5 tables. These tables contain many thousands of records each. All the tables use GUID clustered primary keys. To make it efficient, I've dropped foreign-keys between the tables.

I am running a script 65000 lines long which creates a whole bunch of tables (including my tables) and stored procedures (about half the time spent there) then proceeds to insert into my tables about 40000 records and then updates about 20000 of those records.

It takes 1:15 on my AMD 3.5 Ghz 8-core machine.

Amazingly, if I change those 5 tables such that
 - Add a BIGINT identity surrogate primary key (the queries still join using GUID)
 - Demote the prior clustered GUID primary key to a unique column

then it runs in 3:00 minutes!

Changing it from BIGINT to INT gets to about 1:30!

How is it possible that a clustered GUID PK runs significantly faster than an autoincremented INT and much faster than an autoincremented BIGINT clustered PK?

NOTE: the GUID values themselves are generated in code, not by DB.

Check out this simplified benchmark script demonstrating what i mean. 

<http://pastebin.com/ux5wUJgC>