SQL query execution history from cache plans

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

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;


Similar Posts:

This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , , , , , . Bookmark the permalink.

2 Responses to SQL query execution history from cache plans

  1. Pingback: How to Search a Text/String in SQL Stored Procedure | Blog for reference – Vijred

  2. Pingback: SQL-dependency analysis-Identify impacted modules | Blog for reference – Vijred

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s