共用方式為

Columnstore Index Performance: SQL Server 2016 – Window Aggregates in BatchMode

  • 發行項

SQL product team has made significant improvements in columnstore index functionality, supportability and performance during SQL Server 2016 based on the feedback from customers. This blog series focuses on the performance improvements done as part of SQL Server 2016. Please refer to one of the blogs No performance cliff for details on the database used for the examples here. A full list of columnstore blogs can be found at https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-list-of-blogs-on-columnstore-index-published-by-sql-server-product-team/

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 mswinaggr-1

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

winaggr-2

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!! winaggr-3

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

 
Thanks,
Sunil Agarwal
SQL Server Tiger Team Twitter | LinkedIn
Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam