sys.dm_exec_plan_attributes – Day 42 – One DMV a Day

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

Yesterday I have covered sys.dm_exec_cached_plans. Also I have mentioned that in this post I will write about sys.dm_exec_plan_attributes. We have seen one use case of sys.dm_exec_plan_attributes yesterday. As this DMV gives the attributes of a cached plan, we can use it to filter on results from cached plans.

Sys.dm_exec_plan_attributes provides properties about the cached plans. They include different set options, execution context details, cursor details, date and language settings, and database and object id. There are three columns returned as output. The first one is the attribute. Second is value of the attribute which is of type sql_variant and a bit column is_cache_use to indicate if the column is used for cache lookup for the plan.

More specific detail on the set option value is covered in MSDN. I will talk about a specific example where sys.dm_exec_plan_attributes is useful. Have you ever seen two plans in the cache for a single procedure? Yes, it is possible and quiet frequent in heavily used SQL instances. There are multiple reasons why SQL Optimizer generates a new plan when there is already one in the cache. One of them is due to different SET options.

Let us understand this by using a simple demo. I will run the same procedure which I ran yesterday on AdventureWorks2014 twice. By default SSMS has ARITHABORT set to ON. So I will first run it by default and then OFF the setting and run again.

USE adventureworks2014
GO
exec uspgetEmployeeManagers 10
GO
SET ARITHABORT OFF
GO
exec uspgetEmployeeManagers 10
GO

Now run the below query which we used yesterday and you will observe there are two plans for the single object.

SELECT DB_NAME(CAST(db.value AS INT)) AS DBName,
	 OBJECT_NAME(CAST(obj.value AS INT),CAST(db.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('AdventureWorks2014')  
	 AND obj.attribute = 'objectid'
	 AND obj.value = OBJECT_ID('uspGetEmployeeManagers')

Now run select on sys.dm_exec_plan_attributes with two plan_handles and you will observe the below results.

SELECT * FROM sys.dm_exec_plan_attributes(0x050013007A2CC30EF0F25F600100000001000000000000000000000000000000000000000000000000000000)
WHERE attribute = 'set_options'
SELECT * FROM sys.dm_exec_plan_attributes(0x050013007A2CC30E30FE5F600100000001000000000000000000000000000000000000000000000000000000)
WHERE attribute = 'set_options'

The SET option is the only difference between these two. In real troubleshooting scenarios we listen to customers telling procedure timeout from application. When we run the procedure in SSMS you will see the results come out pretty fast. In those cases make sure you are checking the right plan used by the application. Different applications have different default SET options. The options which will lead to not reusing cached plans are listed below.

ANSI_NULL_DFLT_OFF
ANSI_NULL_DFLT_ON
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
DATEFIRST
DATEFORMAT
FORCEPLAN
LANGUAGE
NO_BROWSETABLE
NUMERIC_ROUNDABORT
QUOTED_IDENTIFIER

You can read more about the plan cache in this whitepaper written by Greg Low. Now you know how to check the plan attributes using sys.dm_exec_plan_attributes. 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