The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,119 other followers

SQL Server: strange way of getting query statistics.

Posted by jpluimers on 2014/01/14

Last year, I had a very odd project at a client.

Their monitoring software was quite odd, and there was no time to create/test/implement a module for it doing SQL query performance measurement any better.

The odd software had two ways of looking at queries:

  • in a succeed/fail fashion
  • in a count(*) fashion

Don’t ask why it was that way, the monitor was hysterically grown.

So below is a small query script that does what the odd monitoring software can do: provide a select with rows indicating the query response time.

What is does is insert into the #sma temporary table a number of records depending on the query duration.
The partition here is 1 record per 125 milliseconds, aiming for four partitions (green, yellow, orange, red) in half a second.

Note the maximum accuracy is about 3.3 milliseconds.

The script is based on these SQL server features:

I might add a try/catch to fake a finally in case the #sma insert fails.

In due time, the monitoring tool will be extended so it can do timing measurements itself, and evaluate query outcomes other than COUNT.

declare @partition as int;
declare @start as datetime;
declare @finish as datetime;
declare @duration as int;
declare @index as int;

set @partition = 125;

select @start = getdate();

-- simulate big query
declare @delayperiod datetime
set @delayperiod = '00:00:01'
--set @delayperiod = '00:00:10'
waitfor delay @delayperiod

select @finish = getdate();

select @duration = datediff(millisecond, @start, @finish); /* max 20 hours plus change */
--select @duration; -- to view the partition

create table #sma (id int);

set @index = 0;

while (@index < @duration)
    insert into #sma values (0);
    set @index = @index + @partition;

select count(*) from #sma;

drop table #sma;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: