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.
August 4th, 2009 - 00:15
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,
August 4th, 2009 - 06:00
@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:
then execute it for each database.
August 4th, 2009 - 10:04
Pretty good article! Gotta love what you can do with the DMV’s
August 5th, 2009 - 00:19
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
August 10th, 2009 - 06:41
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?
August 18th, 2009 - 18:08
@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.