The core problem is that your query can potentially match on two different procedures in two different databases that happen to have the same
object_id. While there isn't a reliable way to reproduce it on demand, I've seen it enough times to know it is common, and your query needs to protect against it. You can do that with a
WHERE s.database_id = DB_ID();
When pulling stats for other databases, you can use:
FROM sys.dm_exec_procedure_stats AS s
INNER JOIN [other_database].sys.procedures AS p
ON s.[object_id] = p.[object_id]
WHERE s.[database_id] = DB_ID(N'other_database');
Note 1: The stats DMV does not persist data beyond service restarts, failovers, or any action that causes the procedure cache to be flushed (for the instance, for the database, or for a specific procedure). You also won't see any entry in the DMV for stored procedures that are intentionally not cached, e.g.
EXEC dbo.procedurename WITH RECOMPILE;. So it isn't necessarily reliable to conclude that procedure A is unused simply because it isn't present in the DMV.
Note 2: It's possible for a database named
foo to have different
database_id values over its lifetime (consider the case where you drop
foo, create another database, then create another instance of
foo). This is unlikely, but is still worth mentioning, because you could potentially miss executions of a stored procedure that happened in the older copy of
Note 3: If you want to truly measure determine the set of procedures that are unused, you will have to use heavy methods like auditing / extended events / server-side trace, or manually add logging to your stored procedures as I describe in this tip:
And even then, you need to leave that running for a full business cycle, to be sure you don't miss reports that are only run, say, once a quarter or other fiscal period.
Once you have identified the set of stored procedures you don't think are being used, don't drop them; put them in a separate schema (I'm using
zzz here so it sorts at the bottom of the list in Object Explorer, but the name is unimportant - just make sure none of your users have it set as their default schema, and maybe even implement
CREATE SCHEMA zzz;
ALTER SCHEMA zzz TRANSFER dbo.UnusedProcedureName1;
ALTER SCHEMA zzz TRANSFER dbo.UnusedProcedureName2;
This doesn't alleviate clutter quite as effectively as dropping the procedures, but is kind of like a very low-cost insurance policy (only the storage for the stored procedure text in the data file and backups), making it very easy to restore a procedure that was deemed unused in error.