I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Multi Connection High precision time measuring in SQL Server 2005

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)]
    public static SqlString ClearDictionary()
    {
        string count = spidTimers.Count.ToString();
        foreach (HiPerfTimer hiPerf in spidTimers.Values)        
            hiPerf.Stop();
        spidTimers.Clear();
        string retMessage = "Hi-perf Timers cleared: " + count.ToString();
        return (SqlString)retMessage;
    }

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

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

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

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

    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt64 TimerFrequency()
    {
        HiPerfTimer hiPerf;
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT @@SPID", conn);
            int spid = Convert.ToInt32(cmd.ExecuteScalar());
            if (spidTimers.ContainsKey(spid))
            {
                hiPerf = (HiPerfTimer)spidTimers[spid];
                return (SqlInt64)hiPerf.TimerFrequency;
            }
            else
                return (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

Print | posted on Saturday, December 02, 2006 9:46 PM | Filed Under [ .Net SQL Server ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET