THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

The Curious Case of the Optimizer that doesn’t

This blog has moved! You can find this content at the following new location:

Published Friday, May 4, 2012 1:13 AM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Paul White said:

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:






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

       Rank2 = RANK() OVER (ORDER BY SalesYTD)

   FROM Sales.SalesTerritory








   MyCTE.TerritoryID < 5



You have my vote.


May 3, 2012 7:18 PM

SomewhereSomehow said:

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...

May 4, 2012 1:30 AM

Paul White said:

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


May 4, 2012 2:27 AM

SomewhereSomehow said:

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.

May 4, 2012 3:47 AM

Paul White said:

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.)

May 4, 2012 4:13 AM

SomewhereSomehow said:

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.

May 4, 2012 5:35 AM

Paul White said:

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.

May 4, 2012 6:32 AM

Leave a Comment


About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement