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.
