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://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Using-Columnstore-on-ERP-tabl...
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.
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://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Parallel-Processing-in-SAP/ba.... 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).
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:
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.
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.
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.