Join Simplification With Table Variables

Erik Darling has an example of how adding a key constraint to a table variable allowed the optimizer to filter it out:

I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret.

Okay, so it’s just embarrassing.

Anyway. I had these two queries. Which are actually the same query twice. The only difference is the table variable definition.

Click through for the demo and additional information.

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

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930