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.
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
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
