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.