Recently I had a situation, I worked with my colleague to develop and test a very complex t-SQL query on his machine. Next day he was on emergency leave with no access to internet, I had to implement few more changes and deploy.
I was lucky, SQL Server was not rebooted! I was able to use SQL Cache plans to pull the latest Query from the Server and deploy on time… 🙂
USE msdb GO SELECT top 100 e.text, creation_time, last_execution_time, execution_count, d.plan_handle ,d.sql_handle , db_name(dbid) as DBName FROM sys.dm_exec_query_stats d CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS e where e.text like '%plan%' -- Filter with keyword order by last_execution_time desc --
Included DB Name in the results;