Sql-server – Wrong Last Execution Time sys.dm_exec_procedure_stats

dmvsql-server-2008-r2stored-procedures

I am running the following query it shows a Stored Proc that I know ran on Mon 6/6/2016 8:45 AM but the below query shows that the last execution time was 2016-05-19 12:09:23.197. Is there something I am missing here or is this an issue on the server? Everything I have found online refers to the opposite issue where people are getting reports that the proc is running when it isn't. 

SELECT * FROM sys.dm_exec_procedure_stats AS s
INNER JOIN sys.procedures AS p
ON s.[object_id] = p.[object_id]

Best Answer

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 clause:

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 foo.

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 DENY EXECUTE).

CREATE SCHEMA zzz;
GO
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.

Related Question