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 –
Now, as the stored procedure is executed, a new entry has inserted in the system objects.
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 🙂