Improved SAP compression tool MSSCOMPRESS

  • Article

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.

Overview

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:

msscompress_1When starting MSSCOMPRESS or pressing the button "Refresh", the following information is retrieved from SQL Server for all tables of the SAP database:

  • 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.

msscompress_2

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".

msscompress_3

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.

Compress Rowgroups

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.

msscompress_4

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.

Summary

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.

Comments

  • Anonymous
    February 07, 2017
    If I have already done this on SQL Server 2008 R2 -- but getting ready to upgrade to SQL Server 2016 (new build/migrate) -- would I need to perform a MSSCOMPRESS again?
    • Anonymous
      April 20, 2017
      The tool MSSCOMPRESS has been improved by adding new features. Installing the new version of this tool does not change the compression state of your tables and indexes on the disk.If your tables are already PAGE compressed on SQL Server 2008 R2, then there is no need to compress them again after upgrading to SQL Server 2016. Things are different, if the tables were originally compressed on SQL Server 2008 (not R2), because SQL Server 2008 R2 introduced a better ROW and PAGE compression (UNICODE compression)