SQL: Get the Cached Query Plan
Author: jason
Date: 2014-06-25 11:32:23
Category: Musing

Our programmers will often ask for the Query Plan of the stored procedure. Here is a great script from Simple Talk showing how to look in the cache for specific query text and show its query plan.

https://www.simple-talk.com/sql/t-sql-programming/dmvs-for-query-plan-metadata/

Code

SELECT
deqs.plan_generation_num
, deqs.execution_count
, deqs.last_execution_time
--, deqs.plan_handle
--, deqs.sql_handle
, execText.text
, deqp.query_plan
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
WHERE execText.text LIKE '%SOME QUERY TEST%'




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009