Based on observing output from Traceflags 8605, 8606 and 8607 (and comments from i-one), I have this as the order in which the logical trees are generated.

  • Converted
  • Input
  • Simplified
  • Join-Collapsed
  • Before Project Normalization
  • After Project Normalization
  • Output

Outside of the ordering of Converted and Simplified, I have not however found independent verification that my assessment is correct.

Is this the correct order?

  • 1
    You may look at Optimizer Deep Dive by Paul White, see "Optimization Pipeline" section. Don't be misguided by "Input Tree ... To see this tree directly, we can use undocumented trace flag 8605" though. I believe that Paul meant 8606 (actually Converted and Input trees are equal for some queries, so it may not be important in the context of the article). – i-one Jul 27 '20 at 12:27
  • @i-one Thanks for that. I'd read the post series, but had incorrectly assumed it meant that the input tree was first. I've also found another reply from Paul White that further confirms what you've highlighted: dba.stackexchange.com/questions/244383/constant-scan-spooling – Paul Holmes Jul 27 '20 at 13:14

Yes that is the correct order.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.