A procedure-level average tells you that something is slow, but it does not name the statement. To find the slowest statement, pull statement slices from the cached plan and compare their work.
Start With the Procedure Total
sys.dm_exec_procedure_stats reports aggregate CPU, reads, elapsed time, and execution count for cached stored procedures. It is useful for ranking procedures, but it cannot say which line consumed the work. Counters are tied to cached plans and reset when plans leave cache or the instance restarts. Rows with NULL names belong to database_id 32767, the hidden resource database that holds system procedures. I use the list to choose a candidate and record the plan handle. A fresh deployment or recompile can remove the evidence, so capture it while the problem is happening.
SELECT TOP (20) DB_NAME(database_id) AS database_name,
OBJECT_SCHEMA_NAME(object_id, database_id) AS schema_name,
OBJECT_NAME(object_id, database_id) AS procedure_name,
execution_count, total_worker_time, total_logical_reads,
total_elapsed_time, plan_handle
FROM sys.dm_exec_procedure_stats
ORDER BY total_worker_time DESC;Slice the Procedure to Find the Slowest Statement
sys.dm_exec_query_stats stores statement offsets into the batch text. Join it to the procedure's plan handle, get the SQL text, and use SUBSTRING with byte offsets divided by two for nvarchar text. A statement_end_offset of -1 means the end of the batch. Run it in the procedure's own database, because the filter uses DB_ID(), and replace dbo.MyProcedure with your procedure name. The result lists the statements that have cached statistics for that plan. I check the extracted text before sorting metrics because an offset error produces a convincing but wrong label. Dynamic SQL executed by the procedure can have a separate plan and require its own investigation.
SELECT TOP (20)
SUBSTRING(st.text,
qs.statement_start_offset / 2 + 1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset) / 2 + 1) AS statement_text,
qs.execution_count, qs.total_worker_time,
qs.total_logical_reads, qs.total_elapsed_time
FROM sys.dm_exec_procedure_stats AS ps
JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = ps.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE ps.database_id = DB_ID()
AND ps.object_id = OBJECT_ID(N'dbo.MyProcedure')
ORDER BY qs.total_worker_time DESC;Rank More Than CPU
The statement with most total CPU is not always the one users complain about. Sort by total elapsed time, logical reads, and average per execution as well. A frequent cheap statement can top total CPU, while one rare statement causes the long request. Divide by execution_count with a zero guard. Compare the time window and plan generation before calling any query the slowest statement. I also look at last_execution_time and min and max metrics where available. A single outlier can change the average.
Question the unit. DMV times are reported in microseconds for these columns, while logical reads are page counts. Convert for presentation and label the units. Do not put CPU and elapsed values into the same unlabeled chart.
Confirm in Query Store
Query Store keeps per-query and per-plan runtime history inside the database when enabled. Run the query in the procedure's database and find statements whose object_id matches the procedure. It joins runtime statistics through plans, and each plan returns one row per collection interval. This survives an instance restart within the Query Store retention policy and can show when a regression began. It still has capture policy and retention limits. I compare the cached ranking with Query Store rather than treating either as complete. If the procedure constructs dynamic SQL, those statements can appear under a different context and need a text or query-hash search.
SELECT q.query_id, p.plan_id, rs.count_executions,
rs.avg_duration, rs.avg_cpu_time, rs.avg_logical_io_reads
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE q.object_id = OBJECT_ID(N'dbo.MyProcedure')
ORDER BY rs.avg_duration DESC;
Inspect the Slowest Statement's Actual Plan
Once the statement is identified, capture its actual plan with representative parameters. Check estimates, actual rows, spills, memory grants, and repeated lookups. Procedure statistics tell you where to focus; they do not explain why the statement is slow. Parameter sensitivity, stale statistics, missing indexes, and blocking can all create different symptoms. I avoid changing the whole procedure before locating the expensive work. A forty-statement rewrite is an ambitious way to fix one predicate.
Run the procedure under production-like volume in a restored copy. SET STATISTICS IO can show which tables the statement reads. Keep the result and parameter values with the plan for the reviewer.
Account for Recompiles and Dynamic SQL
A procedure can recompile because of statistics changes, temp-table cardinality, or an explicit RECOMPILE option. Its statements can then appear under different cached plans. Summing one plan handle will miss part of the story. I take a snapshot of all matching procedure plans and compare generation times. Dynamic SQL executed through sp_executesql is cached as its own batch, so it does not always sit under the parent procedure's plan handle. Search its text or query hash separately, and keep the parent call context from an application trace when needed.
The offset query is a triage tool, not a complete profiler. A statement still running during the capture has not necessarily updated its aggregate yet. Capture active requests and wait information during the slow call. If blocking dominates elapsed time, a high-duration statement can have modest CPU and reads. That distinction changes the fix. I do not add an index to solve a transaction that waits behind another transaction.
Compare Statement Share With User Impact
Calculate each statement's share of the procedure's total CPU or reads only when the counters cover the same cached period. A statement's execution_count can differ from the procedure count because branches run conditionally or loops repeat. I use that difference to understand the flow. A rare branch with high per-execution cost can explain a complaint from one customer segment even if it barely moves the overall average.
What parameter values trigger the branch? Capture them safely and test a representative set. Query Store intervals can show whether the issue appeared after a plan change or after traffic changed. I place the actual plan and statement text beside the interval, then test one change. The goal is to shorten the user request without making other branches worse. A procedure-level total is useful for prioritization; statement-level evidence is what makes a targeted correction possible.
When the statement runs inside a loop, its execution_count can be much higher than the procedure count. That is a useful clue, not a DMV error. Multiply the average work by frequency before choosing a fix. A statement that takes little time once can dominate the procedure after thousands of iterations. I inspect that branch before tuning the largest single execution.
Recheck the Slowest Statement After the Fix
Compare the same statement's CPU, reads, and duration before and after under similar inputs. Check the procedure total and other statements too. An index added for the culprit can increase write work elsewhere. A hint can improve one parameter and hurt another. What evidence would show the whole procedure improved for users? Define that before release. Keep Query Store history long enough to see whether the fix holds.
I leave a short note with the procedure name, statement text, plan IDs, tested parameters, and observation window. The next person should be able to repeat the diagnosis without searching through forty statements from scratch.
Related reading on this blog: Execution Time of Stored Procedures and Capturing Stored Procedure Executions with Extended Events in SQL Server.
A slow procedure is not one slow statement by default, it is a set of statements you can measure.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
