Performance evolution of SAP BW on SQL Server

  • Article

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:

  1. 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.
  2. 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.
  3. 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. An overview of SQL Server 2014 (and 2016) columnstore is attached in the following blog: https://blogs.msdn.microsoft.com/saponsqlserver/2015/03/23/sql-server-2014-columnstore-released-for-sap-bw. Detailed requirements are documented in SAP Note 2116639 – SQL Server Columnstore documentation
  4. 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). The Flat Cube is described in https://blogs.msdn.microsoft.com/saponsqlserver/2015/03/27/columnstore-optimized-flat-cube-in-sap-bw.
  5. 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. A brief overview of this feature can be found here: https://blogs.msdn.microsoft.com/saponsqlserver/2017/03/06/bw-queries-by-factors-faster-using-fems-pushdown.

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.

bw_perf1

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!

bw_perf2

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.

Comments

  • Anonymous
    October 02, 2017
    Its a very promising feature for cubes but like I asked last year we are interested in DSO's to be columnstore, wondering if you have made any progress on that?Thanks