Over time the number of stored procedures in a database can grow. Somehow, it never seems to shrink. Stored procedures remain that have never been used for a long period of time. They become obsolete, but are not removed.
Have you ever wanted to remove those unused stored procedures, but don’t know which ones are obsolete? Today’s tip will help you identify those pesky varmints.
The system view dm_exec_procedure_stats contains statistics about the execution of stored procedures in your database. As a note, this is one of the advantages of stored procedures over ad-hoc queries because execution statistics are maintained. Regardless, because these statistics are available in a view they can be used to identify what stored procedures have been executed. If you can identify the stored procedures in a database, then you can identify the ones that have not been executed because they will not have any statistics in dm_exec_procedure_stats.
Here is a simple query to return a list of un-used stored procedures.
WITH UnUsed (id)
AS
(
SELECT s.object_id
FROM sys.procedures AS s
EXCEPT
SELECT dm.object_id
FROM sys.dm_exec_procedure_stats AS dm
)
SELECT s.name, s.type_desc
FROM UnUsed
JOIN sys.procedures s ON unused.id = s.object_id
There is one caveat when using this query. The statistics in dm_exec_procedure_stats are maintained since the instance of SQL Server was started. If you were to restart the SQL Service most of the stored procedures in your database would be identified as un-used. The value of this query improves the longer your instance of SQL Server has been running. If you have procedures that are executed monthly or quarterly, you’ll need to be sure your SQL instance has been running at least that long.
Script out and save a copy of your stored procedures before deleting those identified as un-used. You can then restored them if you later find they were still used. Many companies already have the stored procedures scripted out and saved in a version control system.
Running this kind of query to identify potential deprecated stored procedures on a regular basis can be useful if monitored over time. Each time a procedure is identified as deprecated your confidence in removing the procedure continues to grow.
That’s my tip for today. Share your own tip here online, or drop me an email at btaylor@sswug.org.
Cheers,
Ben