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.

Simon MᶜKenzie
  • 103
  • 4
Hikari
  • 1,553
  • 11
  • 27
  • 41

5 Answers5

2

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.

marc_s
  • 8,932
  • 6
  • 45
  • 51
dfundako
  • 392
  • 2
  • 13
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.

Community
  • 1
Kin Shah
  • 62,225
  • 6
  • 120
  • 236
  • 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
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.

Solomon Rutzky
  • 68,731
  • 8
  • 152
  • 292
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
Zi0n1
  • 111
  • 4
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
Kasim Husaini
  • 101
  • 2