"The application is slow" doesn't tell you where to start. Ranking the slowest stored procedures gives that complaint a useful first address. Cache statistics tell you where to look before you change anything.
List the Slowest Stored Procedures First
I start with procedure totals when an application complaint has no clear query attached. The application usually calls familiar entry points. A ranked list turns that familiarity into something you can investigate.
The view sys.dm_exec_procedure_stats reports completed executions for cached procedure plans. Run the query in the database you want to investigate. The filter keeps another database's procedures out of your list.
Both schema and object names matter. Different schemas can hold procedures with identical names. Keep the database identifier when you collect results across several databases.
On SQL Server 2022 and later, server-wide access requires VIEW SERVER PERFORMANCE STATE. Older SQL Server versions use VIEW SERVER STATE. Metadata visibility also affects the names you can resolve.
SELECT TOP (20)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS SchemaName,
OBJECT_NAME(ps.object_id, ps.database_id) AS ProcedureName,
ps.execution_count,
ps.total_elapsed_time / 1000.0 AS TotalElapsedMs,
ps.total_elapsed_time / NULLIF(ps.execution_count, 0) / 1000.0 AS AverageElapsedMs,
ps.total_worker_time / 1000.0 AS TotalCpuMs,
ps.total_logical_reads,
ps.cached_time, ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id = DB_ID()
ORDER BY ps.total_elapsed_time DESC;Decide What Slow Means
Total elapsed time finds accumulated demand. A short procedure called repeatedly can outrank a long procedure called once. Both deserve attention, but they describe different application behavior.
Average elapsed time helps locate expensive calls. Sort by AverageElapsedMs when that is your question. Read execution_count beside the average before making a decision.
Total CPU identifies processor demand across completed calls. Divide total_worker_time by execution_count for average CPU. Parallel execution can accumulate CPU across several workers.
Logical reads count page accesses, including pages already in memory. They don't describe disk reads alone. Divide total_logical_reads by execution_count when comparing the average work per call.
Ranking the slowest stored procedures requires a stated goal. Are you reducing total server load or fixing one painfully slow screen? That answer determines which column deserves the first sort.
Keep the Cache Boundary Visible
These totals begin with the cached plan represented by the row. Eviction removes its row and its accumulated history. A restart clears this cache history too.
Recompilation and replacement introduce a fresh reporting boundary. Procedures can also have multiple cached plans. Don't silently collapse those plans into one supposedly uninterrupted lifetime total.
The elapsed and worker time units are microseconds. The query converts them to milliseconds for reading. Those conversions aren't measured results from a test run.
Statistics change when executions complete. A currently running slow call hasn't contributed its completed execution totals yet. Check active requests separately when the complaint is happening now.
A quiet result doesn't prove yesterday was quiet. It proves the surviving cache entries have limited recorded work. The cache has an impressive ability to forget inconvenient history.
Snapshot the Slowest Stored Procedures
Lifetime totals mix several business periods together. To study one hour, collect a baseline and a second sample. Keep the same SSMS session open for these temporary tables.
The snapshot includes plan_handle and cached_time. Those fields help distinguish a surviving plan from a replacement. Capture the wall-clock sample time alongside the counters.
The first query copies the current database's procedure statistics. Don't hold a transaction open while waiting for the second collection. The observation interval needs no application locks.
For repeated monitoring, use a permanent table with a collection identifier. Record the server, database and collection time. Temporary tables are enough for a focused investigation.
SELECT SYSDATETIME() AS SampleAt,
database_id, object_id, plan_handle, cached_time,
execution_count, total_elapsed_time,
total_worker_time, total_logical_reads
INTO #ProcedureBefore
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID();
SELECT COUNT_BIG(*) AS CapturedPlans,
MIN(SampleAt) AS SampleAt
FROM #ProcedureBefore;Subtract Only Matching Plans
Take the second snapshot one hour later. Use the same columns and filter. The SQL doesn't pretend an hour has passed merely because you pasted the next block.
Match the database, object, handle and cache timestamp. Subtract counters only when they remain nondecreasing. A changed plan belongs in a separate review list.
The difference in execution_count becomes the interval denominator. Ignore a zero difference when calculating an average. Keep the individual plan rows until you have inspected their boundaries.
SELECT SYSDATETIME() AS SampleAt,
database_id, object_id, plan_handle, cached_time,
execution_count, total_elapsed_time,
total_worker_time, total_logical_reads
INTO #ProcedureAfter
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID();
SELECT OBJECT_SCHEMA_NAME(a.object_id, a.database_id) AS SchemaName,
OBJECT_NAME(a.object_id, a.database_id) AS ProcedureName,
a.execution_count - b.execution_count AS IntervalExecutions,
(a.total_elapsed_time - b.total_elapsed_time) / 1000.0 AS IntervalElapsedMs,
(a.total_elapsed_time - b.total_elapsed_time)
/ NULLIF(a.execution_count - b.execution_count, 0) / 1000.0 AS IntervalAverageMs,
(a.total_worker_time - b.total_worker_time) / 1000.0 AS IntervalCpuMs,
a.total_logical_reads - b.total_logical_reads AS IntervalReads
FROM #ProcedureAfter AS a
JOIN #ProcedureBefore AS b
ON b.database_id = a.database_id AND b.object_id = a.object_id
AND b.plan_handle = a.plan_handle AND b.cached_time = a.cached_time
WHERE a.execution_count > b.execution_count
AND a.total_elapsed_time >= b.total_elapsed_time
AND a.total_worker_time >= b.total_worker_time
AND a.total_logical_reads >= b.total_logical_reads
ORDER BY IntervalElapsedMs DESC;Investigate What the Difference Excludes
An entry present only in the second snapshot has no usable baseline. An entry present only in the first has disappeared. Neither group should receive an invented interval total.
This method understates activity when plans disappear during the hour. Call that limitation out when sharing the ranking. Query Store provides a better historical investigation when it is already collecting appropriate data.
I check cache turnover before treating a subtraction report as complete. A deployment during the interval changes the interpretation. An application outage during collection changes it again.
Look at the interval's workload too. A scheduled report and an interactive screen don't have the same purpose. Their priorities come from the business complaint, not the ranking alone.
Trace the Slowest Stored Procedures to a Statement
A procedure can contain several queries with very different costs. Use the procedure plan and statement statistics to find the expensive statement. Then inspect its parameters, actual rows and waits.
Elapsed time includes waiting. High elapsed time with relatively little CPU deserves a blocking and wait investigation. High reads point toward access paths and repeated work.
I resist changing an index directly from this first report. The procedure name is a lead, not a complete diagnosis. Different parameter values can explain inconsistent experiences within one entry point.
The slowest stored procedures report earns its place by narrowing the search. Save the collection times and cache caveats with it. Then make the next investigation smaller and more concrete.
Different SET options can leave several plans for one procedure. Review their cache timestamps separately before aggregating matching interval differences. A procedure-level sum is useful only after that review.
Also record the collection gap from the saved timestamps. An interrupted collection schedule doesn't produce a one-hour report automatically. Report the interval you captured rather than the interval you intended.
Related reading on this blog: Finding the Root Cause of Slow Queries and Capturing Stored Procedure Executions with Extended Events in SQL Server.
A procedure ranking is not a diagnosis, it is a starting point for finding the expensive work.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
