The mysterious case of why your procedure has two query plans & the bitwise AND

Article
02/06/2017

Have you ever been looking at your plan cache and found that you have more than one plan for any given query? The reason for this is likely to be that the query has been run with different SET options.

Let's look at an example of a simple stored procedure that is created to return rows from a table in the WideWorldImporters sample database.

A pretty simple and innocuous query it would seem but that all depends on how we execute it.

and we get this result

However, if we execute it like this

then we get this message

You will see that in these two code samples I am using a different set dateformat value. In the first one it is mdy meaning that the 01/06/2016 is interpreted as January 6th 2016 and in the second sample the dateformat is dmy which results in the date value being interpreted as 1st June 2016. This causes the result set to be different and this is a reason that SQL Server keeps a different plan for each combination of TSQL AND the associated connection settings.

I'm working from SSMS here which comes with some environmental settings already configured in the Tools | Options* if you have changed your settings here then you may see different results from the examples illustrated.

So, how do we know what settings are being used in the connections that are running code that results in multiple plans in our cache? To explore this we need to review some dmvs.

Firstly let's see the two plans in the cache by looking in sys.dm_exec_query_stats and sys.dm_exec_sql_text

Sure enough, we have two plans in cache for the sbo.usp_GetOrders_001 procedure

We need the plan_handle value to join to another couple of dmvs so lets adjust the above code to pop the values into a temporary table and then join that temporary table to sys.dm_exec_cached_plans, sys.dm_exec_plan_attributes and sys.dm_exec_query_plan

Now we can see that the query plans have differences in the date_format value

One has a date_format value of 2 and the other has a date_format value of 1. If you click the query_plan in SSMS then you will be able to review the two query plans and see the predicate value being used to filter rows from the Sales.Orders table is different.

For SET DATEFORMAT this is pretty clear and open to evaluation in the dmv but what if something like SET CONCAT_NULL_YIELDS_NULL has been used? Things get a little more complex. Lets create a new stored procedure, execute it with different settings and review the plan cache, just as we have already for the DateFormat setting.

A quick adjustment to the dmv query to filter and we can see the attributes for our new procedure

Again, two plans as expected but we arent including hte date_format attribute as it isnt a factor in this example. The results for my query is like this.

This time we have a different value for the set_options value for each of the plans. OK, we can see they are different in the same way that the date_format value was different and therefore we know why there are two plans but how do we know what the difference is exactly?

The set_options value from sys.dm_exec_plan_attributes is a bitmasked column so it needs some mathematics done on it to disentangle the information held in it.

Let's divert for a moment into what a bitmask is. Imagine that we have a business selling widgets. We have a range of optional features on our widgets and any combination of features can be installed on the widget or not. If we want to record the information about each widget then we could create a really wide table with a column for each feature and then record a 1 or 0 in the column to show whether the specified widget had that feature or not.

This comes with some problems such as a big piece of work refactoring code to work with the table if we build a widget with a new feature etc etc. If we could have one single column that represented the options for each then it would be simpler. By assigning a value to each feature and summing the values we could store just the ID and the sum. Lets assume Turbo has a value of 1, Deluxe has a value of 2, GTi has a value of 4, Metallic has a value of 8, Sunroof has a value of 16 and Charger is 32. This way each combination of features would have a distinct sum.

The trick now is testing which feature is contributing to the value. How can we tell if WidgetID 6 had the GTi? This is where the & operator comes in to play.

We know that the GTi feature has a value of 4 and the Features value for WidgetID 6 is 43 so we simply need to execute this code

If this returns a 0 then the GTi feature is not installed, if the result is 4 then it is installed. The & is known as the bitwise AND - it compares a value with a specified bit and returns 0 if that bit is off or the value tested if the bit is on. Lets see what WidgetID 7 gives us

This is how the set_options value works in sys.dm_exec_plan_attributes so lets see how we can wrangle the information we want out of that value.

First of all we need to reference the MSDN article for the sys.dm_exec_cached_plan_attributes dmv and put the values into a table.

From here we can easily pass in the value from the query plan set_options attribute and see whether CONCAT_NULL_YIELDS_NULL is set for our queries (one had the value 4347 and the other was 4339.

It's a little step then to see all the set_options for for a given value

Code samples to save you typing in all the code from the images :

01-planattributesdateexample-sql 02-planattributesnullexample-sql 03-planattributesbitvalues-sql widgets-sql

Bonus SSMS tip

* - Did you know you can open the Tools Options dialog from the Find textbox in the Toolbar? Just type > Tools.Options in the Find textbox and hit enter …