Why are there different execution plans for the same 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.
Can a SQL Server query generate the same plan?
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: 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: The results are unfortunate:
How are stored procedures and executesqlthe plans compiled?
For both stored procedures and sp_executesqlthe plan is compiled according to the value of the first set of parameters it sees.
What makes an application different from a SSMS?
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 SET settings than SSMS (these are things like ARITHABORT, ANSI_NULLS and QUOTED_IDENTIFIER ).
Why do I get two plans for the same query?
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: 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.
Can a query optimizer generate a different query plan?
The SQL Query Optimizer may generate a different query plan depending upon the compatibility level for your database. Higher compatibility levels provide more intelligent query processing capabilities. For more information on query processing, see Query Processing Architecture Guide.