Improved SAP compression tool MSSCOMPRESS
- This blog has been re-published at https://techcommunity.microsoft.com/t5/Running-SAP-Applications-on-the/Improved-SAP-compression-tool-MSSCOMPRESS/ba-p/368001
- 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
SAP program MSSCOMPRESS is a tool for reducing disk space usage of SAP NetWeaver based systems. In addition, it is often used as a monitoring tool. MSSCOMPRESS has been released by SAP over 6 years ago. In the meanwhile, several improvements have been implemented. The latest version of MSSCOMPRESS is available as an attachment of SAP Note 1488135.
The basic features of MSSCOMPRESS are described in https://blogs.msdn.microsoft.com/saponsqlserver/2010/10/08/compressing-an-sap-database-using-report-msscompress. In this blog we want to describe the new features and functionalities. The user interface of MSSCOMPRESS has changed a little bit:
- Total table size
- Data and index size
- DB compression type of data and indexes
- Number of rows, indexes and partitions
- A checkbox, which flags those tables which use a HEAP
- Number of uncompressed rows in columnstore rowgroups
(be aware, that this number may not be accurate for a few minutes after compressing the columnstore rowgroups of this particular table)
The SQL query for retrieving this information has been tuned several times in the past.
New filter options
Traditionally, you can filter the list of tables in MSSCOMPRESS using the Filter Options. The Name Filter now allows the wildcards star and question mark. If you want to see all tables of a particular SAP BW cube, you can use the filter /*/?CUBE* (see the example above). The list now includes an automatic sum of some columns. In this example, the sum of the table sizes is the total cube size.
In addition, you can now use SAP ALV (ABAP List Viewer) Filters. By right-clicking in the ALV list, you can set ALV Filters or sort the ALV list. This feature is particularly useful, when using MSSCOMPRESS as a monitoring tool. To avoid confusion, compressing "Filtered Tables" is disabled, once you use the traditional Filter Options and the new ALV Filters at the same time. However, you can still compress using "Selected Tables".
DB compression type COLUMNSTORE_ARCHIVE
For B-trees (rowstore) you can choose the DB compression type (NONE, ROW or PAGE) separately for data (clustered index or heap) and indexes. For columnstore, you can now choose between DB compression types COLUMNSTORE and COLUMNSTORE_ARCHIVE. This feature has been added to support the full SQL Server functionality in SAP. However, we strongly recommend keeping the SAP default DB compression types: COLUMNSTORE for columnstore indexes and PAGE for rowstore. MSSCOMPRESS does not touch existing indexes by default, if the DB compression type is already correct. By choosing one of the "Force Data/Index/CS Rebuild" check boxes, an index rebuild is always executed, even when not changing the DB compression type.
This is a new feature in MSSCOMPESS, which actually has nothing to do with the DB compression type. It performs a rowgroup compression of columnstore indexes. For SAP BW, this tasks should be performed in SAP BW process chains, see https://blogs.msdn.microsoft.com/saponsqlserver/2016/11/14/simplified-and-faster-sap-bw-process-chains. There is no need to use MSSCOMPRESS for compressing columnstore rowgroups in SAP BW. However, one may want to create an additional Nonclustered Columnstore Index (NCCI) in SAP ERP on SQL Server 2016. A detailed description regarding this will be published in a BLOG next year. In such a scenario, you can use MSSCOMPRESS to schedule a rowgroup compression job each night.
By choosing "Compress Rowgroups" the mode of MSSCOMPRESS changes from DB compression (which performs an index REBULD) to rowgroup compression (which performs a columnstore index REORGANIZE). Therefore, all DB compression type options are greyed out. The Online and MAXDOP options are also greyed out, because a rowgroup compression is always performed online and single-threaded. MSSCOMPRESS only performs a rowgroup compression, if there are uncompressed rows in a columnstore index of the chosen table. By choosing "Force Reorganize", a rowgroup compression will always be performed. This is useful as of SQL Server 2016, since the rowgroup compression also merges small rowgroups into one larger rowgroup. See https://blogs.msdn.microsoft.com/saponsqlserver/2016/11/11/sql-server-2016-improvements-for-sap-bw.
The newest version of MSSCOMPRESS is faster and provides additional filter options. SAP consultants "misuse" MSSCOMPRESS as a monitoring tool, which enables a quick look on all database tables of an SAP NetWeaver system.