1

I use sys.dm_db_index_usage_stats.last_user_seek, sys.dm_db_index_usage_stats.last_user_scan and sys.dm_db_index_usage_stats.last_user_lookup to query last time a table was read.

For views, functions and stored procedures I'm using sys.objects but it doesn't have any field for read datetime.

Is there any system table where I can see the last time these types of objects were read/used?

My objective is to find objects that aren't being used and possibly drop them.

2

Unfortunately, there is no 100% accurate way to determine this. However, you can get as close as possible by examining all code that could potentially make DB calls, as well as the definitions of all of your DB objects and SQL Server Agent jobs. There are often code paths that are infrequently accessed and hence could easily not show up in DMVs, extended events, SQL Profiler traces, or even custom logging. Checking ALL code will get you as close as possible.

The only area left out of an exhaustive code search is ad hoc queries that are not in code anywhere. In this case DMVs, traces, etc at least have the potential to identify usage, but are certainly no guarantee.

2

Unless you have a server side trace running or XEvent running, there is no guaranteed way of telling a particular object is used or not. Some objects might be referenced by a report that is ran once a year by your CIO or business user. So unless you have a concrete data for your business life cycle, there is always a room for error.

I would suggest that once you identify possible candidates for removal, do not drop them, instead rename them and after a year or so, drop them.

When using DMVs you need to log them to a persisted table as DMV data get flushed out due to instance restart, certain sp_configure changes, etc.

I have answered a similar question here that will get you started.

  • Thanks. I'm renaming objects before deleting. But I don't wanna do it only once, I'm creating some monitoring procedures to warn us when some stuff happens. I need the date of last use to sort it. – Hikari Aug 17 '16 at 13:05
1

Your best bet without built in logging would be using the below query:

select s.last_execution_time
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_query_plan (s.plan_handle) p
where object_name(p.objectid, db_id('DBNAME')) = @proc_nm
  • Thanks, but object_name(p.objectid, db_id('DBNAME')) is null for all records I got. – Hikari Aug 17 '16 at 13:10
1

There is a dedicated DMV for stored procedures.

select DB_NAME(eps.database_ID) AS DatabaseName
, OBJECT_NAME(eps.object_ID) AS ObjectName
, eps.type_desc
, eps.last_execution_time
, eps.execution_count 

from sys.dm_exec_procedure_stats eps

WHERE database_id = DB_ID()

The DMV will show the execution stats since the last server restart or detach (might need to confirm that). Meaning if a stored procedure has not been run since the database was attached or restarted, there will be no entry in this dmv. You can join it against the object table to find NULL's and assume what has not been run.

If you need trigger activity, you can use the same query as above except use sys.dm_exec_trigger_stats as the table.

  • Thanks, unfortunately this query didn't show any user sps :/ – Hikari Nov 25 '16 at 19:20
0

For DMV to give correct results for all databases, we can modify the script shared by @dfundako as below:

 SELECT
    DB_NAME(eps.database_ID)                    AS DatabaseName
   ,OBJECT_NAME(eps.object_ID, eps.database_id) AS ObjectName
   ,eps.type_desc
   ,eps.last_execution_time
   ,eps.execution_count   
FROM sys.dm_exec_procedure_stats eps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.