Posted in Development

SQL Server – How to retrieve SQL plans from Cache

Hi friends, in this blog post I will tell you how we can retrieve the SQL plan from the cache. In SQL Server there are multiple DMV (Dynamic Management Views) and DMO (Dynamic Management Objects) which can be used to retrieve the information about execution plan.

Sys.dm_exec_query_plan is most commonly used DMO which is used to retrieve execution plans.

Similarly, sys.dm_exec_sql_text is used to retrieve the information like definition of object, object name.

I will write a query using these objects to find the information of execution plan.

Here, first I will create a stored procedure and then execute that stored procedure to generate the execution plan and then we will used these DMVs and DMOs to retrieve the execution plan.

CREATE Procedure Check_Plan



 INSERT INTO Test_Plan (ID, Name, CreateDate, Country)

 VALUES (5,’KD’, Getdate(), ‘INDIA’)


In this stored procedure I am simply inserting the data into the sql table. Now to execute the stored procedure we will execute the command –

EXEC Check_Plan

Now, as the stored procedure is executed, a new entry has inserted in the system objects.


b.[objectid] ,

OBJECT_NAME(b.objectid) ObjectName,

a.[objtype] ,

b.[dbid] ,

b.[text] ,



FROM sys.dm_exec_cached_plans a

CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) b

CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c

WHERE b.objectid = 1330103779


As we can see that in query plan column execution plan is stored in XML format.

To see the execution plan you can just simple click on the link and an execution plan will open in new tab.


So in this way we can see the execution plan of sql objects and use it perform analysis for performance tuning.

That’s all for the day folks 🙂



I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

One thought on “SQL Server – How to retrieve SQL plans from Cache

Leave a Reply

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

You are commenting using your 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