SQL Server CLR Integration not calling system time as expected

A little context first, then the specific questions will follow.

I am trying to generate sequential GUIDs for use as primary ids in a SQL Server 2008 R2 table. Previously, these were being generated by a user defined function that performed too many string operations to generates sequential GUIDS and it was a performance bottleneck.

To try and decrease the time of this function, I am attempting to use CLR Integration and do the operations within a C# class. The sequentialization of the GUID is being done by updating the last 12 bytes of a GUID to values generated from the system date time.

In the initial version, the GetUTCDate() SQL Server system function was being used to get the date time and convert it to ticks which was then used to create the sequential 12 bytes of a guid. My approach was to use the .NET Framework DateTime.UtcNow.Ticks property to be converted to the last 12 bytes, but I noticed that the Ticks property was not being updated as expected and this indicated the system time function was not being called as often as I expected.

To test this, I created a simple method which returns the Ticks:

[SqlFunction()]
public static long GetTicks()
{
  return DateTime.UtcNow.Ticks;
}

This assembly for this method was then created as an ASSEMBLY object in SQL Server; note that CLR integration was enabled and the assembly was created in UNSAFE mode thus allowing access to OS APIs. I then created a function in SQL Server and for this CLR function:

CREATE FUNCTION GetTicks() RETURNS BIGINT 
AS EXTERNAL NAME Sequential.Generator.GetTicks; 
GO

I then performed the following two statements with the results shown below

SELECT [dbo].[GetTicks]();
SELECT [dbo].[GetTicks]();

The results were:

What this shows is that sequential calls to the CLR Function returned the same value of TICKS. This implies the underlying OS call was not being performed to return ticks for each invocation of the GetTicks() function. I then performed a second test by creating a simple table like so:

CREATE TABLE #tmp1(
     [NUM] [INT] NULL,
     [TICKS] BIGINT
);

I populated the table with 300 rows, then executed the following statement:

UPDATE #tmp1
SET [ticks] = [Play].[dbo].[GetTicks]();

I then measured the difference between [ticks] value of consecutive rows and there was only a single change of values out of all 300 rows (done in Excel):

Again, this indicates the Framework DateTime.UtcNow.Ticks property was not being called for each invocation of the CLR Integrated function GetTicks().

Is there a way to enable CLR Integration such that calls to the underlying OS can performed with each invocation of a function from SQL Server?
Is SQL CLR Integration an unwise approach for SQL Server to retrieve the system time from the OS on a basis more frequent than the GetUTCDATE() T-SQL function?

Thanks!

(*) Note, the sequential GUIDS are a requirement, I am simply trying to maximize performance of the way they are generated. Additionally, the NEWSEQUENTIALID() function is not acceptable because of the privacy issues.

Again, this indicates the Framework DateTime.UtcNow.Ticks property was not being called for each invocation of the CLR Integrated function GetTicks().

No, it does not indicate that the UtcNow property wasn't being called. The issue is the resolution of System.DateTime within .NET. Regardless of how precise the value is, the value only increments every 10 - 16 milliseconds (similar to how GETDATE() in T-SQL only updates every 3 milliseconds). This is why the value stays constant for a certain number of rows, but not across all rows like the T-SQL datetime functions do (e.g. GETDATE(), etc).

Is there a way to enable CLR Integration such that calls to the underlying OS can performed with each invocation of a function from SQL Server?

The "CLR Integration" feature is either "enabled" or "disabled". There is no configuration of it beyond that. As mentioned directly above, this is an issue with the frequency at which the value of DateTime.UtcNow.Ticks gets updated.

Is SQL CLR Integration an unwise approach for SQL Server to retrieve the system time from the OS on a basis more frequent than the GetUTCDATE() T-SQL function?

Not nearly as unwise as using a GUID / UNIQUEIDENTIFIER as a PK (I assume it is the Clustered Index), which in itself is not nearly as unwise as trying to alleviate the performance problems resulting from that decision by then introducing a non-built-in function.

How is the current UDF being called to generate the value? INSERT Trigger?

If performance is an issue, then make the PK an INT or BIGINT and use IDENTITY. Keep the GUID field, but make it an "alternate key" by adding a NonClustered index to it. App code can use the GUID value, but JOINs, etc should use the INT / BIGINT PK.

But, as far as getting a more frequently changing value for DateTime.UtcNow.Ticks goes, starting in Windows 8 / Windows Server 2012, there is a new OS function, GetSystemTimePreciseAsFileTime, that will work for this. Normally I would add in, "unfortunately it requires that the Assembly be marked as UNSAFE" because that should be avoided unless absolutely necessary, but you already mentioned having your Assembly marked as UNSAFE so this presents no change to that.

In order to use this new function, just grab the code from here:

High Resolution Clock in C#

and then replace:

DateTime.UtcNow.Ticks

with:

HighResolutionDateTime.UtcNow.Ticks

!! PLEASE NOTE: SQL Server does not guarantee the number of times a function will be called. Meaning, it is possible, even if highly unlikely, that multiple calls to this function (or any function, including NEWID()) will return the same value, even if specifically declared as IsDeterministic = false. However, this holds true for T-SQL UDFs as well, so if the process is currently working with a T-SQL UDF, then it should continue to work once that is switched out for a SQLCLR scalar function.

Recommended topics

Hot tags