SQL Server 2005: High definition timer (technical)

Timing with extreme precision

I) Preface

I played with writing a generic metadata repository (prototyping) to prepare for ETL at Nortura. I'm not doing the meta data design, but want to understand what we talk about, and to get some experience it's one of two ways I could think of doing it:

1. Adaption - "Monkey see monkey do": Watch other do stuff and try to copy the submodalities (did I write this)
   
2. Brute force - "Trial and error and success": Try, fail, try again, fail a little less, try again, succeed.
   

I want to do both.

a) Brute force

I started with the hands-on and created a generic meta data table with support for versioning (of the model) and hierarchy:
(
GenMetumId
,GenMetumType
,GenGrupperendeMetumId
,GenMetumForrigeId -- GenMetumPreviousId
,GenMetumGyldigFra -- GenMetumValidFrom
,Navn -- Name
,EkstraXml
,StartTid -- StartTime
,SluttTid -- EndTime
,Suksess -- Success
,Nyeste -- Newest
)
then I created some stored procedures and tried to insert some rows and update them with status etc. surprise surprise:

The timestamps were mostly equal!

BOL reveals that datetime has a resolution of some 3 ms, but this does not account for the results I got. My resolution was 10-15 ms, which to me is an etarnity (I used to do low-level stuff)

This needs to be fixed. By me. Off hours. (as in hobby).
See solution in point II).

b) Adaption

I am so lucky that I am to implement ETL on one of Europes largest BI initiatives, so I'll get to be good with operational or technical meta data (I'm not versed in the jargon, yet)

 

II) Implementation (my favourite)

(I know design preceedes implementation in real life, but we're playing here)

 

a) A better .NET "DateTime.Now"

I know DateTime.Now is sufficient (10M ticks per sec). but we are playing, so let's do it properly.

WINAPI QueryPerformanceCounter

I've written some timing routines far back for Windows CE, because the .NET timer routines does not work very well on Pocket PC and Smartphone (resolution is not good). QueryPerformanceCounter and QueryPerformanceFrequency are the WINAPI methods of reading the OEM high resolution counter, which is the lowest level of time-taking on modern computers.

#region "Timer routines"
[DllImport("kernel32.dll")] static extern public bool QueryPerformanceCounter(out UInt64 i64);
[DllImport("kernel32.dll")] static extern public bool QueryPerformanceFrequency(out UInt64 i64);
static protected UInt64 i64Freq = 0;
public static UInt64 GetNowI64()
{
UInt64 i64;
if (!QueryPerformanceCounter(out i64))
i64 = 0;
return (i64 * 10000000) / GetFreq();
}
public static UInt64 GetFreq()
{
if (0 == i64Freq)
if (!QueryPerformanceFrequency(out i64Freq))
i64Freq = 1;
return i64Freq;
}
protected static DateTime dtBaseline = DateTime.MinValue;
protected static UInt64 i64Baseline = 0;
public static DateTime GetNowDt()
{
UInt64 i64Now = GetNowI64();
DateTime dt;
if (dtBaseline == DateTime.MinValue i64Baseline == 0 i64Baseline > i64Now)
{ // We have no reference point, or reference point is no longer sufficient: create one.
dtBaseline = DateTime.Now;
i64Baseline = i64Now;
dt = dtBaseline;
}
else
{ // We have a sufficient baseline, return baseline plus timespan for diff
dt = new DateTime(dtBaseline.Ticks + (Int64)(i64Now - i64Baseline));
}
return dt;
}
#endregion "Timer routines"

Converting these routines to SQL CLR is a non-brainer.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime GetTime()
{
//return (SqlDateTime)DateTime.Now;
return (SqlDateTime)GetNowDt();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetTimeAsString()
{
return (SqlString)(GetNowDt().ToString("O"));
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetFrequency()
{
return (SqlInt64)(Int64)GetFreq();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetTickCount()
{
UInt64 i64;
if (!QueryPerformanceCounter(out i64))
i64 = 0;
return (SqlInt64)(Int64)i64;
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetMs()
{
return (SqlInt64)(Int64)GetNowI64();
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetTicksPast(Int64 zero)
{
UInt64 uiRes = GetNowI64() - (UInt64)zero;
return (SqlInt64)(Int64)uiRes;
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 GetMicrosPast(Int64 zero)
{
UInt64 uiRes = GetNowI64() / 10;
uiRes -= (UInt64)zero;
return (SqlInt64)(Int64)uiRes;
}

 

SQL Server import:

CREATE ASSEMBLY MeasureTime AUTHORIZATION [dbo] FROM ... WITH PERMISSION_SET = UNSAFE
CREATE FUNCTION Meta.GetFrequency() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetFrequency
CREATE FUNCTION Meta.GetMicrosPast(@zero bigint) RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetMicrosPast
CREATE FUNCTION Meta.GetMs() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetMs
CREATE FUNCTION Meta.GetTickCount() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTickCount
CREATE FUNCTION Meta.GetTicksPast(@zero bigint) RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTicksPast
CREATE FUNCTION Meta.GetTime() RETURNS datetime AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTime
CREATE FUNCTION Meta.GetTimeAsString() RETURNS nvarchar(MAX) AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTimeAsString

Testing with some simple queries shows me that it worked. (hope link to image is not broken)



Green arrows
These demonstrates that you can get a higher granularity on timestamps than using GetDate() with Meta.GetTime(). GetDate jumps from ...573 to ...587, while Meta.GetTime() jumps 2, 4 and 6 ms. Two other facts stands out: times differ, so .NET-time seams not equal to T-SQL time, update of 4ms means that the granularity of datetime can't be exactly 3ms.

Blue arrows
GetTicksPast() (10 000 000 pr sec) and GetMicrosPast() (1 000 000 pr sec) works as expected. Calling CLR, getting the time and putting it into a cell in temp looks to take less than 50 microseconds or less than 1/20000 second, which is faster than I expected (but we have very desent laptops).

Red arrows
Putting the same functions multiple times on the same SELECT ensured me that they are called every time (without caching). I particulary liked GetTimeAsString() which measured time in readable format exact with seven digits after secounds... higher granularity than GetDate(), no?

III) What's the business case
You tell me, I just needed to write som code and wanted to share the result.

IV) Where is the code
http://www.codeplex.com/SqlClrMeasureTime
this got kind of nerdy, next one will be better
have a nice week end

Gorm Braarvig