First published on MSDN on Jul 06, 2018 If you follow this blog, you know that in the last couple years, we have been adding more information to showplan, to make it a one-stop-shop for all your ...
Updated Apr 23, 2019
Version 5.0
Blog Post
Could you please explain the difference between 'the good' and 'the better' section more fully.
You say 'Let’s remove all the breaks and allow SQL Server to do its thing. '
There appears to be no statement change between the two blocks.
Further, I have been testing predicate pushdown and it seems to not work completely with some parts of a predicate ending up in the Clustered index scan and others left in the Filter.
Option recompile changes this behaviour and pushed all predicates down to the Clustered Index scan (effectively doubling performance of the query).