Customer Experience with Columnstore on ERP

  • [アーティクル]

SAP released the report MSS_CS_CREATE a few months ago. Using this report, customers can create an additional Nonclustered Columnstore Index (NCCI) on any SAP ERP table. This has already been described here: https://blogs.msdn.microsoft.com/saponsqlserver/2017/04/13/using-columnstore-on-erp-tables.

In the meanwhile, several customers tested this feature. They reported performance improvements for reporting scenarios using huge aggregations (see below). Other customers had feature requests for the report MSS_CS_CREATE. A new version of this report is now available in SAP Note 2419662 - Implementing Columnstore Indexes for ERP tables. You have to re-apply the correction instructions of this SAP Note to get the code update.

Performance Improvements in SAP CO-PA

One of our customers is using the NCCI for SAP CO-PA. A huge performance improvement has been achieved simply by increasing SQL Server intra-query parallelism. For additional information regarding parallelism in SAP, see https://blogs.msdn.microsoft.com/saponsqlserver/2017/05/18/parallel-processing-in-sap. You might increase the SQL Server configuration option "max degree of parallelism", but this has an impact on all SAP queries (not only on CO-PA). Therefore, the customer decided using a SQL Server optimizer hint in the ABAP code. Just using this hint resulted in a performance improvement of factor 10. Adding an NCCI on the largest CE1, CE2, and CE4 tables further improved the performance to an overall acceleration of factor 77 (from 771 to 10 seconds).

Using ABAP Optimizer Hints for forcing an index

Having rowstore and columnstore indexes at the same time on the same table can become a challenge for the SQL Server Query optimizer. Therefore, you might have to add an ABAP optimizer hint. For example, to enforce the ABAP index IN1 (name of the index in SAP DDIC) on table ERPTEST, you have to add the following hint:
%_HINTS MSSQLNT 'TABLE ERPTEST abindex(IN1)'.

Take care that the table name and index name are in UPPER case. If the SELECT consists of a single table (no JOIN involved), then there is no need to explicitly use the table name. In this case, you can use &TABLE& instead:
%_HINTS MSSQLNT 'TABLE &TABLE& abindex(IN1)'.

You can use several optimizer hints within a single SELECT, for example:
SELECT MAX( msgnr ) sprsl
  FROM t100 INTO l_t_result
  GROUP BY sprsl
  %_HINTS MSSQLNT 'OPTION maxdop 8'
          MSSQLNT 'OPTION hash group'.

You can also combine optimizer hints in a single line:
%_HINTS MSSQLNT 'OPTION maxdop 8  OPTION hash group'.

When using an optimizer hint for SQL Server intra-query parallelism, you should not hard code the degree of parallelism. Instead, you can use a variable. (The same is done in SAP BW with the RSADMIN parameter MSS_MAXDOP_QUERY). The ABAP code could look like this:

Used Optimizer Hints in SAP CO-PA

Our customer added a few SQL Server optimizer hints in the ABAP code of the CO-PA templates. It is a good idea to add optimizer hints using an ABAP variable. When setting this variable in an external form routine (e.g. GET_SQL_HINT), then you can change the hints without having to change the CO-PA code again:

Dependent on the input parameter (name of the CO-PA form routine), GET_SQL_HINT calculates the required optimizer hint and fills the ABAP variable SQL_HINT. Even if the report Z_COPA_SQL_HINTS (which contains GET_SQL_HINT) does not exist, you do not get an error. In this case, the variable SQL_HINT is empty and no optimizer hint is added.

  • For the SELECTs with aggregation on the CE1, CE2, and CE4 tables, a MAXDOP and a HASH GROUP hint have been added.
    Therefore, several form routines in the template include RKEVRK2B_READ_COST were changed: The call of GET_SQL_HINT and the hint %_HINTS MSSQLNT SQL_HINT has been added. Hereby, the variable SQL_HINT is set to 'OPTION maxdop 16 OPTION hash group'. The following example shows the form routine OPEN_CURSOR_NO_HOLD_CE1 from the template include RKEVRK2B_READ_COST:

  • For the SELECTs without aggregation on the CE1, CE2, and CE4 tables, a different hint has been added.
    It turned out, that there are some SELECTs without aggregation, where the existing rowstore index would be a much better choice than the columnstore index. However, caused by the re-usage of SQL Server execution plans for different selective parameters, the columnstore index was sometimes used. This resulted in a high CPU load and suboptimal performance. You could force the required index using an optimizer index hint as described above. However, our customer decided to use a different optimizer hint, which solved the issue: OPTIMIZE FOR UNKNOWN
    Therefore, several form routines in the template include RKEVRK2A_POST have been changed: The call of GET_SQL_HINT and the hint %_HINTS MSSQLNT SQL_HINT has been added. Hereby, the variable SQL_HINT is set to 'OPTION optimize for unknown'. The following example shows the form routine READ_ALL_PAOBJNRS_BY_CHARVALS from the template include RKEVRK2A_POST:

    Keep in mind that changing the templates above has no impact until the ABAP code is regenerated using the new templates. Therefore, the operating concerns must be regenerated using SAP transaction KEA0.

Improvements in SQL Server 2017

SQL Server 2017 allows the online creation of an NCCI. In SQL Server 2016, it was only possible to create an NCCI offline. Therefore, a shared lock was held during the whole runtime of the index creation. This blocked all data modifications (INSERTs, UPDATEs, DELETEs) while the NCCI was created. As of SQL Server 2017, you can now choose in report MSS_CS_CREATE, whether you want to use the online option or not. Keep in mind, that creating an index online takes longer and consumes tempdb space. In return, you do not block any other SAP users while creating the index.

Improvements in SAP report MSS_CS_CREATE

The NCCI cannot be transported using the SAP transport landscape. Therefore, you have to create the NCCI on the development-, consolidation-, and productive-system separately. This works fine with report MSS_CS_CREATE even on a productive system, which is configured in SAP as Not Modifiable. However, you cannot delete an NCCI using SAP transaction SE11 on a Not Modifiable SAP system. Therefore, report MSS_CS_CREATE has now a Delete Index button (Del Indx):

The second improvement in MSS_CS_CREATE is the Online option. It is greyed out in the screenshot above, because this SAP system is running on SQL Server 2016.

Conclusion

An NCCI can speed-up reporting performance on an SAP ERP system running on SQL Server 2016 or 2017. However, it is probably not useful for tables with a high transactional throughput (permanently many concurrent data modifications in the table). Based on the customer scenario, you can create an NCCI index on the tables of your choice.