Performance Concern: Anti-Join And Top

Paul White explains a scenario in which an innocent-looking execution plan can hide something sinister:

Not every execution plan containing an apply anti join with a Top (1) operator on its inner side will be problematic. Nevertheless, it is a pattern to recognise and one which almost always requires further investigation.

The four main elements to look out for are:

  • A correlated nested loops (apply) anti join

  • Top (1) operator immediately on the inner side

  • A significant number of rows on the outer input (so the inner side will be run many times)

  • potentially expensive subtree below the Top

Read the whole thing.  This is a great way to wrap up the series.

Related Posts

Risks Of Using Resource Governor To Set Max DOP

Joe Obbish builds an example where Resource Governor’s CPU cap can actively harm query performance: I uploaded the query plan here if you want to look at it. This type of scenario can happen even without Resource Governor. For example, a compiled parallel query may be downgraded all the way to MAXDOP 1 if it can’t get […]

Read More

When Table Variables Have Realistic Estimates, Unrealistic Results May Occur

Milos Radivojevic wraps up a series on deferred compilation for table variables by looking at a hack which used to work but no longer does: With this change, the query is executed very fast, with the appropriate execution plan: SQL Server Execution Times: CPU time = 31 ms,  elapsed time = 197 ms. However, the LOOP hint […]

Read More

Categories

March 2018
MTWTFSS
« Feb Apr »
 1234
567891011
12131415161718
19202122232425
262728293031