Improve the performance of SAP ABAP HANA with the help of MS SQL Server 2012

Being a SAP HANA ABAP developer involves critical skills and responsibilities, demands the professional to develop optimized applications. And if several advantages could be retained just by a migration, it is worth the effort. 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.

Saving 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.  The 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 data files.

Improvised Performance

About any database compression were that there would surely be an overhead for compression resulting in 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.  While 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 realized 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.

Adopting 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.