How to observe the execution time of a determined query on a MSSQL server

Grafana MSSQL
1

Hello.

I am new to Grafana and all that ecosystem of related tools, like Prometheus, etc.

I need to observe the execution time of a determined query on a MSSQL server.
Unfortunately, after trying for a couple of days, I still cannot find a way on how to do it.
I took a look at the Microsoft SQL Server datasource, but again, no luck.

Could anybody help me?

Thank you and best regards.
Roberto

  • What Grafana version and what operating system are you using?
    Grafana: 9.4.3
    Ubuntu 20.04.5 LTS

  • What are you trying to achieve?
    Observe the execution time of a determined query on a MSSQL server.
    The query is to be executed once every hour, and I want to see a graph of query execution time in function of time of execution, for instance a period of time of 10 days.

  • How are you trying to achieve it?
    Through the Microsoft SQL Server data source

  • What happened?
    I cannot get it to work

  • What did you expect to happen?
    I’d like to see a graph with time of executin on the horizontal axis and the query execution time on the vertical axis.

  • Can you copy/paste the configuration(s) that you are having problems with?
    Here’s my MSSQL query as a timeseries
    use MYDB
    DECLARE @t1 DATETIME;
    DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
select * from mytable;
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS time;

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    Several tries, several errors… last is ‘db has not time column’
2

Hi nunni, did you find out how to collect sql query execution time?

3

Hi.

Yes. Here’s how I did it:
https://groups.google.com/g/prometheus-users/c/Rp6iZfuvniI/m/nwAhHk40AwAJ

Best regards.

4

It is also doable with pure sql server. prometheus seems a bit of an overkill for a simple query.

/*
create table querystats(
  queryname varchar(50), 
  query_date datetime, 
  query_duration integer
  )
*/
use grafana
go


DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
select * from products;
SET @t2 = GETDATE();

insert into querystats
SELECT 'products', getutcdate(), DATEDIFF(millisecond,@t1,@t2) AS time

select * From querystats

image
image1418×751 46.5 KB