SQL Server 2012 Column Store Deployment Accelerates BW Queries and Improves Storage Efficiency
Summary of Benefits
SAP fully support and integrate the SQL Server 2012 Column Store feature into SAP BW applications. SQL Server 2012 first introduced a read only Column Store Index and now SQL Server 2014 enhances the Column Store feature.
This blog describes how Homepro (https://homepro.co.th), a large Retail chain in Thailand implemented SQL Server 2012 Column Store on their BW 7.30 system and the benefits the customer realized both in terms of Load and Query Performance and storage savings.
1. Space Savings and Performance Gains on SQL Server 2012
Homepro has realized benefits in Load and Query Performance in addition to cutting the PAGE compressed database size from 2.124TB to 0.892TB, a reduction of 57% and improved Query Performance by up to 5 times.
Homepro has supplied the following slide showing the benefits of SQL Server Column Store on their SAP BW system
Columnar storage format is particularly efficient if the data in a table is highly repetitive, which is frequently the case with SAP BW systems. SQL Server Column Store compression algorithms are typically even more efficient than PAGE compression. SQL Server PAGE compression will compress SAP databases by 75% to 85%. SQL Server Column Store compression ratios of over 95% have been observed. This is due to the fact data is stored by column. Column Store data in SQL Server is stored in larger units, called segments rather than the 8k pages used to store conventional row store data. Combined with the fact that one stores the same type of data in one segment, the chances of encountering similar values within one segment are way larger. The compression efficiency within such a Column Store segment will improve compared to the traditional page dictionary compression.
Query Performance is significantly improved due to several factors:
1. Column Store format is dramatically more efficient for some queries that reference only a small proportion of the columns
Example:
Table “A” has 100 columns, named Column1, Column2, Column3 etc
Query is only referencing a small number of the 100 possible columns.
All columns are the same length in this example
SELECT Column1, Column2, Column3 FROM TABLE_A WHERE Column4 = x AND Column5 = y
The query only accesses 5 of the 100 columns in Table “A”, therefore only 5% of the data is retrieved.
Row storage formats require the DBMS engine to retrieve the entire row, all 100 columns even though the query only accesses 5 columns
2. Column Store Compression is very efficient, even more so than PAGE compression and greatly reduces the size of the objects on disk. Therefore the query not only retrieves much less data because only the columns that are needed by the query are returned, but the compression is reducing the data even further
3. SQL Server Column Store technologies can also cache objects in RAM rather than disk. Accessing Columnar data out of RAM is hundreds to thousands of times faster than mechanical disk
4. SQL Server Column Store eliminates the need for secondary indexes. This reduces the storage requirement and reduces the overhead involved in maintaining multiple secondary indexes.
2. Performance Improvement Achieved by HomePro
The performance gains at Homepro are summarized in the slide above. SQL Server Column Store feature will improve performance greatly on BW loads and queries with a high percentage of their runtime spent in DB operations. Column Store will only be able to reduce the time spent in the DBMS related processing. Different reports can have very different ratios between the times spent processing on SAP BW application side and time spent in the DBMS backend. This also was the case in HomePro where different ratios of time spent between SAP BW application layer and DBMS backend were influencing the percentage gains by column store.
3. How to Implement SQL Server Column Store for SAP BW?
SAP supports SQL Server 2012 Column Store on all SAP BW releases from 7.00 to 7.40 and above. The enhancements to integrate with SQL Server Column Store are delivered in SAP Support Pack Stacks. SQL Server 2014 is being tested by SAP currently (May 2014) and the same Column Store functionality is available for customers today.
SAP Note 1771177 - SQL Server 2012 column-store support for SAP BW and SQL Server Column-Store: Updated SAP BW code detail the process for implementing SQL Server Column Store. Attached to SAP Note 1771177 is SQL_Server_2012_Column-Store.pdf. This document details how to convert an E Fact table to Column Store.
SQL Server 2012 supports a Read Only Column Store. SAP BW E Fact tables can be converted to Column Store.
SQL Server 2014 may support converting additional BW objects to Column Store.
Homepro implemented the following procedure on their SAP BW systems:
1. Update SAP Support Packs to latest available on SAP BW Development
2. SQL Server 2012 SP1 and latest Cumulative Update on BW Development
3. Update SAP Support Packs to latest available on SAP BW QAS
4. SQL Server 2012 SP1 and latest Cumulative Update on BW QAS
5. Perform functional testing and validation
6. Update SAP Support Packs to latest available on SAP BW Production
7. SQL Server 2012 SP1 and latest Cumulative Update on BW Production
8. Measure Query Performance & Process Chain runtimes on BW QAS
9. Convert BW QAS E Fact cubes to Column Store & change default storage to Column Store for all new cubes with report MSSCSTORE
10. Measure Query Performance & Process Chain runtimes on BW QAS
11. Compare results before vs. after Column Store, compare DB size
12. Nominate one medium size E Fact table to convert to Column Store on BW Production
12. Stop Process Chain for this Fact table and convert to Column Store - record conversion performance in GB/hour
13. Verify that Process Chain runs as fast as or faster than before Column Store
14. Ask users to verify performance on medium size cube
15. Repeat procedure and then change system default to Column Store
Homepro were able to convert E Fact cubes with 500,000,000 rows to Column Store in less than 30 minutes. The exact throughput depends on the exact design of the cube. Process Chains are considerably faster as the creation of the Column Store index on the E Fact table is at least 2-3 times faster than conventional secondary indexes
4. Additional SAP BW Tuning for SQL Server
In addition to SQL Server Column Store integration there are many performance optimizations for SAP BW that have been released. Most of these are delivered in recent SAP Support Pack Stacks. Important performance optimizations, SAP Notes and tuning options are documented here:
1. SAP on SQL Server systems so far always have the global Max Degree of Parallelism set to 1. This is true on ECC 6.0, SCM, CRM and BW systems. SAP BW queries and index creation may benefit from Degrees of Parallelism higher than 1, however the global value must always be set to 1. SAP BW supports two RSADMIN parameters set with SAP_RSADMIN_MAINTAIN - MSS_MAXDOP_QUERY and MSS_MAXDOP_INDEXING. This functionality is explained in SAP Note 1654613 - SQL Server Parallelism for SAP BW and this blog Performance of index creation
2. SAP BW Cube Compression performance has been enhanced in SAP Note 1860832 - Performance improvement for BW cube compression. There is more information in this blog Increasing BW cube compression performance
3. Further information on BW performance enhancements is contained in this blog Optimizing BW Query Performance
4. If required SAP BW aggregates can be created on Column Store cubes. This may be beneficial on very large BW cubes SQL Server Column-Store with SAP BW Aggregates
5. Homepro Test SQL Server 2014
Homepro has begun testing SQL Server 2014 with their SAP environment. There are many new features and enhancements in SQL Server 2014. Features Homepro are evaluating include Backup to URL(Cloud), AlwaysOn and enhancements on the Column Store feature.
6. Homepro Business Background
Homepro in Thailand operate a full IS-RETAIL suite of products to support 66 retail stores in Thailand, a new commercial wholesale business and business expansion into South East Asia. Homepro’s SAP solution includes SAP ECC 6.0, PMR (Promotion Management), F&R (Forecasting & Replenishment), POSDM, CRM, BW, EP and PI. There are also additional solutions for Workflow monitoring and alerting.
Homepro streamlined business processes such as setting up new products and SKU by implementing workflow. Homepro has an ambitious growth target for the next two years and plan to run 100 stores within Thailand and more stores internationally.
SAP Business Warehouse is a key integrated component in the IS-RETAIL solution. To support increased BW transaction volume and more complex reporting requirements Homepro implemented SQL Server Column Store integration for SAP BW
7. Feedback from Customer on SQL Server Column Store Feature
Homepro SAP technical lead Mr. Wiroon Unggurarat has provided the following feedback about their deployment experience:
1. Column Store is simple and easy to deploy. Recent SAP support packs contain all the related functionality and there is a simple administration program called MSSCSTORE
2. Column Store greatly reduces the size of SAP BW database and is highly efficient at storing BW data
3. Query performance at Homepro was greatly improved by Column Store allowing Homepro to continue using existing servers for another 1 year
4. Homepro are upgrading servers to powerful new IvyBridge EX servers with FusionIO cards and 2TB of RAM and will deploy Windows 2012 R2 and SQL Server 2014
5. BW Admin team report that the runtime of Process Chains is reduced with Column Store. Homepro expects to see further improvement in Process Chain performance with SQL Server 2014
6. Homepro has changed the default setting for SAP BW to Column Store for all new cubes. Implementation has been smooth with no problems encountered so far
Links
The following link details the SQL Server Column Store implementation: SQL Server Column Store Indexes
BW Blogs:
SQL Server Column-Store: Updated SAP BW code
Optimizing BW Query Performance
Increasing BW cube compression performance
SQL Server Column-Store with SAP BW Aggregates
Download free evaluation edition of Windows 2012 R2 https://technet.microsoft.com/en-us/evalcenter/dn205286.aspx
Download free evaluation edition of SQL Server 2014 https://www.microsoft.com/en-us/server-cloud/Products/sql-server/Try.aspx
Download free evaluation edition of SQL Server 2012 https://www.microsoft.com/en-us/download/details.aspx?id=29066
Note 1966681 - Release planning for Microsoft SQL Server 2014