CLP Optimize Storage, Improve Performance & Streamline DBA Tasks with SQL 2008 R2 + PAGE Compression

First published on MSDN on Feb 21, 2011

CLP Optimize Storage, Improve Performance & Streamline DBA Tasks with SQL 2008 R2 + PAGE Compression

CLP , head-quartered in Hong Kong is a multinational Asia Pacific Utilities company with large assets in Hong Kong, China, South East Asia, Australia and India.

During the Christmas 2010 holidays, the CLP internal SAP Basis team successfully completed an upgrade of their SAP ECC 6.0 IS-Utilities customer care and billing instance to SQL 2008 R2 and PAGE compressed the database.

The compression project was completed fully online with no interruption of service to end users and the utility’s 2.4M customers in a little over just 3 days and without the help of any outside consultants.  SQL Server 2008 R2 PAGE compression improved overall system performance and there was no significant increase in CPU consumption on the Database server.

Quick Facts:

Company Revenues	$6.5 Billion USD
Number of Customer Accounts	2,387,000
Database Size	1.85TB Uncompressed	~500GB Compressed (reduction of 73%)
Number of SAP Users	800
Average Dialog Task Database Response Time	Before Compression ~150ms	After Compression ~100ms (reduction of 33%)
Nightlight Batch Job Window	3:15 hours	2:05 hours (reduction of 36%)
Backup Runtime	69 minutes	32 minutes (reduction of 54%)
Backup Size	307GB (with SQL 2008 R2 Backup compression only)	210GB (both Database and Backup compression, a reduction of 32%)
DB Server	HP DL585, 4 x AMD Quad Core & 128GB RAM – acquisition cost ~US$35,000	No change in this project, same DB servers used
Application Servers	4 x 8 core AMD & 32 GB RAM – acquisition cost ~US$18,000	No change in this project, same application servers used
SAN	EVA 8100 with 96 x 146GB disks	No change, same SAN used. To be replaced with EMC Clariion in early 2012.
HA solution	Microsoft Cluster Services	Microsoft Cluster Services
DR solution	HP Continuous Access replication over dark fiber to standby data center ~20km away from primary data center	No change. To be replaced with EMC storage replication technology in early 2012.
Unplanned downtime in last 3 years on IS-U ECC 6.0	0 Hours 0 Min 0 Seconds	Database compression and subsequent database file shrink completed fully online without disruption to the business over Christmas and Chinese New Year weekends
Archiving Solution	Open Text IXOS

Upgrade & Compression Project Planning

Microsoft and SAP fully supported compression technologies for SAP systems for some time.   More recently SAP have provided customers with a simple to use intuitive SAP GUI based tool for compressing SAP systems called MSSCOMPRESS.

The internal SAP Basis team at CLP completed the following high level process:

1.      Implement required OSS Notes in non-Productive systems (can be done even prior to upgrading to SQL 2008 R2)

2.      Upgrade database software to SQL 2008 R2 + latest CU or SP on non-productive systems

3.      Upgrade SQL Client software on non-productive application servers

4.      Run a test compression cycle on QA test system – note duration, transaction log and tempdb usage

5.      Implement required OSS Notes into production

6.      Upgrade production to SQL 2008 R2 + latest CU or SP

7.      Upgrade SQL Client software on application servers

8.      Identify a usage period such as a weekend and start compression process in online mode

9.   Monitor transaction and IO performance during and after compression

Previous blogs discuss the prerequisites and requirements to implement the SAP GUI tool MSSCOMPRESS and the OSS Note 1459005 for Secondary Index Compression support.

SQL 2008 R2 Upgrade for SAP Systems

SQL Server 2008 and above allows customers with MS Cluster to perform rolling upgrades thereby reducing the downtime of an upgrade.

Customers running Database Mirroring can reduce downtime even further as explained here .

CLP upgraded to SQL 2008 R2 on 17 ^th October 2010 approximately two months prior to the compression project.  Upgrading the database software prior to compression allowed CLP to clearly identify the performance benefits due to SQL 2008 R2 performance features and the benefits due to compression. Similar to past SQL Server upgrades, CLP noted performance improvements of about 10 – 15% simply by upgrading the SQL release with everything else unchanged.

Database Compression Processes – Key Requirements

During the compression process the CLP Basis team made the following configuration changes:

1.      Set the SQL Server Recovery Model to Bulk Logged and set the transaction log size to 90GB

2.      Suspended any DBA type tasks such as index rebuilds and statistics updates that were scheduled to run after periodic Archiving jobs

3.      Increased the frequency of transaction log backups to ensure the transaction log never became full

4.      Set the TEMPDB size to 5GB and closely monitored the TEMPDB consumption

5.      Excluded SAP Cluster Tables and tables with BLOB data such as RFBLG, CDCLS and BALDAT – these tables do not greatly benefit from compression as they are compressed already by the SAP application layer.

During a fully ONLINE compression procedure the TEMPDB is used to preserve read and write access to a table as it is being compressed.

The exact amount of TEMPDB usage is dependent on how much user initiated write activity occurs during the compression process and the amount of transaction log space is determined by the size of a table and the logging mode.  More information is available in this whitepaper about SQL 2008 Compression .

PAGE Compression Benefits

SQL Server PAGE compression reduced the total allocated (meaning used) space from 1.85TB to 513GB a saving of 73%.

In the days immediately following the compression process the database will grow a few percent due to the reorganization effect of the compression process and normal user activities slightly fragmenting the database again.

The SAP IS-UTILITIES table DFKKOP was originally 266GB in size comprised of 131GB of table data and 135GB in secondary indexes .

After compression this had reduced to 50GB with 21GB of table data and 28GB in secondary indexes – a compression ratio of 81%.

Compression ratios of 75 - 80% are common on non-Unicode systems. Unicode systems will typically compress 80 - 90% due to SQL 2008 R2 Unicode Compression technology .

The increase in size over the last few weeks shows the impact of regular archiving jobs on this table.

The runtime of CLP SAP IS-U CCS batch window has decreased from over 3 hours to about 2 hours.  Dialog and Batch performance decreased significantly for all IS-U and back office transactions universally, but SAP IS-U performance has improved particularly significantly due to the strong improvement in data cache hit ratio.  On a typical nightly batch run approximately 70,000 invoice documents are generated.

The runtime of a full online backup decreased from 69 minutes to 32 minutes .  The runtime of a full Check Database decreased from more than 24 hours to 16 hours .

Next Steps – Shrink Database Files & Free Disk Space (Optional)

SQL Server 2008 R2 table and index compression dramatically lowers storage requirements and improves performance.  SQL Server automatically releases unused data pages (referred to as “blocks” in Oracle) to the database.  SQL Server does not automatically reduce the size of the data files at the operating system level.  It is optional to do this step and many customers allow the database to grow and use the free space in the DB.

There are two ways to physically release the space after compressing an SAP system:

1.      Use R3LOAD to export / import the database – usually this is not recommended

2.      Use SQL Server SHRINKFILE command to shrink each SQL datafile – this is an Online operation (no downtime needed)

Note: SHRINKFILE can have serious performance consequences.  These are explained in Section 2 of this blog .  Always rebuild the table data with MSSCOMPRESS after shrinking a database.

CLP SAP Basis team ran the Shrink operation over Chinese New Year.  The total runtime of the Shrink operation was 1 Day 11 hours.   After the Shrink operation the cluster indexes were rebuilt an online process that took 3 days 21 hours.

Key Ingredients to run a SAP IS-U Billing system successfully

CLP Power have successfully operated their SAP systems on Windows/SQL Server running on low cost commodity infrastructure for more than 12 years.

CLP’s IT solution is built on standard 2 processor and 4 processor commodity AMD and Intel based servers with a significantly lower price than proprietary UNIX or mainframe systems.  The entire CLP IT infrastructure runs on Windows and commodity platforms and CLP has lowered infrastructure costs and improved integration by standardizing on Windows for the entire business ever since migrating off IBM Mainframes.

CLP state that their success in managing a 2.4 million customer SAP IS-U CCS solution is due to the following factors:

1.      Archiving strategy – Due to the massive amount of new data being generated every day, large-scale billing systems must have a data management and archiving solution to preserve performance.  CLP uses Open Text IXOS and archive meter reading, consumption and billing data after it has reached an online retention period of 14 months.

2.      Keeping up with the latest SAP, SQL Server and Windows releases to benefit from new technology and features.  CLP used to invest in 3 ^rd party backup compression software, but this capability has become standard in new releases of SQL Server reduce cost, complexity and support requirements.

3.      Utilize commodity servers and solutions, not only are they cheaper than UNIX/Oracle solutions but the performance on Intel/AMD platforms is as good or better than UNIX platforms.  Intel Servers, Windows OS and SQL DB have matched expensive proprietary solutions in terms of features and performance. The benefit of a Microsoft platform is a broad choice of possible hardware platform providers and technical skills are more commonly available in the market.

4.      Secure Windows servers using Windows Active Directory Polices and remove Internet Explorer to avoid the need to apply Windows Security patches that apply to or depend on Internet Explorer

5.      Consolidate on leading vendors to improve product integration and simpler support.  CLP standardize on HP commodity servers, software from SAP or Microsoft and certified 3 ^rd party solutions only as and when required

6.      Reducing the number of suppliers improves strategic supplier alignment, support and accountability

Future Optimizations & Enhancements

CLP Power will refresh the SAP IS-U/CCS hardware platform in early 2012 and anticipates purchasing affordable commodity servers with 1TB RAM or more for the database cluster nodes.  The entire customer care and billing database serving 2.4M customers will effectively run “in memory” as the database size and SQL Server data cache will be approximately the same size.  This is expected to lead to a very significant further reduction in batch processing times and improvement in online performance. Furthermore CLP will adopt Solid State Disk technology to dramatically improve any remaining I/O to the disk subsystem. Leverage of SSD as default storage medium becomes feasible and cost effective due to the significant database compression ratios enabled by SQL Server 2008 R2, including highly effective compression of SAP Unicode data.