Some Simple Code To Show The Difference Between Newid And Newsequentialid

Denis Gobo — Thu, 05 Feb 2009 17:52:00 GMT

Some Simple Code To Show The Difference Between Newid And Newsequentialid

In SQL Server 2000 we got the uniqueidentifier data type and the newid() function

Lots of people thought that newid() would be very handy to create some unique values across all databases.
Newid() is nice but it has a little side effect; it causes terrible page splits because it is a random value. SQL Server 2005 introduced newsequentialid() where each value generated by the function is always greater than the previous value.

Let's take a look

First we will create these two tables

CREATE TABLE TestGuid1 (Id UNIQUEIDENTIFIER not null DEFAULT newid(),

SomeDate DATETIME, batchNumber BIGINT)

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER not null DEFAULT newsequentialid(),

SomeDate DATETIME, batchNumber BIGINT)

now run this block of code to insert 1000 rows in each table

SET NOCOUNT ON

INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),1)

go 1000

SET NOCOUNT ON

INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),1)

go 1000

Create these two clustered indexes

CREATE CLUSTERED INDEX ix_id1 ON TestGuid1(id)

CREATE CLUSTERED INDEX ix_id2 ON TestGuid2(id)

Run the code below

DBCC showcontig ('TestGuid1') WITH tableresults

DBCC showcontig ('TestGuid2') WITH tableresults

You will see that AvgerageFreeBytes is the same for both tables

What about the inserts themselves? Run this code below

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))

FROM TestGuid1

GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))

FROM TestGuid2

GROUP BY batchNumber

The table with the Newsequentialid did the inserts about 40% faster.

Now we will insert 1000 rows in each table again

SET NOCOUNT ON

INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),2)

go 1000

SET NOCOUNT ON

INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),2)

go 1000

No we will look again what happened

DBCC showcontig ('TestGuid1') WITH tableresults

DBCC showcontig ('TestGuid2') WITH tableresults

Wow the first table uses 21 pages while the second one uses 12.
AvgerageFreeBytes is 3524 bytes per page in the first table and only 96 bytes per page in the second table. Obviously newsequentialid is the better choice.

Running the code below you will see that newid is still slower than newsequentialid by about 16% or so for batch 2

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))

FROM TestGuid1

GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))

FROM TestGuid2

GROUP BY batchNumber

Now we will insert 10000 rows and then look at freespace and duration again

SET NOCOUNT ON

INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),3)

go 10000

SET NOCOUNT ON

INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),3)

go 10000

DBCC showcontig ('TestGuid1') WITH tableresults

DBCC showcontig ('TestGuid2') WITH tableresults

The first table uses 117 pages while the second one uses 80.
AvgerageFreeBytes is 2574 bytes per page in the first table and only 21 bytes per page in the second table.

Running the code below you will see that newid is still slower than newsequentialid by about 40% or so for batch 3

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))

FROM TestGuid1

GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))

FROM TestGuid2

GROUP BY batchNumber

Clean up
DROP TABLE TestGuid2,TestGuid1

So that is all, do you use newid or newsequentialid and if you do use newid did you experience fragmentation or performance problems because of it?

Search results matching tags 'Performance', 'page splits', and 'uniqueidentifier'

Some Simple Code To Show The Difference Between Newid And Newsequentialid