Choosing SQL Server compression type for SAP

Choosing SQL Server compression type for SAP

  • Comments 2

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 simply unicode 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 compress
ion 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.

 

Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
  • Thanks for the Summary on Data Compression...Good blog

  • 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

Page 1 of 1 (2 items)