This Blog will provide information about running SAP applications on SQL Server and Windows. The Blog is written by folks of Microsoft who are working with SAP and SQL Server for more than a decade.
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 typesAny 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 SP2In 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 2008Row 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 R2SQL 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 SAPDepending 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 formatThe 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 andPage compression for tables and indexes as of September 2010In 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.
ConclusionYou 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.
Thanks for the Summary on Data Compression...Good blog
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
Actually we planned to upgrade to MSSQL 2008 and activate the ROW Compression and PAGE for the top tables:
Table - original Size (in KB)
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%!)
table - size in KB
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:
Database Size: 0,66 TB (saving rate of ~77%!)
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.