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:
- GetDate()
- DateDiff()
- Local temporary tables
- while loops
- T-SQL variables
- A max query duration of about 24 days
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)
begin;
insert into #sma values (0);
set @index = @index + @partition;
end;
select count(*) from #sma;
drop table #sma;
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment