Reduce your SAP database size with Microsoft SQL Server Compression

Over the past few months I've been travelling from Aberdeen to the opposite corner of the country to assist with a major SAP migration exercise in the South West of England.  Leaving the snow behind to head for the sunshine has been a nice change in itself, but we've seen some interesting results on the project too.

The migration is from SQL Server 2005 to SQL Server 2012.  For new installations on SQL Server 2008 and above, compression of the database is the default and the results are quite impressive.

Save up to 80% disk space

The highest compression rates we've seen have been on the BW landscape, but broadly similar results have happened with ERP and PI as well.  In general systems now require 50-80% less space than they did before the compression.

The benefits of the reduction are significant, not only in the potential savings in expensive database disk.  Storage for additional pre-production or sandbox landscapes is now feasible, and refreshing QA more frequently is an easier proposition when you don't need to move terabytes of datafiles.

Our customer is pleased that the entire logistics of operating their SAP landscape will now be easier.  Databases can be moved more quickly, disaster recovery can be faster, and less scratch media is required for migrations or restores. 

Improving Performance

My first thoughts on hearing about any database compression was that there would surely be an overhead for compression resulting a performance decrease.  The interesting thing is that as CPUs become more powerful, database compression can actually improve performance by reducing the amount of disk I/O.

As an aside, SAP HANA also uses compression for this very reason.  Whilst the headline performance improvement in HANA is from storing data in-memory and removing the disk I/O bottleneck, that moves the bottleneck to memory I/O.  SAP realised they could use extra CPU power to compress the data flowing through the remaining bottleneck.

If you have a SAP instance installed on your database host, you can see the percentage of time the CPU spends idle in transaction ST06, under 'Detailed Analysis Menu'.  If you see idle CPU time of more than 40% over a typical 24 hour period then you can potentially use the spare CPU to get a performance gain from SQL compression.

Row and Page Compression

Row compression is the first step as it uses a variable length to store rows of data, meaning you don't waste space storing shorter rows without any extra CPU power required. 

Page compression adds further compression on top of row compression, and uses extra CPU power to compress the pages of data.  Page compression has been the default for new SAP installations on SQL Server 2008 upwards since May 2011.

How to Get There

The first step is to use SQL Server 2008 or higher.  If you are not already, you will need to perform a migration or a database upgrade. 

Java stacks don't support SQL compression.  As they generally have much smaller databases this isn't such a significant issue, and you can use compression on your ABAP systems with NetWeaver 7.00 or higher.

Compression may be the default behaviour when you install SAP, but if you have an existing system, you may need to perform some steps to activate the compression.

The report MSSCOMPRESS was first introduced with SAP Note 1488135, but is already available in your system if you have a reasonably recent support package stack.  This report can compress your database tables in-place.  SAP Note 1488135 is the best place to start to compress your database, and there are further resources listed below.

Migration to new hardware

As page compression is the default for new installs, you can migrate your SAP system with the latest tools and it will import with page compression already enabled with no additional work required. 

Consider the age of your existing hardware, operating system and whether you want to make any other changes to your configuration and sizing of application servers and database host.  Especially bear in mind you can also migrate to a newer SQL Server version if you need to, with no extra effort. 

Realise the Benefits

Naturally you can fit more data growth on the same storage, or use less storage.  But to me the real value of the compressed database comes from the flexibility of needing less storage.  It's time to revisit your disaster recovery strategy, your QA refresh strategy, and the roles of systems in your landscape to look at what you can now do without the constraint of needing so much disk.

Further Reading for SQL Compression

by Robert MacDonald, SAP Consultant