PURPOSE
The report MSSCOMPRESS compresses Microsoft SQL Server tables tosave disk space. You can perform the compression in dialog or in an SAPbatch job.

PREREQUISITES
Microsoft SQL Server supports compression as of SQL Server 2008.Therefore, MSSCOMPRESS only runs on SQL Server 2008 or higher.

FEATURES
MSSCOMPRESS uses different forms of database compression:

• Compression types

A table or index has one of the following compression types:
NONE: The table or index is not compressed.
ROW: All rows are stored in variable length. This compressiontype has no CPU overhead, but saves disk space and reduces disk I/O.
PAGE: Page compression is always performed on top of rowcompression. It even saves more disk space. However, there might be ahigher CPU load on the database server.

• Index compression

Each table and index has its individual compression type (NONE, ROW orPAGE). Originally, SAP only supported data compression, but not indexcompression. As a result, tables were created with compression type ROWand indexes with the compression type NONE. You can use compression fornew indexes after having applied recent SAP basis support packages. Tocompress existing indexes, use the report MSSCOMPRES.

• UCS-2 Compression

On SQL Server, a unicode SAP system uses UCS-2, which requires two bytesper character. As of SQL Server 2008 R2, row and page compressed tablesstore UCS-2 characters very efficiently. However, the space used foralready compressed rows does not change when upgrading from SQL Server2008 to SQL Server 2008 R2. To benefit from the UCS-2 compression, youhave to re-compress existing tables using the options Force DataRebuild and Force Index Rebuild in report MSSCOMPRESS.

• Vardecimal Storage Format

As of SQL Server 2005 SP2, SAP used the vardecimal storage format for BWfact tables. As of SQL Server 2008, vardecimal should be considered as adeprecated feature. There is no advantage of vardecimal compared to rowcompression. Therefore<(>,<)> the report MSSCOMPRESSautomatically removes vardecimal compression when a table is ROW orPAGE compressed.

ACTIVITIES
When starting the report MSSCOMPRESS, a list of all databasetables including their size and compression type is displayed. Press the@HJ@ Refresh button to update the actual sizes and compression typesonce they have changed, for example as the result of a compression job.
To compress SQL Server tables and indexes, proceed as follows:
Use the radio buttons, to choose what you want to compress
@PP@ All Tables
Compresses all tables on the database, independent from the tablesdisplayed in the list
@IG@ Filtered Tables
Compresses all tables shown in the list of database tables. You canrestrict the list by setting various filter options. You can filter bytable name or the current compression type. You can also restrict thelist to the TOP N largest tables. After having changed the name filter,press ENTER to apply the filter. Alternatively, press the @01@ OKbutton.
@HD@ Selected Tables:
Compresses only the highlighted tables from the list. Use the mouse, theshift-key and control-key to highlight individual tables.
Choose the compression options
Use the radio buttons, to choose the desired data compression type. Youcan choose a different index compression type, once you set the checkboxChoose Index Compression. If the SAP basis support package is notup-to-date, you can only select the index compression type NONE.The report MSSCOMPRESS only applies the compression, if theactual compression type is different from the desired compression type.To change this, set the check boxes Force Data Rebuild andForce Index Rebuild.
Choose other options
You can configure the number of threads used to execute a single SQLstatement with the SQL Server configuration option max degree ofparallelism. For an SAP system, this is typically set to 1. Tooverwrite this configuration for the database compression, set thecheckbox MAXDOP in report MSSCOMPRESS. This has no effecton SQL statements from other reports.
When setting the checkbox Use Online, the database compressiondoes not acquire table locks, which results in less concurrency.However, this does not work for some tables, for example tables having atext or image field. Even if you have set the checkbox inMSSCOMPRESS, these tables are compressed without the onlineoption.
Start compression
Press the @HL@ Compress button. A dialog box opens, where you can choosehow to start the compression.
Start in Dialog
Only choose this option, if you want to compress few, or small tables inone pass. Depending on the configuration of your SAP system, you may runinto an SAP work process timeout.
Start IMMEDIATE as batch
This is the preferred option when compressing many tables in one pass.This option creates an SAP batch job and starts it subsequently.
Schedule BATCH job
This option provides the most flexibility, but you have to process anadditional dialog box to schedule the SAP batch job.
After confirming the dialog box, the compression starts. Whencompressing in dialog, the status bar displays a process indicator. Whencompressing in batch, the elapsed time is shown in the job status field.To refresh the job status field, press the small button @42@ on theright-hand side.
Check the log files
Each compression run is logged in the SAP application log. To view thelog files, press the @DH@ Log button. When running the compression as abatch job, there is also a job log. To view the job logs, press the @EP@Job Log button.