How to Find Your Slowest Stored Procedures in SQL Server

Tuning the wrong procedure is wasted effort. Before you optimize anything, rank your procedures by real cost — these queries do exactly that, and they run on any SQL Server 2008 or later with no extra tooling.

Rank procedures by average duration

The fastest way to find slow procedures is to read what has already run. sys.dm_exec_procedure_stats aggregates cached execution statistics per procedure. This query lists the 25 with the highest average elapsed time in the current database:

Slowest procedures by average durationSELECT TOP (25)
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS [schema],
    OBJECT_NAME(ps.object_id, ps.database_id)        AS [procedure],
    ps.execution_count,
    ps.total_elapsed_time / ps.execution_count / 1000.0 AS avg_elapsed_ms,
    ps.total_worker_time  / ps.execution_count / 1000.0 AS avg_cpu_ms,
    ps.total_logical_reads / ps.execution_count         AS avg_logical_reads,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id = DB_ID()      -- current DB; remove this line to scan all databases
ORDER BY avg_elapsed_ms DESC;

Rank by total impact, not just average

A procedure that is individually fast but runs millions of times can cost the server more than a slow procedure that runs twice a day. To find what actually consumes the most resources overall, order by the totals rather than the averages:

Biggest total CPU consumersSELECT TOP (25)
    OBJECT_NAME(ps.object_id, ps.database_id) AS [procedure],
    ps.execution_count,
    ps.total_worker_time  / 1000.0 AS total_cpu_ms,
    ps.total_elapsed_time / 1000.0 AS total_elapsed_ms,
    ps.total_logical_reads
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id = DB_ID()
ORDER BY ps.total_worker_time DESC;   -- swap to total_logical_reads for the heaviest I/O

A practical prioritization is average duration × execution count — it surfaces the procedures whose improvement would return the most server time. Tune those first.

Find the slowest statement inside a procedure

Once you know which procedure is slow, the next question is which line. A procedure's cost usually concentrates in one or two statements. Per SQLskills' Erin Stellato, the way to isolate them is to join the query-level stats to the SQL text and slice out each statement using its offsets:

Slowest statements that belong to stored proceduresSELECT TOP (25)
    OBJECT_NAME(qt.objectid, qt.dbid) AS [procedure],
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_ms,
    SUBSTRING(qt.text,
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(qt.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) AS qt
WHERE qt.objectid IS NOT NULL        -- only statements inside modules (procedures, functions)
ORDER BY avg_elapsed_ms DESC;

Add CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) to pull the cached plan for each statement and start reading it directly.

Important caveats

• The numbers live in the plan cache. They reset on a service restart, on plan eviction, or when someone runs DBCC FREEPROCCACHE. Snapshot the data during representative peak hours.
• They reflect cached executions only. A procedure compiled WITH RECOMPILE, or one that has aged out of cache, may be under-represented.
• Averages hide variance. A procedure that is fast for most calls and disastrous for a few is often a parameter-sniffing case — check the min/max, not just the mean.

From finding to fixing

Finding the slow procedures is step one of the optimization loop — the next steps are reading the plan, diagnosing the cause, rewriting safely, and validating the gain. The complete optimization guide walks the rest.

This discovery step is also one SprocOptimizer automates: its Auto-Discover feature queries the plan cache to surface your slowest, most CPU-hungry, or most-executed procedures, and can then batch-optimize them — capturing a real workload, proposing a fix with Claude AI, validating it for identical results, and measuring the before/after.