How can we help you today? How can we help you today?

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

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,


ssh4478
0

Comments

1 comment

Sort by
  • 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
    Alex B
    0

Add comment

Please sign in to leave a comment.