Multiple Plans for an "Identical" Query

I often see people struggle with SQL Server when they are seeing two different execution plans for what they believe is the same query. Usually this is discovered after other observations, such as vastly different execution times. I say they believe it is the same query because, sometimes it is, and sometimes it isn't.

One of the most common cases is when they are testing a query in SSMS and getting a different plan than the one they get from their application. There are potentially two factors at play here (which could also be relevant when the comparison is NOT between the application and SSMS):

1. The application almost always has different SET settings than SSMS (these are things like ARITHABORT, ANSI_NULLS and QUOTED_IDENTIFIER). This forces SQL Server to store the two plans separately; Erland Sommarskog has treated this in great detail in his article, Slow in the Application, Fast in SSMS?
    
2. The parameters used by the application when its copy of the plan was first compiled could have been very different, and led to a different plan, than those used the first time the query was run from SSMS – this is known as parameter sensitivity. Erland talks about that in depth too, and I am not going to regurgitate his recommendations, but summarize by reminding you that testing the application's query in SSMS is not always useful, since it's quite unlikely to be an apples-to-apples test.

There are a couple of other scenarios that are a little more obscure that I bring up in my Bad Habits & Best Practices talk. These are cases where the plans aren't different, but there are multiple copies of the same plan bloating the plan cache. I thought I should mention them here because they always catch so many people by surprise.

cAsE and whitespace are important

SQL Server hashes the query text into a binary format, which means that every single character in the query text is crucial. Let's take the following simple queries:

USE AdventureWorks2014;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
GO
SELECT StoreID FROM Sales.Customer;
GO -- original query
GO
SELECT  StoreID FROM Sales.Customer;
GO ----^---- extra space
GO
SELECT storeid FROM sales.customer;
GO ---- lower case names
GO
select StoreID from Sales.Customer;
GO ---- lower case keywords
GO

These generate the exact same results, obviously, and generate the exact same plan. However, if we look at what we have in the plan cache:

SELECT t.[text], p.size_in_bytes, p.usecounts
 FROM sys.dm_exec_cached_plans AS p
 CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
 WHERE LOWER(t.[text]) LIKE N'%sales'+'.'+'customer%';

The results are unfortunate:

So, in this case, it is clear that case and whitespace are very important. I talked about this in much more detail last May.

Schema references are important

I've blogged before about the importance of specifying the schema prefix when referencing any object, but at the time I wasn't fully aware that it also had plan cache implications as well.

Let's take a look at a very simple case where we have two users with different default schemas, and they run the exact same query text, failing to reference the object by its schema:

USE AdventureWorks2014;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
GO

CREATE USER SQLPerf1 WITHOUT LOGIN WITH DEFAULT_SCHEMA = Sales;
CREATE USER SQLPerf2 WITHOUT LOGIN WITH DEFAULT_SCHEMA = Person;
GO

CREATE TABLE dbo.AnErrorLog(id INT);
GRANT SELECT ON dbo.AnErrorLog TO SQLPerf1, SQLPerf2;
GO

EXECUTE AS USER = N'SQLPerf1';
GO
SELECT id FROM AnErrorLog;
GO
REVERT;
GO
EXECUTE AS USER = N'SQLPerf2';
GO
SELECT id FROM AnErrorLog;
GO
REVERT;
GO

Now, if we take a look at the plan cache, we can pull in sys.dm_exec_plan_attributes to see exactly why we are getting two different plans for identical queries:

SELECT t.[text], p.size_in_bytes, p.usecounts, 
  [schema_id] = pa.value, 
  [schema] = s.name
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
INNER JOIN sys.schemas AS s ON s.[schema_id] = pa.value
WHERE t.[text] LIKE N'%AnError'+'Log%' 
AND pa.attribute = N'user_id';

Results:

And if you run it all again but add the dbo. prefix to both queries, you will see there is only one plan that gets used twice. This becomes a very compelling argument for always fully referencing objects.

SET settings redux

As a side note, you can use a similar approach to determine if SET settings are different for two or more versions of the same query. In this case we are investigating the queries involved with multiple plans generated by different calls to the same stored procedure, but you could also identify them by the query text or query hash.

SELECT p.plan_handle, p.usecounts, p.size_in_bytes, 
  set_options = MAX(a.value)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
WHERE t.objectid = OBJECT_ID(N'dbo.procedure_name')
AND a.attribute = N'set_options'
GROUP BY p.plan_handle, p.usecounts, p.size_in_bytes;

If you have multiple results here then you should see different values for set_options (which is a bitmask). That's just the start; I'm going to cop out here and tell you that you can determine what set of options are enabled for each plan by unpacking the value according to the "Evaluating Set Options" section here. Yes, I'm that lazy.

Conclusion

There are several reasons why you may see different plans for the same query (or what you think is the same query). In most cases you can isolate the cause pretty easily; the challenge is often knowing to look for it in the first place. In my next post, I will talk about a slightly different subject: why a database restored to an "identical" server might yield different plans for the same query.