Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: PerformanceDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Find recently executed procedures
  5. 2. Find the most frequently executed procedures
  6. 3. Find procedures consuming the most total CPU
  7. 4. Find procedures with the highest average CPU
  8. 5. Find procedures doing the most logical reads
  9. 6. Find procedures with the highest average elapsed time
  10. 7. Check one specific stored procedure
  11. 8. Find procedures currently missing from procedure stats
  12. 9. Check the SQL Server uptime / observation window
  13. 10. Check when the procedure’s stats entered the cache
  14. 11. Use Query Store for durable historical evidence
  15. What the result means
  16. What to look for
  17. High execution count + modest average cost
  18. Low execution count + very high average duration
  19. High elapsed time + low CPU
  20. High CPU + high logical reads
  21. cached_time is recent
  22. No row in sys.dm_exec_procedure_stats
  23. Production notes
  24. Procedure stats are cache-based
  25. Plan cache lifecycle matters
  26. Averages can hide skew
  27. Removing a procedure requires more than usage stats
  28. Permissions vary by version
  29. Common mistakes
  30. 1. Treating missing DMV rows as proof of non-use
  31. 2. Ignoring cached_time
  32. 3. Ranking only by average duration
  33. 4. Dropping a stored procedure because it “hasn’t run”
  34. 5. Assuming procedure stats equal statement stats
  35. 6. Comparing stats across cache lifecycles
  36. Next step
  37. 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.

Next step

Use the usage pattern to decide what to investigate next.

  • High execution count + high total CPU → inspect the procedure’s expensive statements and execution plans.
  • High logical reads → inspect scans, predicates, joins, and indexing.
  • High elapsed time + low CPU → inspect blocking and wait types.
  • No current stats row → check Query Store, source references, jobs, application code, and monitoring before calling it unused.
  • Recent cached_time → collect a longer observation window.
  • Procedure is a removal candidate → verify dependencies outside the DMV before deprecation.
  • Procedure has mixed fast/slow behavior → use Query Store for historical plan/runtime analysis.

← All cheat sheets