SQL Server – Tracking Last Execution Date of a Query

plan-cachesql server

My manager is requesting to know when was the last time a number of reports were last ran to see if they are getting used. He requested info from our vendor and the vendor if coming to me for SA level permission to dig through the relative SQL Server.

Obviously, I won't be doing that but I would like to know is there a way to track the last time a specific query has been executed?

This database resides on an old SQL Server 2008 R2 install so I am sure my options are limited if not non-existent especially since he wants to look back as far as 18 months.

Just figured I would ask in case there might be at least a partial way to go back in history and track this information.

Best Answer

When were your servers last booted? Unless you have some form of auditing running, then that is a far back as you will be able to query the sys.dm_exec_query_stats view and sys.dm_exec_sql_text() table-valued function.

You can then use the following statement to see when a statement was last executed:

SELECT sdest.TEXT , sdeqs.last_execution_time, *
FROM   sys.dm_exec_query_stats AS sdeqs
   CROSS APPLY sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest

However, there are some restrictions with this approach:

  • Size of Memory reserved for SQL Server instance
  • Execution of DBCC FREEPROCCACHE
  • Execution of DBCC FREESESSIONCACHE
  • Execution of DBCC FREESYSTEMCACHE('ALL')
  • other possible reasons for SQL Server to free up cache including, but not limited to:
    • Server reboots
    • Instance restarts
Related Question