sys.dm_exec_cached_plan_dependent_objects – Day 43 – One DMV a Day

Hello Geeks and welcome to the Day 43 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.

Today I will be covering sys.dm_exec_cached_plan_dependent_objects. This DMF gives you the information about dependent objects on the current plan. You need to pass the valid plan_handle and get the dependent objects.

Sys.dm_exec_cached_plan_dependent_objects is mapped to sys.dm_os_memory_objects on memory_object_address. This will help you get to the memory object associated with every plan in the cache.

Let us get the details from sys.dm_exec_cached_plan_dependent_objects. I will create procedure to use a cursor and fetch only one row from a table. This way I will have the procedure itself and the cursor as objects for the plan.

CREATE PROCEDURE usp_getRandomEmployee
AS
BEGIN
	DECLARE cur1 CURSOR
	FOR SELECT eId, eName FROM Repl_Pub.dbo.pubLogger_tbl

	OPEN cur1

	FETCH NEXT FROM cur1

	CLOSE cur1
	DEALLOCATE cur1
END

Now execute the procedure at least once and run the below query to get the plan handle.

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

sys.dm_exec_cached_plan_dependent_objects

Pass the plan handle to sys.dm_exec_cached_plan_dependent_objects to see the dependent objects.

SELECT * FROM sys.dm_exec_cached_plan_dependent_objects(0x05000600034DCC6D109E64610100000001000000000000000000000000000000000000000000000000000000)

sys.dm_exec_cached_plan_dependent_objects

In the output you can see that it has displayed two objects. The compiled Plan is related to the plan of the procedure. The cursor is related to the cursor inside the procedure. The objects like CLR also are shown when used in sys.dm_exec_cached_plan_dependent_objects.

Tomorrow I will be covering another execution related DMV. 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

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 *