2

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]
  • How do you know it ran and completed at that time? At the moment we are kinda stuck taking your word for it.... – Zane Jun 9 '16 at 17:53
  • Run it right now and see what happens... – dfundako Jun 9 '16 at 17:54
  • I have an automated report that runs, a console application I wrote calls that stored proc and generates an excel file from the results of that file. It then emails it out to a large distro of people. I double checked the report and it has data that would not have been on it unless it ran that day. – scripter78 Jun 9 '16 at 17:56
  • still shows last ran 2016-05-19 12:09:23.197, dfun, sorry I just realized what you were asking hold a sec – scripter78 Jun 9 '16 at 17:57
  • Defun, I just ran the proc that is in question and then ran the dm_exec_procedure_stats and it now shows today as last executed. The scary about this is that if there is missing history then my purpose of doing this might be hindered. I just happened to know that proc very well so when I saw that it was a month ago ran last I knew it was wrong. The purpose of this is to see which procs are not being used for any reports and look at cleaning up the database. – scripter78 Jun 9 '16 at 18:01
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.