Freigeben über

Choosing SQL Server compression type for SAP

  • Artikel

Depending on the SAP and SQL Server release, there are different compression types supported by SAP NetWeaver. Unfortunately the naming convention used by SAP and Microsoft is not consistent. Therefore there was some confusion about this in the past. In SAP notes the following compression types are mentioned: database compression, data compression, row compression, page compression, UCS-2 compression (aka unicode compression), index compression, vardecimal storage format (aka decimal compression or vardecimal data type)

 

SQL Server compression types Any type of compression currently requires the Enterprise or Datacenter Edition of SQL Server. The Developer Edition also works fine, but is not supported by SAP for productive usage. The following SQL Server releases introduced new compression features:

 

-        SQL Server 2005 SP2
In SQL Server 2005 SP2 the vardecimal storage format was released. This is a subset of the row compression type, which was released in SQL Server 2008. Due to the restrictions of the vardecimal storage format it should be considered as deprecated.

 

-        SQL Server 2008
Row compression and page compression were released in SQL Server 2008. A table or index has one of the following compression types:

 

o   NONE: The table or index is not compressed.

 

o   ROW: All rows are stored in variable length. This compression type has no CPU overhead, but saves disk space and reduces disk I/O.

 

o   PAGE: Page compression is always performed on top of row compression. It even saves more disk space. However, there might be a higher CPU load on the database server.

 

Whether you compress the data (heap, clustered index) or the (non-clustered) indexes: The feature is called data compression. The term “index compression” is not used by Microsoft.
The term “database compression” is not well-defined. It is often used for any kind of compression (data compression or vardecimal storage format).

 

-        SQL Server 2008 R2
SQL Server uses UCS-2 (not UTF-8) to encode Unicode characters. SQL Server 2008 R2 stores row or page compressed UCS-2 characters more efficient than SQL Server 2008. The space usage of a UCS-2 character is roughly the same as a UTF-8 character would need, once a table is row or page compressed. This feature is called UCS-2 compression or simplyunicode compression. However, you may consider it as an improved data compression, not as a new compression type.

 

Supported compression types by SAP Depending on the SAP release, the following compression types are supported for an ABAP application server. For a JAVA application server there is currently no compression support.

 

-        Vardecimal storage format
The vardecimal storage format was used by SAP only for BW fact tables. The indexes of BW fact tables did never benefit from it. As of SQL Server 2008, new BW fact tables are not created using the vardecimal storage format any more, since there is no advantage of it compared to row compression. SAP considers the vardecimal storage format as a deprecated feature.

 

-        Row compression for tables (heap or clustered index)
Before September 2010 the SAP Data Dictionary created new tables on SQL Server 2008 with compression type ROW and all new non-clustered indexes with compression type NONE. When converting an existing table manually in SAP transaction SE14 or during an SAP upgrade, the compression type of a table was not changed (it stayed NONE, ROW or PAGE). However, non-clustered indexes were always created with compression type NONE.

 

-        Row compression for (non-clustered) indexes and
Page compression for tables and indexes as of September 2010
In September 2010 SAP released correction instructions in SAP note 1459005 for SAP Basis 7.00 and all newer releases. These corrections will be included in future SAP BASIS support packages. With the corrected SAP data dictionary all new indexes will be created with the same compression type as their table. In addition you can change the default compression type for new tables, for example to page compression. SAP calls the new feature to compress indexes (as row or page compressed) index compression. This is actually not a SQL Server compression type. It is simply the name of an SAP feature.

 

Conclusion You need at least SQL Server 2008 for data compression (row or page compression).  To benefit from UCS-2 compression you need at least SQL Server 2008 R2. Therefore you should upgrade to SQL Server 2008 R2. All SAP NetWeaver releases supported on SQL Server 2008 are also supported on SQL Server 2008 R2.
Upgrade your SAP system at least to release 7.00 and apply SAP note 1459005. As a result you can use index compression. Use the report MSSCOMPRESS to compress existing tables and indexes. The report is attached to SAP note 1488135.
When starting to apply SQL Server data compression, use row compression first. This compression type does not increase the CPU resource consumption. However, the deployment of page compression should be planned based on your available CPU resources on the database server.
Do not use the vardecimal storage format any more. It is a deprecated feature and only used in a very limited way by SAP. SAP note 991014 describes the usage of the vardecimal storage format in detail.

Comments

  • Anonymous
    September 24, 2010
    Thanks for the Summary on Data Compression...Good blog

  • Anonymous
    September 27, 2010
    Hi guys, Firts of all thanks for the very useful blogs on this page! I just want to share my experiences with compression types. I've spend much time in analyzing compression types of MSSQL 2005/2008/2008R2 as we are facing severe prblems with our database growth (~250GB/month). We created an pilot landscape with the follwoing details: SAP ERP 6.0 Ehp4 MSSQL 2005 SP3 Database Files: 6 DBSize: ~2,8TB Actually we planned to upgrade to MSSQL 2008 and activate the ROW Compression and PAGE for the top tables: Table - original Size (in KB) FAGLFLEXA 468.735.456 GLPCA 221.840.104 COEP 180.493.240 GLFUNCA 145.818.584 BSIS 173.277.360 MSEG 119.981.520 COSP 185.482.488 MLIT 78.152.944 CKIS 59.663.928 MLCR 41.851.240 RESB 57.898.504 Since we are salso going to change the # of database files we decided to perform a complete unload/load (R3Load) of the database - therefore sideaffecft of database defragmentation is included. Thereby we got the following results: Datbase Files: 16 Database Size: 1,3 TB (saving rate of 46%!) Top Tables table - size in KB FAGLFLEXA 275.517.536 GLPCA 92.261.552 COEP 96.724.408 GLFUNCA 72.203.088 BSIS 42.198.048 MSEG 33.133.472 COSP 57.172.136 MLIT 68.282.976 CKIS 37.873.880 MLCR 24.249.032 RESB 33.625.496 We performed many performance test and didn't observed any negative impact of the ROW and Page Compression (we have currently no issues concering CPU Load). As MSSQL 2008 R2 was released recently, we decided to include this opertunity in our project. Again we performed an unload/load of the database (using perocedure describes in 1488135 with &compression_row& for cretab and creind in DDLMSS.TPL). Afterwards we used report MSSCOMPRESS to activate PAGE compression (data + index) for our to tables. Thereby we got the follwoing results: Datbase Files: 16 Database Size: 0,66 TB (saving rate of ~77%!) Top Tables FAGLFLEXA 45.441.856 GLPCA 22.287.880 COEP 16.729.792 GLFUNCA 14.716.672 BSIS 14.436.288 COSP 12.798.344 MSEG 12.739.584 MLIT 8.439.936 CKIS 6.862.592 MLCR 6.157.952 RESB 5.606.592 Based on this results we decided to analyse the performance again in detail and if there aren't any negative impact we will go ahead with DEV, QUA and PRD system. Cheers, Christoph christoph.jakobeit[at]cbs-consulting.de