DX Infrastructure Manager

Expand all | Collapse all

Long running SQL query monitoring

Jump to Best Answer
  • 1.  Long running SQL query monitoring

    Posted 02-16-2017 06:11 AM

    Hi,

    Recently we experienced issue with DB performance. We found that some stored procedures and queries were running for more than 24 hours.

    Any way to monitor this situation. Means, how can I monitor SQL queries which are running for a long time.

    Thanks.



  • 2.  Re: Long running SQL query monitoring
    Best Answer

    Posted 02-16-2017 09:00 AM

    If this is MSSQL, the following query should work to show long running queries.

    SELECT TOP 100 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds
    , qs.total_elapsed_time / 1000000.0 AS total_seconds
    , qs.execution_count
    , SUBSTRING (qt.text,qs.statement_start_offset/2
    , (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query
    , o.name AS object_name
    , DB_NAME(qt.dbid) AS database_name
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN
    sys.objects o ON qt.objectid = o.object_id
    WHERE qt.dbid = DB_ID()
    ORDER
    BY average_seconds DESC;