Tracking the last execution date of a query

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply