DX Unified Infrastructure Management

 View Only
  • 1.  Long running SQL query monitoring

    Posted Feb 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 Feb 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;