I want some Moore

High precision time measuring in SQL Server 2005 with the help from CLR and unsafe code

Update: I've created a muliti connection version which can be found here.

Usually time measuring in sql server is done in 2 ways: with the help of GetDate() function or with SET STATISTICS TIME ON.

For doing hardcore performance analysis where any time reduction counts you need more precise measuring capabilities and the two mentioned are only accurate to a milisecond order.

In SQL Server 2000 there was no practical way to change this.

SQL server 2005 to the rescue. It has DMV's which hold time in microseconds which is better. but they can't be always used to measure the duration of the statement. You could use profiler... but for the sake of the argument lets say we don't want to :)

With the use of CLR integration we can import the .Net features like System.Diagnostics.StopWatch. You can see how in this post which acctually prompted the stuff you're reading.

I found this post on using High-Performance time in C# which uses two Win32 functions called QueryPerformanceCounter() and QueryPerformanceFrequency().

Timer frequencies on my computer:

System.Diagnostics.StopWatch = 3579545                QueryPerformanceFrequency = 3200150000

Physics 101: Higher frequency = better precision.

Some problems with QueryPerformanceCounter can be found here. So like everything else some testing is advisable.

So lets see some code.

The C# project is also called SqlHiPerfTimer.

SqlHiPerfTimer.cs:

using Win32Timer;

public partial class SqlHiPerfTimer
{    
    private static readonly HiPerfTimer hiPerf = new HiPerfTimer();

    [Microsoft.SqlServer.Server.SqlFunction]
    public static int TimerStart()
    {        
        hiPerf.Start();
        return 0;
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static int TimerStop()
    {
        hiPerf.Stop();
        return 0;
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static System.Data.SqlTypes.SqlDecimal TimerElapsedSeconds()
    {
        return (System.Data.SqlTypes.SqlDecimal)hiPerf.ElapsedSeconds;
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static System.Data.SqlTypes.SqlDecimal TimerElapsedMilliseconds()
    {
        return (System.Data.SqlTypes.SqlDecimal)hiPerf.ElapsedMilliSeconds;
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static long TimerFrequency()
    {
        return hiPerf.TimerFrequency;
    }   
}

HiPerfTimer.cs:

using System.Runtime.InteropServices;
using System.ComponentModel;
using System.Threading;

namespace Win32Timer
{
    internal class HiPerfTimer
    {
        [DllImport("Kernel32.dll")]
        private static extern bool QueryPerformanceCounter(out long lpPerformanceCount);

        [DllImport("Kernel32.dll")]
        private static extern bool QueryPerformanceFrequency(out long lpFrequency);

        private long startTime, stopTime;
        private long freq;

        // Constructor
        public HiPerfTimer()
        {
            startTime = 0;
            stopTime = 0;

            if (QueryPerformanceFrequency(out freq) == false)
            {
                // high-performance counter not supported 
                throw new Win32Exception();
            }
        }

        // Start the timer
        public void Start()
        {            
            startTime = 0;
            stopTime = 0;
            Thread.Sleep(0);
            QueryPerformanceCounter(out startTime);
        }

        // Stop the timer
        public void Stop()
        {
            QueryPerformanceCounter(out stopTime);
        }

        // Returns the duration of the timer (in seconds)
        public double ElapsedSeconds
        {
            get
            {
                return (double)(stopTime - startTime) / (double)freq;
            }
        }

        public double ElapsedMilliSeconds
        {
            get
            {
                return (double)(stopTime - startTime) / (double)freq / (double)1000;
            }
        }

        public long TimerFrequency
        {
            get { return freq; }
        }
    }
}

exec sp_configure 'clr enabled', 1
RECONFIGURE
go
create database testCLR
go
ALTER DATABASE testCLR SET TRUSTWORTHY ON
go
use testCLR
go

CREATE ASSEMBLY SqlHiPerfTimer 
FROM 'D:\Test\SqlHiPerfTimer.dll'
WITH PERMISSION_SET = UNSAFE 
GO

CREATE FUNCTION dbo.TimerStart()
RETURNS int
AS EXTERNAL NAME SqlHiPerfTimer.SqlHiPerfTimer.TimerStart
GO

CREATE FUNCTION dbo.TimerStop()
RETURNS int
AS EXTERNAL NAME SqlHiPerfTimer.SqlHiPerfTimer.TimerStop

GO
CREATE FUNCTION dbo.TimerElapsedSeconds()
RETURNS decimal(38, 20)
AS EXTERNAL NAME SqlHiPerfTimer.SqlHiPerfTimer.TimerElapsedSeconds

GO
CREATE FUNCTION dbo.TimerElapsedMilliSeconds()
RETURNS decimal(38, 20)
AS EXTERNAL NAME SqlHiPerfTimer.SqlHiPerfTimer.TimerElapsedMilliseconds

GO
CREATE FUNCTION dbo.TimerFrequency()
RETURNS bigint
AS EXTERNAL NAME SqlHiPerfTimer.SqlHiPerfTimer.TimerFrequency

go

declare @i int
select @i=0;
select dbo.TimerStart() as TimerStart
while @i < 1000
begin 
    select @i = @i + 1
end
select @i
select dbo.TimerStop() as TimerStop
select dbo.TimerElapsedSeconds() as TimerElapsedSeconds
select dbo.TimerElapsedMilliSeconds() as TimerElapsedMilliSeconds
select dbo.TimerFrequency() as TimerFrequency

go
DROP FUNCTION dbo.TimerStart
DROP FUNCTION dbo.TimerStop
DROP FUNCTION dbo.TimerElapsedSeconds
DROP FUNCTION dbo.TimerElapsedMilliSeconds
DROP FUNCTION dbo.TimerFrequency

go
DROP ASSEMBLY SqlHiPerfTimer
go
use master
drop database testCLR

Here i've used Unsafe code with P/Invoke so i had to use
ALTER DATABASE testCLR SET TRUSTWORTHY ON
You could use PERMISSION_SET = SAFE assembly code with signing the DLL and creating the asymetric key from it in SQL Server
if you don't want to do SET TRUSTWORTHY ON for a databse.

How to do that is well described in this post by Kent Tegels which is in my opinion a must read.

Print | posted on Wednesday, November 29, 2006 12:24 PM | Filed Under [ .Net SQL Server ]