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):
- The application almost always has different
SETsettings than SSMS (these are things like
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?
- 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';
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.
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.
5 thoughts on “Multiple Plans for an "Identical" Query”
Execution plan changes for a query when run on same server but with different parameter value. In one it uses clustered index, which is expected to be used for index seek. But for another value, a non-clustered index is used where Index scan is used,which should not have happened.
For me its baffling, can you help me understand why this behavior.
I don't know enough to understand exactly why, but does the statement have hints like OPTION (RECOMPILE) or has enough activity happened between executions that the original plan has been aged out of the cache?
There has been no HINTS in use. Aging out of plan from cache cannot happen as to recreate the issue, same query with different parameter value was executed one after another. The only difference is number of records associated with those parameter values. Good plan has 28000 records associated to parameter value, where as Bad plan has 40000 records associated to parameter value.
Please share some actual plans somewhere. Hard to guess what *might* be going on.
Aaron, Appreciate your intent in helping me-out.
Both SQL plans are available at given below links.Hope this can give you much better picture about my baffling.
Not good plan
Comments are closed.