Increasing BW cube compression performance

• First published on MSDN on Mar 13, 2014 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 https://launchpad.support.sap.com/#/notes/2116639
• The performance impact of SQL Server Columnstore is described in https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Performance-evolution-of-SAP-BW-on-SQL-Server/ba-p/368073

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 https://service.sap.com/sap/support/notes/1860832
  Contains old corrections and description of all issues regarding the cube compression

• SAP note https://service.sap.com/sap/support/notes/1951490
  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 https://service.sap.com/sap/support/notes/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 https://service.sap.com/sap/support/notes/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. 

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 https://service.sap.com/sap/support/notes/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. 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.