Find Recently Executed Stored Procedures
This past weekend, we had an issue where replication fell far behind on one of our databases. The replicated database is used for all sorts of reporting, so the immediate need was to identify processes that may have been affected by the incomplete data.
Now, there’s hundreds of stored procedures that reference the affected database; the trick is finding out which ones are relevant. To do this, I used the sys.dm_exec_query_stats DMV. This does two things for me. One, it shows me a list of stored procedures in cache, meaning they’ve been executed relatively recently and are probably relevant to the search. Secondly, it shows me the last execution time, which in some cases may have been before the issue, meaning I do not need to worry about re-running those processes.
Here’s the query I used:
SELECT DB_NAME(dest.[dbid]) AS 'databaseName' , OBJECT_NAME(dest.objectid, dest.[dbid]) AS 'procName' , MAX(deqs.last_execution_time) AS 'last_execution' FROM sys.dm_exec_query_stats AS deqs Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.[TEXT] Like '%yourTableName%' -- replace And dest.[dbid] IS Not Null -- exclude ad-hocs GROUP BY DB_NAME(dest.[dbid]) , OBJECT_NAME(dest.objectid, dest.[dbid]) ORDER BY databaseName , procName OPTION (MaxDop 1);
This will return results similar to:
databaseName procName last_execution -------------------- ------------------------------ ----------------------- AdventureWorks ufnGetProductListPrice 2009-08-03 09:57:25.390 AdventureWorksDW DimProductCategoryGet_sp 2009-08-03 09:59:05.820 AdventureWorksDW DimProductGet_sp 2009-08-03 09:58:38.370
I want to stress that this is *not* a list of all referencing objects, but rather a list of recently executed stored procedures that are still in memory. This list may not be accurate if your cache has recently been flushed or if you’ve recently rebooted your server.
Comments
8 Comments on Find Recently Executed Stored Procedures
-
SqlServerKudos on
Mon, 3rd Aug 2009 9:20 am
-
Matthieu on
Tue, 4th Aug 2009 12:15 am
-
Michelle Ufford on
Tue, 4th Aug 2009 6:00 am
-
Mark Horninger on
Tue, 4th Aug 2009 10:04 am
-
Matthieu on
Wed, 5th Aug 2009 12:19 am
-
Something for the weekend: SQL Server Links 07/08/09 | John Sansom - SQL Server DBA in the UK on
Fri, 7th Aug 2009 6:26 am
-
Kiran on
Mon, 10th Aug 2009 6:41 am
-
Michelle Ufford on
Tue, 18th Aug 2009 6:08 pm
Find Recently Executed Stored Procedures…
Kudos for a great Sql Server article – Trackback from SqlServerKudos…
Hello,
I can’t make this script work on sql server 2005.
The following error appears (I translate it from french) :
Msg 174, Level 15, State 1, Line 2
La fonction object_name requiert 1 argument(s).
=>
Msg 174, Level 15, State 1, Line 2
object_name function require 1 argument(s).
Do you know what is this ?
Thanks by advance,
@Matthieu You need to be on SQL Server 2005 SP2 or higher. In prior versions of SQL Server, the OBJECT_NAME function only accepts a parameter for object_id. You can read more about this here: http://msdn.microsoft.com/en-us/library/ms186301(SQL.90).aspx
To make this work for you, you could try the following query:
SELECT DB_NAME(dest.[dbid]) AS 'databaseName' , OBJECT_NAME(dest.objectid) AS 'procName' , MAX(deqs.last_execution_time) AS 'last_execution' FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.[TEXT] LIKE '%yourTableName%' -- replace And dest.[dbid] = DB_ID() -- exclude ad-hocs GROUP BY DB_NAME(dest.[dbid]) , OBJECT_NAME(dest.objectid) ORDER BY databaseName , procName OPTION (MaxDop 1);
then execute it for each database.
Pretty good article! Gotta love what you can do with the DMV’s
Thanks for your answer.
It’s working fine. I just missed something at the beginning. I was running the query outside the MASTER DB. Oups.
Thx
[...] Find Recently Executed Stored Procedures – Nice tip. [...]
Do you know how long the procedure calls remain in cache, approximately? Like, are we talking a few hours, or a few days, or a few weeks?
@Kiran I can’t really give you a good answer. It really depends on your server, i.e. how much activity is going on, how much available memory you have, etc. If I had to take a wild guess, I would say you’re looking at anywhere from 1 to 5 days, depending on the specifics of your environment.
Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog. ![]()





