Long running queries reported since installing 12.1.43 from the server where sql monitor is installe

ssh4478

Hello,

I have upgraded the sql monitor 12.1.43 since then long running queries is reported from the servers (nodes which are monitored by SQL monitor) as below:

2/2/2022 1:31:58 PM -> :

[Top SQL]:

SPID: 74

Event Class: Running

Host: nhsuk-p-dbm01

Application: SQL Monitor - Monitoring

Database: tempdb

Login: NHSCHOICES\svcSQLprod

CPU: 16

Reads: 1297

Writes: 0

Text Data:

--RedGateIgnore

SET NOCOUNT ON;

IF OBJECT_ID('[tempdb]..[redgate_sqlmonitor_SprocStats_b_NJ3c9jo_aVgUrnB_xxQtsteASSo]', 'U') IS NOT NULL

BEGIN

    DROP TABLE [tempdb]..[redgate_sqlmonitor_SprocStats_a_NJ3c9jo_aVgUrnB_xxQtsteASSo]

END

CREATE TABLE [tempdb]..[redgate_sqlmonitor_SprocStats_a_NJ3c9jo_aVgUrnB_xxQtsteASSo] (

        object_id int NOT NULL,

        plan_handle varbinary(64) NOT NULL,

        dbname nvarchar(450) NOT NULL,

        object_name nvarchar(450) NOT NULL,

        sql_handle varbinary(64) NOT NULL,

        object_type char(2) NOT NULL,

        execution_count bigint NOT NULL,

        total_worker_time_ms bigint NOT NULL,

        total_physical_reads bigint NOT NULL,

        total_logical_writes bigint NOT NULL,

        total_logical_reads bigint NOT NULL,

        total_elapsed_time_ms bigint NOT NULL,

        last_execution_time DATETIME NOT NULL

)

CREATE CLUSTERED INDEX [redgate_sqlmonitor_SprocStats_b_index_NJ3c9jo_aVgUrnB_xxQtsteASSo] ON [tempdb]..[redgate_sqlmonitor_SprocStats_a_NJ3c9jo_aVgUrnB_xxQtsteASSo] (sql_handle, dbname)

IF OBJECT_ID('[tempdb]..[redgate_sqlmonitor_SprocStats_a_NJ3c9jo_aVgUrnB_xxQtsteASSo]', 'U') IS NOT NULL

BEGIN

    DROP TABLE [tempdb]..[redgate_sqlmonitor_SprocStats_b_NJ3c9jo_aVgUrnB_xxQtsteASSo]

END

CREATE TABLE [tempdb]..[redgate_sqlmonitor_SprocStats_b_NJ3c9jo_aVgUrnB_xxQtsteASSo] (

        object_id int NOT NULL,

        plan_handle varbinary(64) NOT NULL,

        dbname nvarchar(450) NOT NULL,

        object_name nvarchar(450) NOT NULL,

        sql_handle varbinary(64) NOT NULL,

        object_type char(2) NOT NULL,

        execution_count bigint NOT NULL,

        total_worker_time_ms bigint NOT NULL,

        total_physical_reads bigint NOT NULL,

        total_logical_writes bigint NOT NULL,

        total_logical_reads bigint NOT NULL,

        total_elapsed_time_ms bigint NOT NULL,

        last_execution_time DATETIME NOT NULL

)

CREATE CLUSTERED INDEX [redgate_sqlmonitor_SprocStats_a_index_NJ3c9jo_aVgUrnB_xxQtsteASSo] ON [tempdb]..[redgate_sqlmonitor_SprocStats_b_NJ3c9jo_aVgUrnB_xxQtsteASSo] (sql_handle, dbname)

WHILE (1 = 1) WAITFOR DELAY '00:00:30'

----------------------------------------------------------------------

[Connection]:  NHSUK-P-DB03.NHSCHOICES.LOCAL\USDB

[Object Name]:  Top SQL

[Message]:  Top SQL Exceeded Max Runtime

[Start Time]:  12/2/2022 1:31:58 PM

[Duration]:  14 minutes, 9 seconds

[Max Allowed Runtime]:  10 minutes, 0 seconds (explicit threshold)

[Average Runtime]:  1 hour, 35 minutes, 52 seconds

----------------------------------------------------------------------

[Top SQL]:

url:sqlsentry:nhsuk-p-dbm02\MONITOR/SQLSentry/pa/ts/?c=73&id=173597660

----------------------------------------------------------------------

[Timestamp (Local)]:  12/2/2022 1:46:08 PM

[Timestamp (UTC)]:  12/2/2022 1:46:08 PM

[Generated By]:  SentryOne 20.0 Server [NHSUK-P-DBM02]

[Version]:  20.0.0.0

[Monitor Type]:  SqlServerProfiler

[Condition]:  SQL Server: Top SQL: Duration Threshold Max

[Response Ruleset]:  RishiTest

[Configured Object Name]:  Global

[Configured Object Type]:  Global

----------------------------------------------------------------------

[Other Active Events]:  NONE

----------------------------------------------------------------------

How can I stop them, I have restarted the SQL Monitor instance to which SQL monitor is connected to store the related DB objects but no success.

Thanks,

Alex B

Hi @ssh4478,

This is not a problem, the sampler is keeping the connection open similarly to one of the possible methods used by the TopQueries sampler.  Having said that, the team have looked into whether it needs to do so or not and should be making a change so it is no longer kept open.

So it is not a problem that it is long running, but it may not be necessary and so the team are addressing that.

Kind regards,
Alex