Tracking the last execution date of a query

Question

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.

Answer 1

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

Answer 2

Go ahead and run this:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

This code snippet will tell you wen SQL Server was last restarted. If you are not running something that captures code to analyze later from the dynamic management views, you will only have historical data up until either the last restart or when that data has aged out of cache.

You can run this while changing the database in the code to match yours:

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('msdb')
ORDER BY deqs.last_execution_time DESC

Written by Tommy_o on Stack Exchange.

I would recommend to start capturing and aggregating data like this regularly if you can and have the administrative / spec capacity to do so. Sp_Whoisactive is a great start to doing this. When you do upgrade to 2016, you can use the Query Store to capture similar data when you enable that feature.