We have a complex stored procedure working in our database. Some of the statements are executed with different query plans depending on database compatibility level / Trace Flags. The different query plans leads to very significant differences in execution time of that stored procedure (some seconds to more than one hour).
See the linked QueryPlans.
I tested with these configurations on a SQL Server 2019:
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
a) compatibility level 100 (best performance) QueryPlan
b) compatibility level 150 (poor performance) QueryPlan
c) compatibility level 150 + LEGACY_CARDINALITY_ESTIMATION (best performance) QueryPlan
insert into AttribInt (oid, aid, category, attrflag, attrStatusFlag, value)
select tm.new_oid, a.aid, a.category, a.attrflag & ~@ATTRFLAG_FROM_TYPICAL_VALUE, a.attrStatusFlag, a.value
from AttribInt as a
join #tblMerge as tm on (a.oid = tm.oid)
where tm.new_oid is not null
and tm.matchType = @MR_COPY
and a.aid <> @aidObjectTag
The statement (see above) is a join between a temp. table "#tblMerge" with about 300 rows and a data table "AttribINT" with about 230 million rows. We want to copy all rows in AttribINT (about 1200 rows) which are referenced by OID from the rows in #tblMerge.
with a) and c) the join was performed with a Nested Loop and Index Seek - so as expected
with b) the join used Merge Join with Clustered Index Scan over the 230 million rows.
What is the reason for that behavior?
What can be the solution except setting down the compatibility level or activating LEGACY_CARDINALITY_ESTIMATION ?
Thanks for your support!