Optimization of Window Functions
This chapter describes the optimization of window functions in Microsoft SQL Server. It assumes that you are familiar with analyzing graphical query-execution plans and with the core operators such as Index Scan, Index Seek, Sort, Nested Loops, Parallelism, Compute Scalar, Filter, Stream Aggregate, and so on.
The chapter starts with a few general aspects of optimization of window functions, such as indexing guidelines, ordering NULLs last, improving parallelism with APPLY, row-mode versus batch-mode processing, and batch mode on columnstore versus batch mode on rowstore.
The chapter then delves into the specifics of optimization of each group of window functions: ranking functions, aggregate and offset ...