Finding Dead Code: Stored Procedures Nobody Calls

An old procedure can look unused simply because its plan disappeared from cache. Finding dead code requires observation across the relevant business cycle and a retirement process that can recover from an overlooked caller.

Treat Dead Code as a Candidate First

A procedure without recent observed execution is a candidate for investigation. It is not automatically unused. Some routines run during year-end work, recovery exercises, rare support cases, or application paths that ordinary traffic does not reach. Choose an observation window that includes those accepted uses before reaching a retirement decision.

I ask who owns the procedure before asking how old it is. An owner can explain an infrequent dependency that a short counter history misses. Preserve the current definition, settings, permissions, signatures, and relevant deployment evidence before proposing any consequential change. An object name that sounds obsolete is weak evidence about its actual callers.

Use the following queries read-only against the reviewed database first. Administrative performance views require appropriate permissions, including VIEW SERVER PERFORMANCE STATE for relevant SQL Server 2022 and later instance DMVs. Missing metadata access can produce an incomplete candidate population, so verify the review scope as part of the evidence.

Find Procedures Without a Cached Statistics Row

The procedure statistics view describes cached procedure plans and their execution counts since compilation. A procedure can have more than one relevant cached plan. Use NOT EXISTS to identify procedures without any matching current row rather than relying on one arbitrarily selected plan.

SELECT s.name AS SchemaName,p.name AS ProcedureName,p.create_date,p.modify_date
FROM sys.procedures AS p
JOIN sys.schemas AS s ON s.schema_id=p.schema_id
WHERE p.is_ms_shipped=0 AND p.type='P'
  AND NOT EXISTS
  (
      SELECT 1 FROM sys.dm_exec_procedure_stats AS d
      WHERE d.database_id=DB_ID() AND d.object_id=p.object_id
  );
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Restart resets the available cache, and eviction or recompilation can remove or replace a row before the next restart. A missing row therefore describes current cached evidence, not lifetime usage. A monthly procedure can be entirely innocent of the cache's short memory. Keep that limitation attached to every candidate report.

Check Dependencies Before Calling It Dead Code

The dependency catalog identifies names referenced in persisted SQL expressions. Search for procedures without a resolved incoming reference from another module. Self-reference alone does not establish an external caller, so the example excludes it from this particular candidate test.

SELECT s.name AS SchemaName,p.name AS ProcedureName
FROM sys.procedures AS p
JOIN sys.schemas AS s ON s.schema_id=p.schema_id
WHERE p.is_ms_shipped=0 AND p.type='P'
  AND NOT EXISTS
  (
      SELECT 1 FROM sys.sql_expression_dependencies AS d
      WHERE d.referenced_id=p.object_id AND d.referencing_id<>p.object_id
  );
SELECT referencing_id,referenced_database_name,referenced_schema_name,
       referenced_entity_name,is_caller_dependent
FROM sys.sql_expression_dependencies
WHERE referenced_id IS NULL;

Dynamic SQL, external applications, and some runtime-resolved references need separate investigation. Cross-database references also do not provide resolved IDs in this catalog. Review callers from other databases, Agent jobs, reporting entry points, and operational runbooks. No local incoming dependency means no recorded local dependency under this method, not no possible caller.

Keep Execution-Counter Snapshots Over Weeks

A history table makes observations survive the next cache change. Preserve capture time, object identity, creation date, plan handle, compilation time, and raw count. The sample deliberately allows NULL plan fields for a procedure with no current cached row. That absence remains unknown execution evidence rather than a made-up zero.

CREATE TABLE dbo.ProcedureHistory
(
    HistoryID bigint IDENTITY PRIMARY KEY,
    SnapshotUTC datetime2 NOT NULL,
    StartupTime datetime2 NOT NULL,
    ProcedureID int NOT NULL,
    ProcedureCreated datetime NOT NULL,
    SchemaName sysname NOT NULL,
    ProcedureName sysname NOT NULL,
    PlanHandle varbinary(64) NULL,
    CachedTime datetime NULL,
    ExecutionCount bigint NULL,
    LastExecutionTime datetime NULL
);
DECLARE @SnapshotUTC datetime2=SYSUTCDATETIME();
DECLARE @Startup datetime2=(SELECT sqlserver_start_time FROM sys.dm_os_sys_info);
INSERT dbo.ProcedureHistory
    (SnapshotUTC,StartupTime,ProcedureID,ProcedureCreated,SchemaName,
     ProcedureName,PlanHandle,CachedTime,ExecutionCount,LastExecutionTime)
SELECT @SnapshotUTC,@Startup,p.object_id,p.create_date,s.name,p.name,
       d.plan_handle,d.cached_time,d.execution_count,d.last_execution_time
FROM sys.procedures AS p
JOIN sys.schemas AS s ON s.schema_id=p.schema_id
LEFT JOIN sys.dm_exec_procedure_stats AS d
  ON d.database_id=DB_ID() AND d.object_id=p.object_id
WHERE p.is_ms_shipped=0 AND p.type='P';

Create the table once in an approved monitoring location and schedule the INSERT at a reviewed interval. Alert on missed captures and retain the weeks or months relevant to the business cycle. A snapshot collection records what was visible at capture time; executions followed by eviction between captures can still be missed.

Compare Counts Within a Cache Lifetime

Use differences only where the captured plan lifetime and object identity remain comparable. Partition by procedure identity, creation date, plan handle, compilation time, and startup period. A new compilation starts a new observation segment. Do not subtract its initial count from an unrelated prior plan's count.

WITH Observations AS
(
    SELECT *,LAG(ExecutionCount) OVER
    (
        PARTITION BY ProcedureID,ProcedureCreated,PlanHandle,CachedTime,StartupTime
        ORDER BY SnapshotUTC,HistoryID
    ) AS PreviousCount
    FROM dbo.ProcedureHistory
    WHERE PlanHandle IS NOT NULL
)
SELECT SchemaName,ProcedureName,SnapshotUTC,
       ExecutionCount-PreviousCount AS ObservedExecutionDelta
FROM Observations
WHERE PreviousCount IS NOT NULL AND ExecutionCount>=PreviousCount;

Treat the deltas as observed cached activity rather than a complete execution audit. Where retirement needs stronger evidence, use an approved Extended Events or application-level collection scoped to the candidates. Retain coverage gaps and failures. A zero observed delta cannot erase a period during which the observer was absent.

Review External Calls and the Retirement Baseline

I search known application and job entry points before accepting a candidate for quarantine. A text search is only a supporting check, because dynamic construction and indirect invocation can conceal the name. Ask owners to confirm rare business paths and retain their accepted retirement scope with the evidence.

SELECT j.name AS JobName,st.step_id,st.step_name,st.database_name,st.command
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS st ON st.job_id=j.job_id
WHERE CHARINDEX(N'dbo.LegacyReport',st.command)>0;
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.LegacyReport')) AS SavedDefinition;

The procedure name is an illustrative reviewed candidate, not an existing object to remove automatically. Check definition visibility and preserve the baseline outside the database before changing its name. Also record the exact rollback action and who can execute it if a caller appears during quarantine.

Quarantine Dead Code With a Return Route

The retirement sequence here is rename, observe, then drop. Use renaming only as an explicitly approved temporary quarantine, since calls using the old name can fail. SQL Server does not rewrite dependent references or the stored module-definition text during sp_rename; it is not a general recommendation for normal procedure renaming.

EXEC sys.sp_rename
    @objname=N'dbo.LegacyReport',
    @newname=N'LegacyReport_Retired',@objtype=N'OBJECT';

Monitor name-resolution failures, application errors, and owner feedback across the accepted interval. Be ready to rename the quarantined object back if an overlooked caller appears. For a normal permanent rename, a reviewed create-and-migrate-caller plan is preferable. The temporary quarantine has a deliberately different purpose and still needs permission and rollback evidence.

Wait Through the Cycle Before Dropping

Which rare operation has the observation period actually covered? Include the accepted month-end, year-end, or support interval rather than choosing a short quiet week. Recheck dependencies and current definition identity before the final approved removal. A later deployment can reintroduce a use while the candidate is waiting.

DROP PROCEDURE dbo.LegacyReport_Retired;

Run the drop only after the owner accepts retirement and the preserved baseline has a tested restoration path. Keep the decision and evidence after removal. Dead code is established by converging observations and ownership review, not by one empty DMV result that happened to appear during a quiet moment. Restoration after the final drop needs more than the procedure text. Recreate the accepted original name, restore the captured creation settings and permissions, and reapply any required signatures through the reviewed process. Test the actual caller identity on a rehearsal copy. Keep that package available for the agreed retention interval so a late-discovered operational dependency has a practical recovery route.

Related reading on this blog: Last Used Stored Procedure and Capturing Stored Procedure Executions with Extended Events in SQL Server.

Missing execution evidence is not proof of dead code, it is a reason to investigate before a reversible retirement decision.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.