Inserts With Sequential Guid Key On Clustered Index Not Significantly Faster

In SQL Server 2008 I have tried to reproduce the results from the experiments on clustered index on sequential vs. non-sequential GUID keys seen here http://sqlblog.com/blogs/de

Solution 1:

Can you try this modified script and post your results?

SET NOCOUNT ONCREATETABLE TestGuid1 (Id UNIQUEIDENTIFIER NOTNULLDEFAULT NEWID() PRIMARY KEY,
    SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

    CREATETABLE TestGuid2 (Id UNIQUEIDENTIFIER NOTNULLDEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

    DECLARE@BatchCounterINT=1

    WHILE (@BatchCounter<=20)
    BEGINBEGIN TRAN

    DECLARE@LocalCounterINT=0

        WHILE (@LocalCounter<=100000)
        BEGININSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
        SET@LocalCounter+=1ENDSET@LocalCounter=0

        WHILE (@LocalCounter<=100000)
        BEGININSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
        SET@LocalCounter+=1ENDSET@BatchCounter+=1COMMITEND

    DBCC showcontig ('TestGuid1') WITH tableresults
    DBCC showcontig ('TestGuid2')  WITH tableresults

    SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWID()]
    FROM TestGuid1
    GROUPBY batchNumber

    SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWSEQUENTIALID()]
    FROM TestGuid2
    GROUPBY batchNumber

DROPTABLE TestGuid1
DROPTABLE TestGuid2

I see quite wildly varying results between individual runs (on my laptop not a server!) but a definite trend for sequential to be faster.

NEWID() Average 5168.9

batchNumberNEWID()-------------------------------142702248032706433335748065346743068771397313104760114680124113133433142686154963168040175313188160199533202750

NEWSEQUENTIALID() Average 3000.85

batchNumberNEWSEQUENTIALID()-------------------------------------120162182031886418705487363473737308369091983102020111906125596132100141950152096161876175196182110192113207713

Solution 2:

Since I wrote that original blog post, I decided to run your code, here is what I get

38726-- newid()312550-- newsequantialID 

Remember I am running this on a server with 32 GB of RAM and 8 procs, not on a laptop

on my local machine, I almost see no difference between the two

Remember, besides inserts, reads will be much slower because the table is fragmented

Here is what I get when running Martin's script on the server

batchNumberNEWID()171696191706141680161706516606189071650816631316731516832165691673201750120333167310167312167041650111690181696batchNumberNEWSEQUENTIALID()212769126020129013126615128017126619126651260612667126081260112433125610127012126314126616127641256111270181270

Here is what happens on my desktop, files are not sized BTW

batchNumberNEWID()194702444635996438605417062403732838357391883103980112580122780131643142836153250164303173250183376198723202616batchNumberNEWSEQUENTIALID()125662133631256431235302361166723968118092386103896113790123066131396142010151183163110174060184260191896202013