sys.dm_exec_cached_plans – Day 41 – One DMV a Day

Hello Geeks and welcome to the Day 41 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

In the 24th day of this series I have covered sys.dm_os_memory_cache_entries. I have used sys.dm_exec_sql_text and sys.dm_exec_cached_plans in that post. I have covered the first one on day 32. Today I will be talking about sys.dm_exec_cached_plans.

Whenever a procedure or trigger is executed for the first time the execution plan is generated. This plan is the estimated plan and is saved in the plan cache for future use. The cache is needed as generating a plan is more costly than using the exiting one. Having said that, when a procedure is called it doesn’t use the cached plan directly. It checks if it can generate a better plan. If generating a better plan is more costly than using the existing plan it goes for the cached plan. Sys.dm_exec_cached_plans shows all the cached plans in the plan cache.

It would be heavy and meaning less to view all data from sys.dm_exec_cached_plans. All you need from the cache is the plan of procedure you are interested in. So I will be joining this DMV with sys.dm_exec_plan_attributes which I am going to cover tomorrow. But for today let’s just concentrate on sys.dm_exec_cached_plans.

I will use the below query to get the plan handle from sys.dm_exec_cached_plans. The first time I execute the plan for the procedure will not exists in the cache. Then I will execute the procedure once. The second time I will get the plan handle for the procedure.

SELECT DB_NAME(CAST(db.value AS INT)) AS DBName,
	 OBJECT_NAME(CAST(obj.value AS INT),CAST(db.value AS INT)) AS objName,
	 ecp.plan_handle,
	 ecp.refcounts,
	 ecp.usecounts,
	 ecp.pool_id,
	 ecp.cacheobjtype,
	 ecp.bucketid
FROM sys.dm_exec_cached_plans ecp (NOLOCK)
CROSS APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) db 
CROSS APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) obj
WHERE db.attribute = 'dbid'
	 AND db.value = DB_ID('AdventureWorks2012')  
	 AND obj.attribute = 'objectid'
	 AND obj.value = OBJECT_ID('uspGetEmployeeManagers')

You will see no results unless the plan exists in the cache. So execute the procedure now.

EXEC uspGetEmployeeManagers 1
GO

Output from sys.dm_exec_cached_plans after the procedure is executed.

sys.dm_exec_cached_plans

Now to check the estimated plan in the cache use the plan handle in sys.dm_exec_query_plan. You can also see the additional columns like reference count and use count of the cached plan.

SELECT * FROM sys.dm_exec_query_plan(0x050005007A2CC30ED007B1E30200000001000000000000000000000000000000000000000000000000000000)
GO

sys.dm_exec_cached_plans

By clicking on the xml plan you can see the plan in the new plan window. As mentioned earlier in this post, you know what I will be covering tomorrow. Stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *