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:
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.
How does the planner / optimizer work in PostgreSQL?
The planner/optimizer starts by generating plans for scanning each individual relation (table) used in the query. The possible plans are determined by the available indexes on each relation. There is always the possibility of performing a sequential scan on a relation, so a sequential scan plan is always created.
What is the task of a planner / optimizer?
The task of the planner/optimizer is to create an optimal execution plan. A given SQL query (and hence, a query tree) can be actually executed in a wide variety of different ways, each of which will produce the same set of results.
How to select plan handle from syscacheobjects?
SELECT * FROM sys.dm_exec_cached_plans PLANS CROSS APPLY sys.dm_exec_sql_text (PLANS.plan_handle) AS Q where [text] like ‘%SOMETHING%’ order by [text] vs select * from syscacheobjects where sql like ‘%SOMETHING%’ order by [sql] This is not true. Your plans are different and you can see it looking at pagesused and setopts columns.
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 ).