Published
Mar 23 2019 03:29 PM
454
Views
First published on MSDN on Jan 15, 2017
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-...
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
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.
Thanks,
Sunil Agarwal
SQL Server Tiger Team
Twitter | LinkedIn
Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam
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-...
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.
Thanks,
Sunil Agarwal
SQL Server Tiger Team
Twitter | LinkedIn
Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam