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!
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.
The BW queries were running against the following configurations:
All these features are described in detail in SAP Note 2116639 – SQL Server Columnstore documentation
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.
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!
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.