Bad cardinality estimate for subquery after patches / QUERYTRACEON 4199
We run the latest version of SQL Server 2017(14.0.3048.4).
When running the following query, it runs fine:
(SELECT TOP 1 ROLEDEFINITION.NAME -- Rolle for ansvarlig dommer
INNER JOIN PERSON ON PERSON.ID = AKTOER.PERSON_ID
INNER JOIN ROLEDEFINITION ON AKTOER.ANSATTROLLEDEFSNAPSHOT = ROLEDEFINITION.ID
WHERE AKTOER.SAK_ID = S.SAK_ID AND PERSON.ORGUNIT_ID = S.DOMMER_ORG_ID
ORDER BY COALESCE(TODATE, '9999-01-01') DESC)
FROM SAK S
INNER JOIN #AktuelleSaker AS AKTUELLE_SAKER ON AKTUELLE_SAKER.SAK_ID = S.SAK_ID option(recompile)
However, If we add option(recompile, QUERYTRACEON 4199)
it use a very wrong cardinality estimate. I included the good(no query optimizer fixes) and bad plan(query optimizer fixes).
I have tried to run UPDATE STATISTICS WITH FULLSCAN on all the tables involved, without affecting the outcome.
The good plan takes 0 seconds, and the bad plan takes 8-12 seconds.
If I read the plans correctly, it seems that with the query fixes turned on, after resolving the joins for the temptable/table, and starts performing the correlated subquery pr row, it somehow thinks its a good idea to start with the DOMMERORGID predicate, and it estimates to in total have 16.538 rows, while in reality it got 7.365.157
The good plan estimated 67.765 and got 99.264
Czarny, Derek commented
I think I am seeing similar results with SQL Server 2016 SP2 CU5