共用方式為

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:

SELECT t1.*
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.

image

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

Solution:

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 https://support.microsoft.com/kb/2222998 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

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

  • Anonymous
    September 02, 2010
    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.

  • Anonymous
    September 19, 2010
    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?????????

  • Anonymous
    September 22, 2010
    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...

  • Anonymous
    September 27, 2010
    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

  • Anonymous
    October 12, 2014
    Hi JackLi, I have migrate from sql2000 to 2008r2 64bit i apply hotfix from support2.microsoft.com/.../2345451 then enable DBCC TRACEON (4199, -1) the query plan still same as before apply. nothing change.

  • Anonymous
    April 21, 2015
    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...