Newsequentialid (Histrory/Benefits and Implementation)

  • [アーティクル]

            In general, we made significant improvements in SQL Server scalability during Yukon.  One of the areas of improvement is replication scalability.  While doing merge replication testing we found out that scaling was severely affected by high number of I/O operations.  The cause of the problem was that new rows were inserted in random disk pages.  Guid generating function (newid) was returning non-sequential guids which resulted in random B-tree lookups.  After some investigation we figured out that we could use the new OS function UuidCreateSequential with some byte scrambling to convince the rest of SQL engine that guids are produced in sequential order.

           Therefore, we implemented the new intrinsic function, Newsequentialid which is nothing more than a wrapper over the OS function UuidCreateSequential.  Therefore all of the semantics of UuidCreateSequential are present in newsequentialid intrinsic.  The official documentation for UuidCreateSequential is here. 

            The conclusion:  If you are generating guids as your row identifiers, you may be interested in newsequentialid function.  For example, after replication folks started using this function they were able to completely fill their data and index pages.  Also, the row inserts did not require searches through the B-trees because the last modified page was already in memory. 

            You should be aware of all of the limitations of UuidCreateSequential before you start using newsequentialid.  For example, computers without network card will return only locally unique values and the IDs returned can be used to discover some network properties of your server. 

 

    Additional notes:

 

1) While testing newsequentialid in highly concurrent environments, we found that on AMD64 machines the OS function may return same values.  Therefore, we put the global mutex so that only one user can be going through newsequentialid at the time. 

2) The old guid generating function NewId is also a wrapper over an OS function CoCreateGuid, therefore all of the semantics of CoCreateGuid exist in NewId. 

3) The only additional work that we do in newsequentialid is that we take guids and arrange bytes such that values work well with existing engine guid compare algorithm.  Since input bits are unique and we only move the bytes returned by the OS, the output is also guaranteed to be unique. 

4) Because two consecutive calls to this function are guaranteed to return different results, the function is marked as non-deterministic.  Therefore, we opted to allow the use of this function only as default value.  The reason for this decision is because algebrizer and optimizer are limited in what they can do with non-deterministic functions.

Comments

  • Anonymous
    November 29, 2006
    We are considering to use the OS function UuidCreateSequential to generate the key at the client side. As you said the implementation of the newsequentialid in Sql Server 2005 scramble the result from UuidCreateSequential, is there any issue with that approach? Is there any detail information about the scrambling?

  • Anonymous
    December 26, 2006
    My current project uses GUID primary keys in a SQL Server 2005 database. The primary keys are created

  • Anonymous
    January 29, 2007
    It is frustrating implementing NewSequentialID() as a default value within a table (though that is the ONLY place it CAN be used).  Every time I try it, SQL SMS keeps giving an "Error validating the default for column..." message each time I attempt to apply it.

  • Anonymous
    February 13, 2007
    Any luck ever getting around the validating error?

  • Anonymous
    July 22, 2007
    A colleague pointed me to this code that implements NewSequentialId() in C#: http://codebetter.com/blogs/scott.bellware/archive/2006/12/27/156671.aspx

  • Anonymous
    July 27, 2007
    Scott Bellware's c# implementation over on codebetter (link above) only calls the underlying OS function. However, SQL 2005's implementation does some byte re-ordering after calling the same function, and if you want the sequential GUIDs to be "compatible", you'd have to do the same operations on the client side. You can see the reordering in the source code of yaflaGUID (http://www.yafla.com/dforbes/stories/2005/10/17/yaflaguid.html). You'd have to translate the assembly code to c#. In essence you call UuidCreateSequential (as Scott does), then invoke ToByteArray, swap bytes 0 & 3, 1 & 2, 4 & 5 and 6 & 7, and create a new GUID from the byte array.

  • Anonymous
    August 29, 2007
    I posted a C# implementation that handles the byte reordering as a comment to Scott Bellware's Blog post. Go to Scott's blog entry (link above) and look in the comments for the c# snippet. This will produce a guid that is equivalent to SQL's newSequentialID(). I did place the external definition to UuidCreateSequential() in a SafeNativeMethods class that is not included in the snippet. cheers. Kendall

  • Anonymous
    October 16, 2007
    The validation error seems to be a SQL Studio bug.  You can still save the table (after accepting the error again), using the function as the default value.  Thanks Scott and Kendall for your code - I will be working on a NHibernate generator for this and hopefully post it to my blog soon.

  • Anonymous
    February 07, 2008
    Just an observation on the re-ordering of bytes in the implementation of newsequentialid. I understand fully why this must be performed to adhere to the ordering algorithm in SQL Server. But as I understand it this changes the algorithm version for generating the guid from 1 (which is what UuidCreateSequential uses), to whatever is currently the 2nd highest 4 bits of the time (currently D on my system). Am I correct in assuming that this is currently not a problem in practice because (i.e. it will not cause clashes with other algorithms because):

  1. There is no standardized algorithm which has been assigned D, E or F as version number
  2. The 2nd highest 4 bits of the time will not wrap around to a lower value any time soon. ?

  • Anonymous
    April 24, 2008
    A few months ago we started development on a new system. From the ground up we redesigned everything

  • Anonymous
    October 26, 2008
    Unraveling the mysteries of NewSequentialID

  • Anonymous
    May 12, 2009
    Sequential GUID i SQL Server og i .NET

  • Anonymous
    May 25, 2009
    When creating client-side guids to be stored in SQL Server things are not as simple as you expect...

  • Anonymous
    June 07, 2009
    PingBack from http://weakbladder.info/story.php?id=387

  • Anonymous
    June 09, 2009
    PingBack from http://toenailfungusite.info/story.php?id=4164