Partager via

SQL Server Page Compression became default for SAP

  • Article

SAP supports SQL Server Data Compression (row- and page-compression) for all applications based on ABAP application server as of NetWeaver (NW) Basis 7.00 and SQL Server 2008. Per default, SAP used to create tables with compression type ROW and indexes with compression type NONE. This decision was made in the past for the following reasons:

  • Indexes were not compressed because of some issues with SAP BW and missing development and test resources at that time. These issues have been fixed over a year ago. Since then, customers can use index-compression with SAP NW. However, the default behavior of SAPINST was not changed.
  • Page-compression was not used by default, because of the expected higher CPU load on the database server. These concerns have been resolved within the last 12 months. On productive customer systems the decreased number of I/Os always overcompensate the slightly higher CPU usage caused by page-compression. The overall SAP response time is much better for a page-compressed SQL Server database in comparison to a row-compressed database. If there is a resource bottleneck on the database server, it is typically the I/O system, not the CPU resources, which are causing the bottleneck.

Due to the great experience SAP made with productive page-compressed SQL Server databases, it was decided to change the default for new SAP NW ABAP installations to page-compression (for data and indexes).

How SAP changed the default compression type

In order to change the default behavior of an SAP installation, it was decided, not to change SAPINST. Instead, the default behavior of R3LOAD and the SAP Data Dictionary (DDIC) was changed.

The template file DDLMSS.TPL defines the compression type for data and indexes. This file is created during data export by SAP (to create the installation export DVDs) or by a customer (for a system copy or a DB/OS migration). DDLMSS.TPL is used by R3LOAD during SAP system installation or system copy. The newest version of R3LDCTL creates a template file for page-compression. The newest version of R3LOAD is able to use the template file DDLMSS.TPL for page-compression. SAP note 1505884 (R3load: compression for table / index) tells the necessary versions of R3LOAD and R3LDCTL. It also describes, how to change the template file DDLMSS.TPL manually.

One year ago, the Data Compression support for SAP DDIC was introduced by SAP note 1459005 (Enabling index compression for SQL Server). By setting the profile parameter dbs/mss/compression you can change the compression type, which is used by the DDIC when creating new tables. The default used to be row-compression. After applying the correction instruction of SAP note 1581700 (PAGE compression support for DDIC & Hom./Het. System Copy), the default is changed to page-compression. This correction will be included in Basis Support Packages of all SAP releases as of NW 7.00. This may result in an unexpected behavior, if you have not set the profile parameter dbs/mss/compression. By applying the according Basis Support Package, the default compression type (for newly created tables) will change from row-compression to page-compression. As a result, you may have tables with different compression type within the same database.

However, SAP works fine with different compression types. A few tables are neither created by R3LOAD nor by the DDIC, for example all tables created by report MSSPROCS. You can manually change the compression type for tables and indexes using the report MSSCOMPRESS as described in SAP note 1488135 (Database compression for SQL Server).

Installing future SAP products with page-compression

All future SAP products and Service Releases based on NW ABAP will create the database tables and all secondary indexes with page-compression out of the box. There is nothing you need to configure in SAPINST or in the SAP profiles.

Installing already released SAP products with page-compression

The official and by SAP supported way to install a page-compressed, older SAP release is to

  • Install the SAP system with the default settings (row-compressed)
  • Apply the corrections of SAP note 1581700
  • Perform page-compression on all tables using report MSSCOMPRESS

However, you may speed-up this process as follows. Once SAPINST has copied the executables to the local disk, replace R3LOAD with the newest version. Modify the file DDLMSS.TPL as described in SAP note 1505884 to use page-compression. Then start the data load. Once the installation has finished, immediately apply the corrections of SAP note 1581700. Use report MSSCOMPRESS to detect non page-compressed tables and compress them. Since this procedure is not officially supported by SAP, it should only be used by an experienced SAP consultant.

Performing system copies with page-compression

If you follow all the instructions of the system copy note 888210 (NW 7.**: System copy supplementary note), then the target database will automatically be created page-compressed. The key points are

  • Import the correction instructions of SAP note 1581700 on the source system
  • Use the newest version of R3LDCTL.exe for the data export
  • Use the newest version of R3LOAD.exe for the data import

However, this only works for a system copy using R3LOAD. Using simply detach/attach or backup/restore of the database will not change the data compression type of any table.