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

2014/06/20:
Included DB Name in the results;

-Vijred

Similar Posts:
https://vijredblog.wordpress.com/2014/01/31/how-to-search-a-textstring-in-sql-stored-procedure/
https://vijredblog.wordpress.com/2014/04/15/sql-dependency-analysis-identify-impacted-modules/

Advertisements
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s