Increasing BW cube compression performance

• This blog has been re-published at https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Increasing-BW-cube-compression-performance/ba-p/367736

• 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

Last year we already released an SAP note regarding BW cube compression performance, containing a correction instruction. The text of this note has been updated last week.

We released additional, new correction instructions for the BW cube compression in a new SAP note together with other improvements for the SQL Server column-store. The two notes are:

• SAP note 1860832 - Performance improvement for BW cube compression
  Contains old corrections and description of all issues regarding the cube compression
• SAP note 1951490 - SQL Server Column-Store for SAP BW Aggregates
  Contains new, additional corrections – also for BW cube compression

The root cause of the performance issues with BW cube compression are described in detail in the first SAP note. This BLOG is intended as a step-by-step guide for increasing BW cube compression.

Step 0: Upgrade SQL Server

The BW cube compression uses the SQL Server MERGE command in SQL Server 2008 and all newer releases. SQL Server 2005 and older releases do not support the MERGE command, resulting in increased transaction log usage and slower runtime of the BW cube compression.

Step 1: Solve issue with HASH JOIN HINT

This issue can only occur when running one of the following support packages:

Applying the correction instructions of SAP note 1860832 will solve this issue.
If you are still not satisfied with the BW cube compression performance, then continue with step 2.

Step 2: Solve issue with execution plan

The optimal SQL Server execution plan of the MERGE command uses the P-index of the e-fact table. In almost all cases an optimal execution plan is used. However, you can force the usage of the P-index by activating on optimizer hint for the MERGE command. Therefore you have to apply the correction instructions of SAP note 1951490 and set the following RSADMIN parameter:

• MSS_COND_HINT = X

Use SAP report SAP_RSADMIN_MAINTAIN for setting the RSADMIN parameter. If the RSADMIN parameter does not help, then remove it again. Details about applying the correction instructions are described in BLOG SQL Server Column-Store with SAP BW Aggregates.

Step 3: Optimize fields of P-index

The P-index of the e-fact table is not needed for SAP BW queries in most cases. Then main intention of this index is to speed-up the BW cube compression. In the past, this index was not optimal in all cases. After applying SAP note 1951490 the P-index of new cubes will be optimized for BW cube compression, in particular when using BW partitioning.
Since the P-index is not needed for BW queries, the index check in SAP transactions RSA1, RSRV and report MSSCSTORE does not report a red flag for a suboptimal P-index. As a result, the suboptimal P-index will not be automatically converted during normal SAP BW process chains. It is implemented this way to prevent an unexpected, one-time increase of the process chain runtime after installing the next SAP BW support package. However, you can configure the automatic, one-time conversion of the P-index by setting the following RSADMIN parameter:

• MSS_P_INDEX_REPAIR = X

The P-index will then be repaired the next time a “Create Index” process is running in a BW process chain. Alternatively, you can convert the P-index manually using SAP transactions RSA1 or RSRV. You can also display the status of the P-index in report MSSCSTORE, as described in BLOG SQL Server Column-Store with SAP BW Aggregates . An index with suboptimal P-index looks like this:

In transaction RSA1 or RSRV you can also see the yellow status of the cube, but not the explanation in DB-Status.