Queries hitting the 10 min timeout

queries_hitting_the_10min_timeout.sql

DECLARE @days        INT = 30;
DECLARE @timeout_s   INT = 600;
DECLARE @tolerance_s INT = 30;

WITH timeouts AS (
    SELECT
        query_hash,
        ISNULL(program_name, '(none supplied)')                       AS program_name,
        login_name,
        statement_type,
        allocated_cpu_time_ms,
        data_scanned_remote_storage_mb,
        submit_time,
        command
    FROM queryinsights.exec_requests_history
    WHERE submit_time >= DATEADD(DAY, -@days, GETUTCDATE())
      AND UPPER(status) LIKE 'CANCEL%'
      AND error_code = 3617
      AND end_time IS NOT NULL
      AND DATEDIFF(SECOND, submit_time, end_time) >= @timeout_s - 5
      AND DATEDIFF(SECOND, submit_time, end_time) <= @timeout_s + @tolerance_s
)
SELECT TOP 50
    query_hash,
    MAX(program_name)                                                 AS program_name,
    MAX(statement_type)                                               AS statement_type,
    COUNT(*)                                                          AS times_timed_out,
    COUNT(DISTINCT login_name)                                        AS distinct_logins,
    SUM(allocated_cpu_time_ms)                                        AS wasted_cpu_ms,
    CAST(SUM(allocated_cpu_time_ms) / 3600000.0 AS DECIMAL(18, 2))    AS wasted_cpu_hours,
    CAST(AVG(CAST(data_scanned_remote_storage_mb AS FLOAT))
         AS DECIMAL(18, 1))                                           AS avg_remote_mb_scanned,
    MIN(submit_time)                                                  AS first_seen,
    MAX(submit_time)                                                  AS last_seen,
    LEFT(MAX(command), 400)                                           AS sample_command
FROM timeouts
GROUP BY query_hash
ORDER BY wasted_cpu_ms DESC
OPTION (LABEL = 'QI_WASTE_05_OFFENDING_STATEMENTS');