Identifying and Troubleshooting Long-Running SQL Queries in SQL Server

 

Identifying and Troubleshooting Long-Running SQL Queries in SQL Server

Slow running queries can drag down application performance and degrade user experience. As a DBA, it's important to monitor and optimize long running SQL statements.

In this post, I'll demonstrate how to find queries that have been executing for over 1 minute in SQL Server, using DMVs like sys.dm_exec_requests and sys.dm_exec_query_stats.

By querying these DMVs and joining to sys.dm_exec_sql_text, we can retrieve the full text of long running queries. Important details like total elapsed time, CPU time, reads and more are also returned.

The T-SQL code below filters for queries running longer than 1 minute and returns useful troubleshooting columns like the complete query plan. Reviewing this data helps identify the root cause like missing indexes, inefficient joins, repetitive scans etc.

Copied!
/*https://www.dbascrolls.com*/

DECLARE @time_threshold INT = 60000; -- 1 minute in milliseconds

SELECT 
    r.session_id,
    DB_NAME(r.database_id) AS dbname,
    s.login_name,
    r.blocking_session_id,
    r.status,
    s.program_name,
    s.host_name,
    r.transaction_isolation_level,
    r.percent_complete,
    r.start_time,
    r.total_elapsed_time / 1000.0 AS total_elapsed_seconds,
    r.wait_type,
    r.last_wait_type,
    r.command,
    r.cpu_time,
    r.reads,
    r.writes,
    r.logical_reads,
    t.text AS query_text,
    qp.query_plan
FROM 
    sys.dm_exec_requests r
JOIN 
    sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY 
    sys.dm_exec_query_plan(r.plan_handle) qp
WHERE 
    r.total_elapsed_time > @time_threshold -- queries running longer than 1 minute
ORDER BY 
    r.total_elapsed_time DESC;


Optimizing or tuning the longest running queries provides an easy "quick win" to improve performance. Bookmark this script to regularly check for potential problem queries in your system!
Lince Sebastian

Indian MSSQL DBA thriving in database management - ensuring efficiency and smooth operations. Devoted father of two and avid Clash of Clans player, driven by strategic thinking. Football fuels my passion - cheering Kerala Blasters and Arsenal. I share my professional and life insights through my blog.

*

إرسال تعليق (0)
أحدث أقدم