Browse cheat sheets · SQL Server
Find Stored Procedure Usage
Find recently executed stored procedures, compare execution counts and resource usage, and understand the limits of SQL Server procedure-cache statistics.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Find recently executed procedures
- 2. Find the most frequently executed procedures
- 3. Find procedures consuming the most total CPU
- 4. Find procedures with the highest average CPU
- 5. Find procedures doing the most logical reads
- 6. Find procedures with the highest average elapsed time
- 7. Check one specific stored procedure
- 8. Find procedures currently missing from procedure stats
- 9. Check the SQL Server uptime / observation window
- 10. Check when the procedure’s stats entered the cache
- 11. Use Query Store for durable historical evidence
- What the result means
- What to look for
- High execution count + modest average cost
- Low execution count + very high average duration
- High elapsed time + low CPU
- High CPU + high logical reads
- cached_time is recent
- No row in sys.dm_exec_procedure_stats
- Production notes
- Procedure stats are cache-based
- Plan cache lifecycle matters
- Averages can hide skew
- Removing a procedure requires more than usage stats
- Permissions vary by version
- Common mistakes
- 1. Treating missing DMV rows as proof of non-use
- 2. Ignoring cached_time
- 3. Ranking only by average duration
- 4. Dropping a stored procedure because it “hasn’t run”
- 5. Assuming procedure stats equal statement stats
- 6. Comparing stats across cache lifecycles
- Next step
- Related deep dives
Problem
You need to know whether a stored procedure is still being used, which procedures run most often, or which procedures consume the most CPU, reads, or elapsed time.
Typical questions include:
- Has this stored procedure executed recently?
- How many times has it run?
- Which procedures are most expensive?
- Which procedures have not appeared in the recent workload?
- Can I safely remove a procedure that seems unused?
The key limitation is that sys.dm_exec_procedure_stats is cache-based history, not permanent usage history.
Quick answer
Use sys.dm_exec_procedure_stats for procedures that currently have cached execution statistics.
SELECT TOP (50)
DB_NAME(ps.database_id) AS database_name,
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.cached_time,
ps.last_execution_time,
ps.execution_count,
CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
ps.total_worker_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
ps.total_logical_reads,
CAST(
ps.total_logical_reads * 1.0
/ NULLIF(ps.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
CAST(ps.total_elapsed_time / 1000.0 AS decimal(18,2)) AS total_elapsed_ms,
CAST(
ps.total_elapsed_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_elapsed_ms
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.last_execution_time DESC;
Use this to understand recent cached activity.
Do not interpret “no row returned” as proof that the procedure has never been used.
Code / commands
1. Find recently executed procedures
SELECT
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.last_execution_time,
ps.execution_count,
ps.cached_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.last_execution_time DESC;
Use this when the question is:
Which stored procedures have run most recently?
2. Find the most frequently executed procedures
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
ps.last_execution_time,
ps.cached_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.execution_count DESC;
High execution count can identify:
- hot API procedures
- chatty application patterns
- frequently called utility procedures
- procedures where small inefficiencies become expensive at scale
3. Find procedures consuming the most total CPU
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
ps.total_worker_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.total_worker_time DESC;
Use total CPU when you care about overall server impact.
A moderately expensive procedure called thousands of times can matter more than one very slow procedure called once.
4. Find procedures with the highest average CPU
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
CAST(
ps.total_worker_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
avg_cpu_ms DESC;
Average cost is useful for expensive-per-call procedures.
Always look at execution_count next to the average.
Averages based on one or two executions are weak evidence.
5. Find procedures doing the most logical reads
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
ps.total_logical_reads,
CAST(
ps.total_logical_reads * 1.0
/ NULLIF(ps.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.total_logical_reads DESC;
Use this to identify procedures that put heavy pressure on the buffer pool or scan large amounts of cached data.
High reads do not automatically mean “missing index.”
Inspect query plans and procedure logic.
6. Find procedures with the highest average elapsed time
SELECT TOP (50)
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.execution_count,
CAST(
ps.total_elapsed_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_elapsed_ms,
CAST(
ps.total_worker_time * 1.0
/ NULLIF(ps.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
avg_elapsed_ms DESC;
If elapsed time is high but CPU is low, investigate waits and blocking.
7. Check one specific stored procedure
DECLARE @schema_name sysname = N'dbo';
DECLARE @procedure_name sysname = N'YourProcedure';
SELECT
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.cached_time,
ps.last_execution_time,
ps.execution_count,
CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(ps.total_elapsed_time / 1000.0 AS decimal(18,2)) AS total_elapsed_ms,
ps.total_logical_reads,
ps.total_logical_writes,
ps.total_physical_reads
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
AND ps.object_id = OBJECT_ID(
QUOTENAME(@schema_name) + N'.' + QUOTENAME(@procedure_name)
);
If this returns no row, do not conclude the procedure is unused.
It may simply not currently have a cached procedure plan.
8. Find procedures currently missing from procedure stats
This query compares user procedures with the cache-based stats DMV.
SELECT
s.name AS schema_name,
p.name AS procedure_name,
p.create_date,
p.modify_date
FROM sys.procedures AS p
INNER JOIN sys.schemas AS s
ON s.schema_id = p.schema_id
LEFT JOIN sys.dm_exec_procedure_stats AS ps
ON ps.database_id = DB_ID()
AND ps.object_id = p.object_id
WHERE
p.is_ms_shipped = 0
AND ps.object_id IS NULL
ORDER BY
s.name,
p.name;
Interpret this correctly:
These procedures do not currently have a row in
sys.dm_exec_procedure_stats.
It does not mean:
These procedures have never been executed.
9. Check the SQL Server uptime / observation window
SELECT
sqlserver_start_time
FROM sys.dm_os_sys_info;
Procedure statistics are tied to cached plans.
If the instance restarted recently, your visibility window may be short.
10. Check when the procedure’s stats entered the cache
SELECT
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
ps.cached_time,
ps.last_execution_time,
ps.execution_count
FROM sys.dm_exec_procedure_stats AS ps
WHERE
ps.database_id = DB_ID()
ORDER BY
ps.cached_time DESC;
cached_time is important because the counters apply to the current cached procedure plan/statistics lifecycle.
11. Use Query Store for durable historical evidence
If Query Store is enabled, it is usually a better source for longer-term execution history than procedure cache statistics.
Start by locating queries associated with the procedure’s object context and historical runtime.
The exact Query Store analysis depends on your procedure/query structure, but the general principle is:
- procedure stats = fast recent cache view
- Query Store = persisted historical query/runtime evidence
Use both when deciding whether a procedure is genuinely unused or expensive.
What the result means
| Field | Why it matters |
|---|---|
execution_count |
Number of executions represented by the current cached statistics. |
cached_time |
Time the procedure statistics/plan entered cache. Defines the current observation window for that row. |
last_execution_time |
Most recent execution represented by the current stats row. |
total_worker_time |
Total CPU time accumulated by executions in the current stats row. |
total_elapsed_time |
Total elapsed time accumulated. |
total_logical_reads |
Total logical reads accumulated. |
total_logical_writes |
Total logical writes accumulated. |
total_physical_reads |
Total physical reads accumulated. |
avg_cpu_ms |
Derived CPU per execution. Useful only with execution count/context. |
avg_elapsed_ms |
Derived average duration. Can hide outliers. |
What to look for
High execution count + modest average cost
This can still be a major workload contributor.
Small inefficiencies multiplied by very high call volume can consume substantial CPU and I/O.
Low execution count + very high average duration
Possible interpretations:
- report or batch procedure
- infrequent maintenance workload
- genuinely problematic slow procedure
- one-off parameter-specific issue
Do not prioritize by average alone.
High elapsed time + low CPU
Investigate:
- blocking
- I/O waits
- network/result consumption
- memory waits
- external resource contention
High CPU + high logical reads
Likely worth deeper execution-plan analysis.
Look for:
- large scans
- join strategy
- cardinality issues
- expensive repeated statements
- indexing opportunities
cached_time is recent
Weak observation window.
The procedure may have years of production usage but only minutes of current cache statistics.
No row in sys.dm_exec_procedure_stats
Possible reasons include:
- procedure has not run since cache/reset events
- plan was evicted
- procedure was recompiled
- server restarted
- database context changed
- cache pressure
- metadata/schema changes
Do not label it unused from this alone.
Production notes
Procedure stats are cache-based
sys.dm_exec_procedure_stats is not durable historical telemetry.
Rows can disappear when plans are removed from cache.
That means the DMV is excellent for:
- recent usage
- recent CPU/read ranking
- immediate triage
but weak for:
- “Has this been used in the last 6 months?”
- deprecation decisions
- compliance/history reporting
Plan cache lifecycle matters
Events that can change the observation window include:
- restart
- recompilation
- plan eviction
- schema changes
- memory pressure
- cache flush operations
Always inspect cached_time.
Averages can hide skew
One procedure may be:
- fast for 99% of calls
- very slow for 1% of calls
Average duration can conceal that pattern.
Use Query Store or detailed telemetry when distribution matters.
Removing a procedure requires more than usage stats
Before dropping a procedure, check:
- application code references
- SQL Agent jobs
- ETL packages
- reporting systems
- external integrations
- deployment scripts
- documentation
- Query Store/history
- source control
A procedure can be rarely used but operationally critical.
Permissions vary by version
Broad DMV visibility generally requires the appropriate server performance/state permission.
For SQL Server 2022 and later, Microsoft uses VIEW SERVER PERFORMANCE STATE for many performance DMVs.
Earlier versions commonly use VIEW SERVER STATE.
Common mistakes
1. Treating missing DMV rows as proof of non-use
This is the biggest mistake.
No row means no current cached statistics row.
It does not mean the procedure has never been executed.
2. Ignoring cached_time
Execution counts without an observation window are misleading.
10 executions in 5 minutes and 10 executions in 6 months mean very different things.
3. Ranking only by average duration
Averages can over-prioritize one-off procedures and under-prioritize frequently executed workload.
Look at:
- total resource use
- average resource use
- execution count
- business importance
4. Dropping a stored procedure because it “hasn’t run”
Before removal, confirm references outside SQL Server runtime stats.
Rare operational procedures can be critical during incidents, month-end, year-end, or recovery workflows.
5. Assuming procedure stats equal statement stats
A stored procedure can contain several statements with very different behavior.
Use Query Store, execution plans, or statement-level DMVs when you need to know which statement is expensive.
6. Comparing stats across cache lifecycles
If a procedure was recompiled or evicted, the counters may restart.
Do not compare current counters with older snapshots unless you account for resets.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.