Why Finding Your Longest Running Queries Matters

The fastest way to find longest running queries in SQL Server is to query the sys.dm_exec_query_stats dynamic management view (DMV), joined to sys.dm_exec_sql_text. This gives you execution statistics for every cached query plan, including average elapsed time, logical reads, and execution count - all without touching a profiler or waiting for a slow period to reproduce itself.

Performance problems in SQL Server almost always trace back to a small number of queries doing a disproportionate amount of work. In most environments, the 80/20 rule applies: roughly 20% of queries account for 80% of the resource consumption. Identifying those queries quickly is the difference between a reactive, firefighting approach to database administration and a proactive one. If your application is sluggish, your server CPU is spiking, or your storage subsystem is under pressure, the answer is almost certainly sitting inside SQL Server's query statistics cache right now.

What Is sys.dm_exec_query_stats?

sys.dm_exec_query_stats is a system DMV that SQL Server maintains in memory, tracking cumulative execution statistics for every query plan currently in the procedure cache. It's been available since SQL Server 2005 and is documented in full on Microsoft Learn.

Each row in the DMV represents a single cached query plan. The statistics it holds are cumulative from the time the plan was compiled or last recompiled, not from the last time you looked. That distinction matters. A query that ran 10,000 times overnight will show a very different picture in total_elapsed_time versus total_elapsed_time / execution_count. Both numbers tell you something useful, but they tell you different things.

One important limitation to understand: the DMV only reflects what's currently in the plan cache. If SQL Server has been restarted, if a plan was evicted under memory pressure, or if DBCC FREEPROCCACHE has been run, historical data is gone. For persistent query performance tracking, you'll want Query Store enabled - but for a quick, right-now snapshot of what's running hot, this DMV is your first stop.

The Query to Find Longest Running Queries

The script below pulls the key performance metrics from sys.dm_exec_query_stats, calculates average elapsed time per execution, and extracts the actual statement text using sys.dm_exec_sql_text. Results are ordered by average elapsed time descending, so the worst offenders surface immediately.

SELECT
     creation_time
    ,last_execution_time
    ,total_physical_reads
    ,total_logical_reads
    ,total_logical_writes
    ,execution_count
    ,total_worker_time
    ,total_elapsed_time
    ,total_elapsed_time / execution_count AS avg_elapsed_time
    ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
        ((CASE statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

Run this against any SQL Server instance and you'll have a ranked list of your most expensive queries within seconds.

Understanding the Columns

Knowing what each column means helps you interpret the results correctly rather than just reacting to the top row.

creation_time - When the query plan was compiled and added to the cache. If this is recent, the plan may have been recompiled due to statistics changes or schema modifications.

last_execution_time - The last time this query ran. Useful for identifying queries that ran once, caused a spike, and haven't been seen since.

total_physical_reads - Physical reads hit the disk. High physical reads indicate data isn't being served from the buffer pool, which points to either insufficient memory or a query pulling far more data than it needs.

total_logical_reads - Logical reads are served from memory (the buffer pool). These are cheaper than physical reads but still generate CPU overhead. A query with millions of logical reads per execution is almost certainly missing an index or scanning more data than necessary.

total_logical_writes - Writes to the buffer pool, typically from modification operations. High write counts can indicate expensive update or insert patterns.

execution_count - How many times this plan has executed since it was compiled. Combined with total_elapsed_time, this gives you the average. A query averaging 5 seconds per execution but running 10,000 times a day is a far bigger problem than one that takes 30 seconds but runs once a week.

total_worker_time - CPU time consumed, in microseconds. Compare this against total_elapsed_time. If worker time is close to elapsed time, the query is CPU-bound. If elapsed time is significantly higher than worker time, the query is waiting on something - locks, I/O, or network.

avg_elapsed_time - The calculated average. This is the primary sort column and your starting point for identifying the longest running queries in your environment.

statement_text - The actual T-SQL statement. The SUBSTRING logic here is important. sys.dm_exec_sql_text returns the full batch text, but statement_start_offset and statement_end_offset in the DMV identify exactly which statement within that batch the statistics belong to. Without this extraction, you'd see the entire stored procedure or batch rather than the specific statement causing the problem.

What to Do With the Results

Finding the query is only the first step. Once you've identified your top offenders, the next actions depend on what the data shows.

High logical reads with low execution count - Look at the execution plan. You're likely missing a covering index or the query is performing a large scan where a seek would do.

High execution count with moderate elapsed time - Even a "fast" query becomes a problem at scale. A query taking 50 milliseconds that runs 50,000 times per hour is consuming nearly 42 minutes of cumulative server time every hour. Consider caching results at the application layer, or review whether the query frequency is actually necessary.

Large gap between worker time and elapsed time - The query is spending most of its time waiting. Check sys.dm_exec_requests and sys.dm_os_wait_stats to identify what it's waiting on. Blocking and lock contention are common culprits.

High physical reads - Review your memory allocation. If SQL Server's buffer pool is undersized for your working data set, you'll see consistently high physical reads across many queries. This is a configuration problem, not just a query problem.

Sorting by Other Metrics

The query above sorts by average elapsed time, which is the right default for finding longest running queries. But you can adjust the ORDER BY to answer different questions:

  • ORDER BY total_logical_reads DESC - Find the most I/O-intensive queries overall
  • ORDER BY total_worker_time DESC - Find the biggest CPU consumers
  • ORDER BY total_logical_reads / execution_count DESC - Find the most I/O-intensive queries per execution
  • ORDER BY execution_count DESC - Find the most frequently executed queries

Each view surfaces a different category of problem. A thorough performance review should look at all of them.

Key Takeaways

  • sys.dm_exec_query_stats gives you an immediate, ranked view of query performance without any tracing or profiler overhead - it's the fastest way to find longest running queries in SQL Server.
  • Always look at avg_elapsed_time (total divided by execution count), not just the raw totals. A rarely run query with high total time is a different problem from a high-frequency query with moderate per-execution cost.
  • Compare total_worker_time against total_elapsed_time to determine whether a slow query is CPU-bound or wait-bound. The remediation is completely different for each.
  • The DMV only reflects the current plan cache. Data is lost on restart or cache eviction. Enable Query Store for persistent, historical query performance data.
  • High physical reads usually point to a memory or indexing problem. High logical reads usually point to a missing index or inefficient query pattern.

Identifying your longest running queries is a foundational step in any SQL Server performance review, but interpreting the results and acting on them correctly requires experience across indexing, execution plans, wait statistics, and instance configuration. At DBA Services, our SQL Server health checks systematically analyse query performance, index efficiency, and resource utilisation across your environment, giving you a prioritised list of what to fix and why. If your team doesn't have the bandwidth or the deep SQL Server expertise to work through performance issues methodically, our managed DBA support gives you access to that expertise on demand. Get in touch to find out how we can help.