CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

  • Comments 5

 

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 http://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

Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post
  • The support KB you link to and CU pack doesn't say anything about requiring trace flag 4199, is that an oversight?

  • 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.

  • 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?????????

  • 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...

  • 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

Page 1 of 1 (5 items)