Mladen Prajdić Blog

In a previous post I've shown how to use High precision timer.

That method had a drawback of working for whole server not distiguishing between connections.

So if you ran the time measurement in 2 different windows in SSMS the times would be incorrect.

You'd get the correct time in the window which was run last.

This of course isn't very usefull for proper performace diagnosys.

So with little experimenting i've managed to fix the timer to work properly for each connection.

The code is self explanatory. For each function run I return the spid to which it belongs and i store the HiPerfTimer objects in a dictionary.

I've also added a function called ClearDictionary that amazingly clears the dictionary of al HiPerfTimers :)

so the fixed c# code looks like this:

SqlHiPerfTimer.cs:

using Win32Timer;
using System.Data.SqlTypes;
using System.Collections.Generic;
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class SqlHiPerfTimer
{
private static readonly Dictionary<int, HiPerfTimer> spidTimers = new Dictionary<int, HiPerfTimer>();
[SqlFunction(DataAccess = DataAccessKind.Read)]
<span class="kwrd">public</span> <span class="kwrd">static</span> SqlString ClearDictionary()
{
    <span class="kwrd">string</span> count = spidTimers.Count.ToString();
    <span class="kwrd">foreach</span> (HiPerfTimer hiPerf <span class="kwrd">in</span> spidTimers.Values)        
        hiPerf.Stop();
    spidTimers.Clear();
    <span class="kwrd">string</span> retMessage = <span class="str">&#34;Hi-perf Timers cleared: &#34;</span> + count.ToString();
    <span class="kwrd">return</span> (SqlString)retMessage;
}

[SqlFunction(DataAccess = DataAccessKind.Read)]
<span class="kwrd">public</span> <span class="kwrd">static</span> SqlString TimerStart()
{
    HiPerfTimer hiPerf;
    <span class="kwrd">int</span> spid = -1;
    <span class="kwrd">using</span> (SqlConnection conn = <span class="kwrd">new</span> SqlConnection(<span class="str">&#34;context connection=true&#34;</span>))
    {
        conn.Open();
        SqlCommand cmd = <span class="kwrd">new</span> SqlCommand(<span class="str">&#34;SELECT @@SPID&#34;</span>, conn);
        spid = Convert.ToInt32(cmd.ExecuteScalar());        
        <span class="kwrd">if</span> (!spidTimers.ContainsKey(spid))
        {
            hiPerf = <span class="kwrd">new</span> HiPerfTimer();
            spidTimers.Add(spid, hiPerf);
        }
        <span class="kwrd">else</span>
        {
            hiPerf = (HiPerfTimer)spidTimers[spid];
            hiPerf.Stop();
        }
    }
    <span class="kwrd">string</span> retMessage = <span class="str">&#34;Hi-perf Timer for SPID: &#34;</span> + spid.ToString() + <span class="str">&#34; started.&#34;</span>;
    hiPerf.Start();        
    <span class="kwrd">return</span> (SqlString)retMessage;
}

[SqlFunction(DataAccess = DataAccessKind.Read)]
<span class="kwrd">public</span> <span class="kwrd">static</span> SqlString TimerStop()
{
    HiPerfTimer hiPerf;
    <span class="kwrd">int</span> spid = -1;
    <span class="kwrd">using</span> (SqlConnection conn = <span class="kwrd">new</span> SqlConnection(<span class="str">&#34;context connection=true&#34;</span>))
    {
        conn.Open();
        SqlCommand cmd = <span class="kwrd">new</span> SqlCommand(<span class="str">&#34;SELECT @@SPID&#34;</span>, conn);
        spid = Convert.ToInt32(cmd.ExecuteScalar());
        <span class="kwrd">if</span> (spidTimers.ContainsKey(spid))
        {
            hiPerf = (HiPerfTimer)spidTimers[spid];
            hiPerf.Stop();
        }
    }
    <span class="kwrd">string</span> retMessage = <span class="str">&#34;Hi-perf Timer for SPID: &#34;</span> + spid.ToString() + <span class="str">&#34; stopped.&#34;</span>;
    <span class="kwrd">return</span> (SqlString)retMessage;
}

[SqlFunction(DataAccess = DataAccessKind.Read)]
<span class="kwrd">public</span> <span class="kwrd">static</span> SqlDecimal TimerElapsedSeconds()
{
    HiPerfTimer hiPerf;
    <span class="kwrd">using</span> (SqlConnection conn = <span class="kwrd">new</span> SqlConnection(<span class="str">&#34;context connection=true&#34;</span>))
    {
        conn.Open();
        SqlCommand cmd = <span class="kwrd">new</span> SqlCommand(<span class="str">&#34;SELECT @@SPID&#34;</span>, conn);
        <span class="kwrd">int</span> spid = Convert.ToInt32(cmd.ExecuteScalar());
        <span class="kwrd">if</span> (spidTimers.ContainsKey(spid))
        {
            hiPerf = (HiPerfTimer)spidTimers[spid];
            <span class="kwrd">return</span> (SqlDecimal)hiPerf.ElapsedSeconds;
        }
        <span class="kwrd">else</span>
            <span class="kwrd">return</span> (SqlDecimal)0;
    }
}

[SqlFunction(DataAccess = DataAccessKind.Read)]
<span class="kwrd">public</span> <span class="kwrd">static</span> SqlDecimal TimerElapsedMilliseconds()
{
    HiPerfTimer hiPerf;
    <span class="kwrd">using</span> (SqlConnection conn = <span class="kwrd">new</span> SqlConnection(<span class="str">&#34;context connection=true&#34;</span>))
    {
        conn.Open();
        SqlCommand cmd = <span class="kwrd">new</span> SqlCommand(<span class="str">&#34;SELECT @@SPID&#34;</span>, conn);
        <span class="kwrd">int</span> spid = Convert.ToInt32(cmd.ExecuteScalar());
        <span class="kwrd">if</span> (spidTimers.ContainsKey(spid))
        {
            hiPerf = (HiPerfTimer)spidTimers[spid];
            <span class="kwrd">return</span> (SqlDecimal)hiPerf.ElapsedMilliSeconds;
        }
        <span class="kwrd">else</span>
            <span class="kwrd">return</span> (SqlDecimal)0;
    }
}

[SqlFunction(DataAccess = DataAccessKind.Read)]
<span class="kwrd">public</span> <span class="kwrd">static</span> SqlInt64 TimerFrequency()
{
    HiPerfTimer hiPerf;
    <span class="kwrd">using</span> (SqlConnection conn = <span class="kwrd">new</span> SqlConnection(<span class="str">&#34;context connection=true&#34;</span>))
    {
        conn.Open();
        SqlCommand cmd = <span class="kwrd">new</span> SqlCommand(<span class="str">&#34;SELECT @@SPID&#34;</span>, conn);
        <span class="kwrd">int</span> spid = Convert.ToInt32(cmd.ExecuteScalar());
        <span class="kwrd">if</span> (spidTimers.ContainsKey(spid))
        {
            hiPerf = (HiPerfTimer)spidTimers[spid];
            <span class="kwrd">return</span> (SqlInt64)hiPerf.TimerFrequency;
        }
        <span class="kwrd">else</span>
            <span class="kwrd">return</span> (SqlInt64)0;
    }
}

}

Since function return types changed we must implement them like this:

CREATE FUNCTION dbo.ClearDictionary()
RETURNS nvarchar(4000)
AS EXTERNAL NAME SqlHiPerfTimer.SqlHiPerfTimer.ClearDictionary
GO
CREATE FUNCTION dbo.TimerStart()
RETURNS nvarchar(4000)
AS EXTERNAL NAME SqlHiPerfTimer.SqlHiPerfTimer.TimerStart
GO
CREATE FUNCTION dbo.TimerStop()
RETURNS nvarchar(4000)
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

To test if the timers work properly i've used this stored procedure that i've run in 5 windows in SSMS.

create proc spTestTime
as
select dbo.TimerStart() as TimerStart
waitfor delay '00:00:10'
select dbo.TimerStop() as TimerStop
select dbo.TimerElapsedMilliSeconds() as TimerElapsedMilliSeconds
select dbo.TimerElapsedSeconds() as TimerElapsedSeconds
select dbo.TimerFrequency() as TimerFrequency

When we're done with our analisys we should clear the HiPerfTimer dictionary:

select dbo.ClearDictionary()

I must say that this was a very fun excercise in SQL CLR integration