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

AS

BEGIN

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

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

END

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.

SELECT

b.[objectid] ,

OBJECT_NAME(b.objectid) ObjectName,

a.[objtype] ,

b.[dbid] ,

b.[text] ,

c.[query_plan],

a.plan_handle

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

sqlplan1

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.

sqlplan2

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 🙂

Advertisements

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:

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