Query timeout when polling querying group 'longest_queries' in Microsoft SQL Server extension

[Leonardo_Val]

Hi!

I'm helping a client to monitor an Azure SQL Database (DTU) with the Microsoft SQL Server extension.

All the metrics that are supported are polling without problems except for the "longest_queries".

In the extension logs (of the platform) a log like the following shows everytime the request is made:

"Endpoint <mydatabaseendpoint> group 'longest_queries' did not produce any events"

Also in the ActiveGate server, in the Microsoft SQL Server extension logs a log like the following is showing.

While polling from endpoint <mydatabaseendpoint>, querying group 'longest_queries' failed

[...][...][83609][out]java.sql.SQLTimeoutException: The query has timed out.

I checked with my client and the Query Store is enabled in the database in question, they also say that they dont have a timeout defined but i continue to investigate this last point.

I'm a little confused because of this behaivour of the extension even when (I think) I have all the prerrequisites accomplished, so, I would like to know if anyone has crossed with something like this in the past?

Thanks!

[vagiz_duseev]

Hi, 

The timeout is defined (hard-coded) in the ActiveGate and is set to 30 seconds. The ActiveGate's extensions module expects the query to finish within this timeout limit.

Here is the query itself:

SELECT TOP (10)
  UPPER(
    ISNULL(
      CONVERT(sysname, SERVERPROPERTY('InstanceName')),
      'MSSQLSERVER'
    )
  )                                     AS instance_name,
  LEFT(
    CONVERT(sysname, SERVERPROPERTY('ServerName')),
    ISNULL(
      NULLIF(
        CHARINDEX('\', CONVERT(sysname, SERVERPROPERTY('ServerName'))) -1,
        -1
      ),
      LEN(CONVERT(sysname, SERVERPROPERTY('ServerName')))
    )
  )                                     AS server_name,
  p.query_id                            AS query_id,
  p.query_plan                          AS query_plan,
  q.object_id                           AS object_id,
  ISNULL(OBJECT_NAME(q.object_id), '')  AS object_name,
  qt.query_sql_text                     AS query_sql_text,
  ROUND(
    CONVERT(
      float,
      SUM(rs.avg_duration * rs.count_executions)
    ) * 0.001,
    2
  )                                     AS total_duration,
  ROUND(
    CONVERT(
      float,
      SUM(rs.avg_duration)
    ) * 0.001,
    2
  )                                     AS avg_duration,
  SUM(rs.count_executions)              AS num_executions,
  COUNT(distinct p.plan_id)             AS num_query_plans
FROM
  sys.query_store_runtime_stats rs
  JOIN sys.query_store_plan p
    ON p.plan_id = rs.plan_id
  JOIN sys.query_store_query q
    ON q.query_id = p.query_id
  JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
WHERE
  DATEADD(
    hour,
    DATEDIFF(hour, rs.last_execution_time, GETUTCDATE()),
    rs.last_execution_time
  ) >= DATEADD(minute, -5, GETUTCDATE())
  AND DATEADD(
    hour,
    DATEDIFF(hour, rs.first_execution_time, GETUTCDATE()),
    rs.first_execution_time
  ) <= GETUTCDATE()
GROUP BY
  p.query_id,
  qt.query_sql_text,
  q.object_id,
  p.query_plan
HAVING
  COUNT(distinct p.plan_id) >= 1
ORDER BY
  total_duration DESC

All suggestions on how to tweak it in such a way that it completes in less than 30 sec are welcome!

[Leonardo_Val]

Thanks for your reply @vagiz_duseev !

I will try to adjust the query directly in the sql server management studio of my client so I can see if anything else is happening during the query processing.

[rudy_h]

Hi @vagiz_duseev ,

I was installing SQL Server Extension and found 2 minutes Lock Wait Time using data explorer. I did not find any analysis from Davis in the top right corner. Is it because of 30 seconds timeout? Or should I configure how Davis to provide problem analysis? Or is it only working in Dynatrace SaaS for Davis to provide problem analysis? I was using Dynatrace Managed