How to Find Long-Running Queries in SQL Server

How to Find Long-Running Queries in SQL Server

Identifying long-running queries is a crucial aspect of performance tuning and diagnostics in SQL Server. With the right tools and techniques, it's possible to detect both currently executing slow queries and those that have historically consumed excessive resources. This guide covers multiple approaches using built-in Dynamic Management Views (DMVs), the Query Store, and external tools.

 

1. Identifying Currently Executing Long Queries

The sys.dm_exec_requests DMV reveals real-time insights into executing queries.

SELECT

    session_id,

    start_time,

    status,

    command,

    blocking_session_id,

    wait_type,

    wait_time,

    last_wait_type,

    cpu_time,

    total_elapsed_time AS duration_ms,

    reads,

    writes,

    logical_reads,

    ROW_COUNT,

    granted_query_memory,

    text AS query_text,

    query_plan

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp

WHERE r.session_id <> @@SPID

  AND r.status = 'running'

  AND r.total_elapsed_time > 1000

ORDER BY duration_ms DESC;

Key Columns

• session_id: Active session ID.
• start_time: When the query began.
• status: Execution status.
• wait_type, wait_time: Useful for diagnosing bottlenecks.
• cpu_time, total_elapsed_time: Indicate resource usage.
• reads, writes, logical_reads: I/O stats.
• query_text, query_plan: Essential for query diagnosis.

 

2. Reviewing Historically Slow Queries

The sys.dm_exec_query_stats DMV helps identify queries that consistently perform poorly.

SELECT TOP 50

    DB_NAME(st.dbid) AS database_name,

    OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,

    OBJECT_NAME(st.objectid, st.dbid) AS object_name,

    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,

              ((CASE qs.statement_end_offset

                WHEN -1 THEN DATALENGTH(st.text)

                ELSE qs.statement_end_offset

               END - qs.statement_start_offset) / 2) + 1) AS statement_text,

    qs.creation_time,

    qs.last_execution_time,

    qs.execution_count,

    (qs.total_worker_time / 1000) AS total_cpu_time_ms,

    (qs.total_elapsed_time / 1000) AS total_elapsed_time_ms,

    qs.total_logical_reads,

    qs.total_physical_reads,

    (qs.total_elapsed_time / qs.execution_count / 1000) AS avg_elapsed_time_ms,

    (qs.total_worker_time / qs.execution_count / 1000) AS avg_cpu_time_ms,

    qp.query_plan

FROM sys.dm_exec_query_stats 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

ORDER BY avg_elapsed_time_ms DESC;

Use Cases

• Identify queries with high avg_elapsed_time_ms.
• Monitor those with frequent executions or heavy reads.
• Review query plan details for optimization opportunities.

 

3. Using sp_WhoIsActive for Real-Time Monitoring

sp_WhoIsActive, a third-party stored procedure, offers rich, real-time diagnostics.

Setup

• Download from Brent Ozar’s site: WhoIsActive
• Install in the master database.

Sample Usage

EXEC sp_WhoIsActive

    @get_full_info = 1,

    @get_additional_info = 1,

    @get_plans = 1,

    @get_transaction_info = 1,

    @find_block_leaders = 1;

Advantages

• Displays wait types, duration, blocking chains, CPU, reads/writes.
• Outputs graphical query plan links and session context.
• Excellent for production diagnostics without heavy overhead.

 

4. Leveraging Query Store (SQL Server 2016+)

Query Store provides historical query insights within the database context.

Enabling Query Store

ALTER DATABASE YourDatabaseName

SET QUERY_STORE = ON;

 

ALTER DATABASE YourDatabaseName

SET QUERY_STORE (

    OPERATION_MODE = READ_WRITE,

    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

    MAX_STORAGE_SIZE_MB = 1000,

    QUERY_CAPTURE_MODE = AUTO

);

SSMS Reports

• Top Resource Consuming Queries: Highlights long-running or resource-heavy queries.
• Regressed Queries: Detects recent performance drops.
• Overall Resource Consumption: Gives a high-level performance snapshot.

 

5. Using Profiler and Extended Events

SQL Server Profiler

Profiler is useful for ad-hoc analysis but incurs overhead and is deprecated for regular use.

Extended Events

This is the modern replacement for Profiler, with better performance and customization.

Example:

• Track sql_statement_completed or rpc_completed with filters on duration.
• Output to files or ring buffer for later analysis.

 

Practical Workflow for Long Query Analysis

1. Initial Identification
• Use sys.dm_exec_requests or sp_WhoIsActive to detect current issues.
2. Historical Analysis
• Analyze sys.dm_exec_query_stats or Query Store for long-term patterns.
3. Execution Plan Review
• Always retrieve execution plans for problem queries.
• Look for red flags: scans, key lookups, missing indexes, sort warnings.
4. Optimization
• Apply indexing, query rewrites, or statistical updates based on plan findings.
5. Validation
• Re-monitor after changes using the same tools to ensure improvements are effective.

 

Summary

Detecting and resolving long-running SQL Server queries involves a combination of real-time and historical analysis using DMVs, Query Store, and optionally tools like sp_WhoIsActive or Extended Events. Effective performance tuning requires methodical analysis, execution plan interpretation, and continuous monitoring.