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; }
}
}
}
SQL Server 2005:
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.