This is the official team web log for SQL Server engineering (TIGER) team – the customer success team that focuses on learning customer workloads running on SQL Server, providing guidance to use SQL Server to solve their business challenges and integrating that feedback to enhance the product.

SQL Server 2016 introduces BatchMode execution model for aggregates computed over a set of rows defined by Over Clause. A set of rows so defined is referred to as ‘window’ and the aggregates are being computed on this set of rows are referred to as Window Aggregates. Please refer to an excellent introduction on Window functions/aggregates by one of its most passionate promoter Itzik Ben-Gen, SQL Server MVP, author and an excellent teacher!

Let us show case this using an example query as follows
SELECT Productkey, OrderQuantity as curqty,
Sum (OrderQuantity) OVER (ORDER BY ProductKey) AS TotalQuantity
FROM FactResellerSalesXL_CCI
WHERE orderdatekey in (20060301,20060401)

SQL Server 2014

The picture below shows the actual execution plan. You will note that the aggregate computations was done in RowMode using stream aggregate. As expected, the SCAN of the columnstore index was in the BatchMode. The execution time of the query

SQL Server Execution Times:

CPU time = 139 ms, elapsed time = 396 ms

SQL Server 2016

The actual execution plan shown below has a new ‘Window Aggregate’ operator that executes in BatchMode. Also, the execution plan is lot more simpler. The execution time with these changes is as follows. Note, the CPU taken is much lower with this execution.

SQL Server Execution Times:

CPU time = 62 ms, elapsed time = 228 ms

While these results may not appear as dramatic on my laptop, the picture below shows the performance gains with Window Aggregates on a Server class machine with large DW database. The orange bar represents the query speed up we got with Window Aggregate operator in BatchMode. The highest speed up we saw was 289x!!

Like before, you get this performance boost by just upgrading to SQL Server 2016! This performance improvement is available with 130 DBCOMPAT.