Slow query performance because inaccurate cardinality estimate when using anti-semi join following upgrade from SQL Server 2000


We have a few customers who reported that some of their queries run slower following upgrade from SQL Server 2000 to SQL Sever 2005,  2008 and 2008 R2.   Specifically, queries experiencing the issue have anti-semi joins in the query plan and the join involves multiple columns as joining condition.

Anti-semi joins are results of query constructs like NOT EXISTS, NOT IN.   Here is an example of the query that would result in anti-semi join:

FROM tst_TAB1 t1
WHERE NOT EXISTS( SELECT *   FROM tst_TAB2 t2     WHERE t1.c1 = t2.c1 AND t1.c2 = t2.c2 )

Note that you only experience this issue when multiple joining columns are involved in the join as the example above.

If you examine the query plan, you can spot the issue.  In this query execution plan output (re-arranged for ease of explanation),  the left anti semi join (merge join) returned 2808 rows but the EstimateRows only estimate 1 row.


Inaccurate estimate will impact overall query plan and potentially slow performance.


This is a product regression and we have put out fixes for both SQL Server 2005, 2008 and 2008 R2.   Currently the fixes for SQL 2005 and 2008  are released.  Refer to KB for this fix.  SQL Server 2008 R2 fix is being planned and the same KB will be updated to reflect the fix once it becomes available.   Please note that you will need to enable trace flag 4199 to activate the performance fix.


Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support

Comments (7)

  1. Michael Codanti says:

    The support KB you link to and CU pack doesn't say anything about requiring trace flag 4199, is that an oversight?

  2. JackLi says:

    Regarding Michael's comment on trace flag 4199, yes it is an oversight.  The change request was submitted for the KB before this post but it hadn't made it to the web yet.

    You will need trace flag 4199 to activate the fix.

  3. Tinus van Tonder says:

    This fix did not work. Performance on my system is still very slow. In 2000 script ran 1 sec and in 2008 it takes 8 min?????????

  4. Michael Codanti says:

    I've have a query that runs in about 2.5 minutes without trace flag 4199 enabled, and 4.45 HOURS with trace flag 4199 enabled. This is on SQL Server 2008 R2 CU3. CU3 doesn't include this fix yet, and I see that CU10, which was just released, for SQL Server 2008 SP1 includes even more fixes for things that run slower than they did in SQL Server 2000.

    I'm hoping the next CU for 2008 R2 fixes my problem, but I doubt it. I just hope I don't run into a situation where we have to turn 4199 on for one query, and off for another…

  5. Kirtan Jabbar says:

    After upgrading from SQL Server 2008  to SQL Server 2008 R2  the performance for SSRS has decreased.  Reports are taking forever to complete.  Is there a date of when hotfix will be there for R2.  Thanks

  6. Linping says:

    Hi JackLi,

    I have migrate from sql2000 to 2008r2 64bit

    i apply hotfix from…/2345451

    then enable DBCC TRACEON (4199, -1)

    the query plan still same as before apply.

    nothing change.

  7. George says:

    Using SQL Server 2008 R2 SP3, with and without trace flag, the query runs in SQL Server 2000 in 3s but in SQL Server 2008 R2 it takes forever…

Skip to main content