The Curious Case of the Optimizer that doesn’t

re: The Curious Case of the Optimizer that doesn’t

Paul White — Thu, 03 May 2012 23:18:59 GMT

Hi Hugo,

The same considerations apply to the extensions in 2012 (e.g. SUM OVER ORDER BY) that use a Window Spool operator.

Almost all the optimizer's current exploration rules are based in relational theory and the algebraic equivalences derived from that theory (extended a bit to work with SQL's multi-sets, rather than strict sets), as you mention. These rules don't work directly with sequences, which were a new concept for the engine added in SQL Server 2005 (hence the 'Sequence Project' operator). Sequences have a order to them that sets (and multi-sets) do not have.

The optimizer reasons about sort orders for operations like Merge Join and Stream Aggregate, but this reasoning is based on multi-sets, and does not apply to sequences. Nevertheless, I had hoped for optimizer support for sequences to deepen more than it has since SQL Server 2005.

For example, the optimizer still cannot push a predicate past a sequence operation unless the sequence has the predicated column in its PARTITION BY clause, *and* the predicate evaluates against a runtime constant value. See the unpushed Filter in this query plan:

WITH MyCTE AS

(

SELECT

TerritoryID,

SalesLastYear,

Rank1 = RANK() OVER (ORDER BY SalesLastYear),

Rank2 = RANK() OVER (ORDER BY SalesYTD)

FROM Sales.SalesTerritory

)

SELECT

TerritoryID,

Rank1,

Rank2

FROM MyCTE

WHERE

MyCTE.TerritoryID < 5

ORDER BY

MyCTE.SalesLastYear;

You have my vote.

Paul

re: The Curious Case of the Optimizer that doesn’t

SomewhereSomehow — Fri, 04 May 2012 05:30:39 GMT

Hugo Kornelis,

Very interesting nuance, I'll add it to my KB.

Paul White,

How could we use predicate pushdown, to early filtering before ranking, in your example? If we do so, we may expect different logical sense, and different ranking numbers...

re: The Curious Case of the Optimizer that doesn’t

Paul White — Fri, 04 May 2012 06:27:15 GMT

@SomewhereSomehow TerritoryID would need to be part of the RANK's PARTITION BY clause. Point is, it only works with constants.

Paul

re: The Curious Case of the Optimizer that doesn’t

SomewhereSomehow — Fri, 04 May 2012 07:47:54 GMT

I talked about your example. You said still can not push if there is no partition by, and I thought how could it be ever possible to push without partition by in such queries.

Interesting note about a run-time constant, seems to be so indeed.

re: The Curious Case of the Optimizer that doesn’t

Paul White — Fri, 04 May 2012 08:13:44 GMT

That's right, the PARTITION BY requirement is logical. In 2005, the optimizer couldn't do the push even with a constant. 2008 on, it can, but not, in general, with a variable reference (without OPTION RECOMPILE or something else that causes a statement-level recompilation.)

re: The Curious Case of the Optimizer that doesn’t

SomewhereSomehow — Fri, 04 May 2012 09:35:35 GMT

Maybe it is worth saying, I did some experiments and couldn't find any other ways (for now) except option(recomple) to force predicate push down with variable after recompilation.

Altering schema, using temp tables, changing set options, auto update stats - all these stuff produce StmtRecompile event, but we see no pushdown. Even creating proc with recompile keyword isn't helpful. It seems to act like "is null or value" problem for some reasons.

re: The Curious Case of the Optimizer that doesn’t

Paul White — Fri, 04 May 2012 10:32:41 GMT

Of course - RECOMPILE is required for the parameter embedding optimization. I must have been thinking of table variable cardinality estimation, which is affected by a statement-level recompile. Thanks.