Last Used Stored Procedure in SQL Server: When Did It Last Run?

The last used stored procedure is the one with the newest last_execution_time in the plan cache. SQL Server keeps no “last used” date on the procedure itself. The date lives in a view that forgets, so read it with care.

Where the Date Lives

Every procedure that runs gets a row in sys.dm_exec_procedure_stats while its plan stays in the cache. The row holds the run count and the time of the last run. A LEFT JOIN from sys.procedures keeps the procedures that have no row, and those show NULL.

Older scripts read sys.dm_exec_query_stats instead. That view counts statements, not procedures, so a procedure with ten statements shows up ten times. The procedure view returns one row per procedure.

The demo database has a shelf table and four procedures. The first block builds them and turns on Query Store, which a later section explains. Query Store needs a moment to start capturing, so the block waits three seconds after switching it on. It runs ListShelf three times, CloseMonth once and FreshEveryTime twice. OldYearReport never runs.

IF DB_ID(N'LastRunProcDemo') IS NULL CREATE DATABASE LastRunProcDemo;
GO
ALTER DATABASE LastRunProcDemo SET QUERY_STORE = ON (QUERY_CAPTURE_MODE = ALL, INTERVAL_LENGTH_MINUTES = 1);
WAITFOR DELAY '00:00:03';
GO
USE LastRunProcDemo;
GO
DROP TABLE IF EXISTS dbo.Shelf;
CREATE TABLE dbo.Shelf (BookID int NOT NULL PRIMARY KEY, Title nvarchar(60) NOT NULL);
INSERT INTO dbo.Shelf VALUES (1, N'Herb Garden Basics'), (2, N'Tea Around the World');
GO
CREATE OR ALTER PROCEDURE dbo.ListShelf AS SELECT BookID, Title FROM dbo.Shelf;
GO
CREATE OR ALTER PROCEDURE dbo.CloseMonth AS UPDATE dbo.Shelf SET Title = Title WHERE BookID = 1;
GO
CREATE OR ALTER PROCEDURE dbo.OldYearReport AS SELECT COUNT(*) AS Books FROM dbo.Shelf;
GO
CREATE OR ALTER PROCEDURE dbo.FreshEveryTime WITH RECOMPILE AS SELECT TOP (1) Title FROM dbo.Shelf ORDER BY BookID;
GO
EXEC dbo.ListShelf;
EXEC dbo.ListShelf;
EXEC dbo.ListShelf;
EXEC dbo.CloseMonth;
EXEC dbo.FreshEveryTime;
EXEC dbo.FreshEveryTime;

SELECT p.name AS ProcedureName, ps.execution_count AS Runs, ps.last_execution_time AS LastRun
FROM sys.procedures AS p
LEFT JOIN sys.dm_exec_procedure_stats AS ps ON ps.object_id = p.object_id AND ps.database_id = DB_ID()
ORDER BY p.name;

The times are server local time and change with every run. ListShelf shows three runs, CloseMonth one, and OldYearReport has no row, as expected. FreshEveryTime ran twice and still has no row. A procedure created WITH RECOMPILE never keeps a plan in the cache, so this view cannot see it. The view needs VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

NULL Does Not Mean Never

A plan also leaves the cache after a restart, under memory pressure, or when someone clears it. The next statement clears the plans of this database only, and nothing else on the server. It needs SQL Server 2019 or later. Then the same query runs again.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
SELECT p.name AS ProcedureName, ps.execution_count AS Runs, ps.last_execution_time AS LastRun
FROM sys.procedures AS p
LEFT JOIN sys.dm_exec_procedure_stats AS ps ON ps.object_id = p.object_id AND ps.database_id = DB_ID()
ORDER BY p.name;

All four procedures look unused now, and three of them ran seconds ago. A NULL says only that no plan is in the cache. Dropping a procedure on that evidence is how a month-end job breaks a month later. For the counting side of the same view, read Frequently Run Stored Procedures: Find Them in the Cache.

A Longer Memory With Query Store

Query Store keeps runtime numbers inside the database, so a restart or a cache clear does not erase them. It also records procedures created with recompile. The first block turned it on with QUERY_CAPTURE_MODE = ALL, which records cheap statements too. The default mode skips queries it judges insignificant, and these are tiny.

The next script waits two seconds and then flushes its memory to disk. The query joins each procedure to its queries, plans and run statistics.

WAITFOR DELAY '00:00:02';
EXEC sys.sp_query_store_flush_db;
GO
SELECT p.name AS ProcedureName, MAX(rs.last_execution_time) AS LastRun, SUM(rs.count_executions) AS Runs
FROM sys.procedures AS p
LEFT JOIN sys.query_store_query AS q ON q.object_id = p.object_id
LEFT JOIN sys.query_store_plan AS pl ON pl.query_id = q.query_id
LEFT JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = pl.plan_id
GROUP BY p.name
ORDER BY p.name;

A complete result lists CloseMonth, FreshEveryTime and ListShelf with a run count and a time, and OldYearReport stays NULL. This is one possible result, and the times vary.

The times are UTC, with a +00:00 offset. They differ from the cache view by the server time zone. The cache was cleared before this query, and Query Store still answered.

The counts need a warning. Query Store captures runs in a background task. A run or a whole procedure can be missing from the result. The counts can differ from one run to the next. Treat Query Store as a longer memory, not an audit log. Let it run a while before you trust a NULL.

How Long to Watch Before You Drop

A last used stored procedure date needs a long window. A procedure that runs once a year needs a year of watching. The cache cannot give you that, and Query Store can only start counting from the day you enable it. Watch through every business cycle, such as month end and year end, before you call a procedure unused.

You could argue that a log table is more certain. Each procedure writes a row when it starts, and nothing is ever forgotten. That is true. It also means editing every procedure and paying for a write on every call. Query Store gives most of that value without changing any code.

When you do find a candidate, do not drop it first. Rename it, or deny execute permission, and wait for complaints. Both steps are easy to undo, and a drop is not.

What to Remember

The last used stored procedure comes from last_execution_time in sys.dm_exec_procedure_stats. A NULL means no plan is cached, not that the procedure never ran. Use Query Store for a memory that survives restarts, and watch a full business cycle.

Clean up the demo when you finish.

USE master;
GO
ALTER DATABASE LastRunProcDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE LastRunProcDemo;

A missing date is not proof of an unused procedure, it is a cache that forgot.

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