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.

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , . Bookmark the permalink.

9 Responses to Find Recently Executed Stored Procedures

  1. Pingback: SqlServerKudos

  2. Matthieu says:

    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. @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 says:

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

  5. Matthieu says:

    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. Pingback: Something for the weekend: SQL Server Links 07/08/09 | John Sansom - SQL Server DBA in the UK

  7. Kiran says:

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

  9. Just wanted to say thanks for this script, it helped me out today. Just to add to the discussion, I made a couple of easy modifications that made it more helpful for me.

    1. Limit results to one database name instead of by table name
    2. Order by last execution time

    – Shows recently executed stored procedures for a given database
    Select Object_Name(dest.objectid, dest.[dbid]) As ‘CalledprocName’
    , 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.[dbid] Is Not Null — exclude ad-hocs
    and DB_Name(dest.[dbid]) = ‘YourDatabaseNameHere’
    Group By db_name(dest.[dbid])
    , Object_Name(dest.objectid, dest.[dbid])
    Order By last_execution desc
    Option (MaxDop 1);

    I thought about putting it into a function for easy call but it’s a fairly short script so may not be necessary. Anyway, thanks again.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>