- First published on MSDN on May 08, 2017 by Martin Merdes.
- 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 and in SAP Note 2116639
In SAP customer support, we still see several customers running old SAP BW code that cannot leverage the improvements we delivered within the last years. In this blog, we want to demonstrate the huge performance improvements which can be achieved even without hardware replacements. Until 2011, the standard configuration of BW queries on SQL Server used only one database thread and was running against a BW cube with b-tree indexes. With the actual improvements, you can easily speed-up BW queries by factor 100!
Test Scenario
All tests were running with SQL Server 2016 on a former high-end server with 48 CPU threads constructed in the year 2008. This server does not even support modern vector operations (SIMD), which can be natively used by SQL Server 2016. We created 54 BW test queries with varying complexity and a varying number of FEMS filters. All queries were running against a BW cube with 100,000,000 rows. BW cube compression had been performed on 90% of all rows, resulting in 100 uncompressed BW requests. The queries had been created for our own, internal performance tests. They have not been modified or optimized for this blog. However, they might not be typical for your specific BW query mix.
Optimization levels
The BW queries were running against the following configurations:
- MAXDOP 1
This was the default SAP BW configuration from 2011 when running on Microsoft SQL Server: Standard BW cubes with rowstore (b-tree) indexes were used. All tables in SQL Server were PAGE compressed. BW queries were not using SQL Server intra-query parallelism. - PAGE-compression (Rowstore)
In this scenario, all SAP BW queries can use 8 CPU threads. Therefore, the SAP RSADMIN parameter MSS_MAXDOP_QUERY is set to 8. - COLUMN-compression (Columnstore)
Requires: SQL Server 2014 or higher, SAP BW 7.x
For this scenario we change the index structure of SAP BW cubes. A clustered columnstore index is applied on the cubes using SAP report MSSCSTORE. We do not recommend using the restricted read-only columnstore of SQL Server 2012 anymore. - FLAT Cube
Requires: Columnstore, SAP BW 7.40 (SP8) or higher
The next optimization step is to apply a new table structure for the BW cube. Therefore, the cube is converted to a Columnstore Optimized Flat Cube (which does not need an e-fact table and the dimension tables any more). - FEMS Pushdown
Requires: Flat Cube, SAP BW 7.50 (SP4) or higher
The last optimization uses a new SQL query structure, which implements the push down of FEMS filters from the OLAP engine to SQL Server.
All these features are described in detail in SAP Note 2116639 – SQL Server Columnstore documentation
Measured results
The below table contains the runtime of the 54 BW queries in the different configurations. The time consumed in SQL Server is displayed in purple, the time spend in the SAP BW OLAP engine is displayed in blue. A significant OLAP runtime is only observed for queries with a couple of FEMS filters. The runtime is rounded to full seconds. It has been measured by the SAP OLAP statistics in transaction ST03.
Comparing optimization levels
The following table shows the performance impact of each optimization step individually. Some optimizations may even be counterproductive for a particular BW query. However, the mix of all optimizations almost always results in great BW query performance.
In this mix of 54 BW queries, the slowest query with FEMS optimization (21 seconds) was even faster than the fastest query without any optimization (27 seconds). The average performance improvement was factor 121 faster!
Conclusion
The SAP BW code is permanently being updated for supporting new Microsoft SQL Server features like columnstore. Several BW improvements have been implemented to optimize SAP BW running on SQL Server. These optimizations have increased SAP BW query performance by two magnitudes within the last 6 years.
Therefore, customers should upgrade to SQL Server 2016 and apply the required SAP BW code soon.