How does the sqlserver probe's "long_queries" checkpoint work?

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

No alarm is generated despite long query which passes defined threshold or the alarm is generated and cleared quickly.

Environment

• Component: UIMSQS
• sqlserver 5.42 or higher

Resolution

After further testing and re-examining this checkpoint-> long_queries, it appears that it is only applicable to SQL Server 2005 which is very old.

***Long running queries works with SQL Server 2005 ONLY.***
See: http://support.nimsoft.com/unsecure/archive.aspx?id=89

Existing underlying query statement:

SELECT r.session_id spid, s.host_process_id hostid, s.host_name, s.login_name loginname, 
s.program_name, (convert(dec(15,3),r.cpu_time))/1000 cpu_time, (convert(dec(15,3),r.total_elapsed_time))/1000 elapsed_time,
convert(varchar(3000),isNull(SUBSTRING (st.text,1,3000),'n/a')) as sql_text
FROM sys.dm_exec_sessions s, sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st
where  s.session_id = r.session_id and r.status = 'running' and s.session_id <> @@SPID

Instead, if you do need this checkpoint you can set up a custom checkpoint 
https://knowledge.broadcom.com/external/article/39422/how-to-create-a-new-custom-checkpoint-us.html

And use a query such as:

SELECT  st.text,
        qp.query_plan,
        qs.*
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
      OR qs.max_elapsed_time > 300

or this query:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO

• Make sure when you test it that your query runs significantly longer than the threshold you have defined for long queries and that your monitoring interval is frequent enough to pick it up as well. In other words, if you're only checking every 5 minutes, a long-running query of 3 minutes might get missed by the probe.