Hugo Kornelis digs into a performance improvement the SQL Server optimizer has for window functions:
This is part twenty-four of the plansplaining series. In the previous part, I explained the execution plans for basic window functions, with and without a window frame. Especially the latter group performed quite poorly in the examples. So let’s now look at an optimization that SQL Server can apply to most cases, that prevents this rather bad scaling.
Click through to see what this is, how it works, and when the optimizer is able to use it.