Find Recently Executed Stored Procedures

August 3, 2009 by Michelle Ufford
Filed under: SQL Tips, Syndication, T-SQL Scripts 

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.

Tags: , , ,

Comments

8 Comments on Find Recently Executed Stored Procedures

  1. SqlServerKudos on Mon, 3rd Aug 2009 9:20 am

    2. Find Recently Executed Stored Procedures…

    Kudos for a great Sql Server article – Trackback from SqlServerKudos…

  2. Matthieu on Tue, 4th Aug 2009 12:15 am

    3. 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,

  3. Michelle Ufford on Tue, 4th Aug 2009 6:00 am

    4. @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.

  4. Mark Horninger on Tue, 4th Aug 2009 10:04 am

    5. Pretty good article! Gotta love what you can do with the DMV’s

  5. Matthieu on Wed, 5th Aug 2009 12:19 am

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

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

    7. [...] Find Recently Executed Stored Procedures – Nice tip. [...]

  7. Kiran on Mon, 10th Aug 2009 6:41 am

    8. 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?

  8. Michelle Ufford on Tue, 18th Aug 2009 6:08 pm

    9. @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.