SQL Server 2016 improvements for SAP (BW)
- This blog has been re-published at https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/SQL-Server-2016-improvements-for-SAP-BW/ba-p/367981
- This blog post might be outdated by now. The latest documentation regarding SQL Server Columnstore on SAP is available in https://www.sap.com/documents/2019/04/023e5928-487d-0010-87a3-c30de2ffd8ff.html
SAP recently released support for SQL Server 2016 for some SAP NetWeaver based systems, see https://blogs.msdn.microsoft.com/saponsqlserver/2016/10/04/software-logistics-on-sql-server-2016. SQL Server 2016 contains many new features which can be used by SAP. For SAP BW, particularly the improved columnstore features are relevant. An overview of SQL Server Columnstore in SQL Server 2014 is published here: https://blogs.msdn.microsoft.com/saponsqlserver/2015/03/24/concepts-of-sql-server-2014-columnstore. The most important columnstore improvements in SQL Server 2016 are discussed below:
Batch mode available for more operators and with maxdop 1
Since SQL Server 2014 there are three types of parallelism used for columnstore processing: Firstly, several SQL Server queries are running in parallel for a single SAP BW query (for example a SQL query against the f-fact table and another SQL query against the e-fact table). Secondly, a single SQL query can use many CPU threads at the same time (maxdop). Thirdly, a single CPU thread can process a set of rows, typically up to 900 rows (batch mode), rather than processing row by row (row mode). SQL Server 2016 can use the batch mode for much more operators, for example the sort operator. An overview is available at https://msdn.microsoft.com/en-us/library/dn935005.aspx.
In SQL Server 2014, single-threaded queries running under MAXDOP 1 or with a serial query plan cannot use batch mode. Therefore, we recommended a relatively low value for the SAP RSADMIN parameter MSS_MAXDOP_QUERY, which controls the maxdop setting used for BW queries (see https://blogs.msdn.microsoft.com/saponsqlserver/2013/03/19/optimizing-bw-query-performance). However, in SQL Server 2016 batch mode can be used with maxdop 1. Therefore, queries are still pretty fast, even when there is only one CPU thread available during high workload.
Columnstore uses vector instructions (SSE/AVX)
In addition to intra-query parallelism (maxdop) and batch mode, SQL Server 2016 uses another kind of parallelism: CPU vector instructions (AVX or SSE). You benefit most from this feature on most modern CPU generations. You can even use this feature in virtualized environments. More recent versions of virtualization layers used for private and public cloud like Azure, are supporting SSE/AVX instructions as well. SQL Server usage of SSE/AVX is described here: https://blogs.msdn.microsoft.com/bobsql/2016/06/06/how-it-works-sql-server-2016-sseavx-support/
Rowgroup Compression also merges rowgroups
In SQL Server 2014, you can perform columnstore rowgroup compression using the SQL command ALTER INDEX REORGANIZE. In SQL Server 2016, this SQL command performs additional optimizations: It also merges small rowgroups into a larger one for improving query performance. This is documented here: https://msdn.microsoft.com/en-us/library/dn935013.aspx. In SAP BW, there is no need to run this SQL command manually. This is automatically done during BW Cube Compression and when executing an Index Repair (within a BW Process Chain).
Parallel update of sampled statistics
Update Statistics on BW fact tables can be very time consuming, in particular for a BW Flat Cube (see https://blogs.msdn.microsoft.com/saponsqlserver/2015/03/27/columnstore-optimized-flat-cube-in-sap-bw). By default, an Update Statistics on the fact table(s) is performed during BW Cube Compression. SQL Server 2016 can use up to 16 CPU threads for running Update Statistics with default sample rate (vs. one thread in SQL Server 2014). See also https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/05/23/query-optimizer-additions-in-sql-server.
Writable Nonclustered Columnstore Index (NCCI)
In SQL Server 2016, you can create an additional, writable columnstore index on top of a table, which already has a primary key and other rowstore indexes. This feature is planned to be used in future SAP BW releases for implementing the columnstore on other BW objects besides the fact tables.
NCCIs are also useful for SAP ERP. SAP will not deliver NCCIs in standard ERP systems. However, customers can create a NCCI for some tables of their ERP system on their own. A detailed description and recommendations for such a project will be published in a BLOG next year.
This list of SQL Server 2016 features is by far not complete. For Example, we did not mention additional indexes on top of a Clustered Columnstore Index (CCI) or the new SQL Server Query Store. The intention of this blog was to give an overview of the features, which justify a database upgrade to SQL Server 2016 for SAP BW.