Performance Verbesserung von SAP
BW mit SQL Server Columnstore
Martin Merdes
Senior Software Development Engineer
Microsoft Deutschland GmbH
SAP BW/SQL Server Porting
AGENDA
1. Columnstore Overview
2. SQL Server 2012 / 2014
3. SAP implementation
4. Customer experience
5. Summary
2
1. Columnstore Overview
2. SQL Server 2012 / 2014
3. SAP implementation
4. Customer experience
5. Summary
Columnstore overview
Optimized storage structures for OLAP
Stored by column
Highly compressed
Stored in segments
no fixed size pages (8KB)
instead segments of up to
1 million rows (1048576 = 0x100000)
In memory: separate memory pool
Column Store Object Pool vs. Buffer Pool
Optimized memory structures
SQL 2012: “max server memory (MB)” contains
But: no need to keep everything in memory Buffer Pool
Column Store Object Pool
On Disk: Saved as BLOB Mem-to-leave memory
4
Columnstore overview
Integration in SQL Server RDBMS
Implemented as index
Same table definition (no CREATE TABLE ON)
Having row- and column-store side by side
Familiar environment for DBAs
SQL Server management Studio
Backup, restore
Rich high availability features
AlwaysOn, DB Mirroring, Clustering
Use existing SAP BW / SQL Server Porting
Not a new platform for SAP
5
Columnstore overview
Creating a columnstore index
Original Table
(Row-Store)
6
Columnstore overview
Horizontally partition (Row Groups)
Row Group 1
(up to 1 million rows)
Row Group 2
(up to 1 million rows)
7
Columnstore overview
Vertically partition (Segments)
Row Group 1
(up to 1 million rows)
Row Group 2
(up to 1 million rows)
8
Columnstore overview
Reorder rows within row-group
Encode
Compress each segment separately
Columnstore:
2 row-groups
6 segments per row-group
9
Columnstore overview
SELECT ProductKey, SUM (SalesAmount)
FROM SalesTable
WHERE OrderDateKey < 20101108
1. Fetch only needed columns
2. Segment elimination
(filter in query)
3. Segment elimination
(same row group as
eliminated segment)
10
1. Columnstore Overview
2. SQL Server 2012 / 2014
3. SAP implementation
4. Customer experience
5. Summary
SQL Server 2012
Non-clustered columnstore index
Additional columnstore index
Data still stored in heap or clustered index
Secondary row-store indexes can be deleted
Table is read-only as long as columnstore index exists
Works optimal with SAP BW E-fact tables
Most common data types supported
Works fine with most SAP BW cubes
A few cubes with high precision key figures cannot use the columnstore
12
SQL Server 2014
Improvements for non-clustered columnstore index
Supports all (non-blob) data types
Better compression („columnstore_archive“ compression)
New clustered columnstore index
Writeable
No second copy of data needed
Additional space saving of ~70%
No additional non-clustered indexes allowed
No primary key possible
Not an issue for SAP BW fact tables (except SAP APO)
13
SQL Server 2014
Writable columnstore
Row-group consists of
Delta Store (row-store), for open row-groups
Compressed segments (column-store), for closed row groups
Deleted Bitmap
DML operations
INSERTs are written into Delta Store
DELETEs are removed from Delta Store, or row in segment is marked by Deleted Bitmap
UPDATEs are DELETEs followed by INSERTs
Row-group Compression
Tuple Mover runs every 5 minutes by default
It automatically compresses full row-groups (with 1 million rows),
i.e. data is moved from Delta Store to columnstore segments
Committed and uncommitted reads are not blocked during row-group compression
14
SQL Server 2014
Delta Store Segments Deleted
Bitmap
X Row Group 1
(compressed)
X
X Row Group 2
(compressed)
Row-group Row Group 3
(open)
Compression
15
1. Columnstore Overview
2. SQL Server 2012 / 2014
3. SAP implementation
4. Customer experience
5. Summary
SAP implementation 1st wave
Read-only columnstore on SAP BW E-fact table
Released by SAP in October 2012 for BW 7.0x and higher
Documented in http://scn.sap.com/docs/DOC-33129
See also http://blogs.msdn.com/b/saponsqlserver/archive/tags/bw/
Requires recent SAP NW SP + SAP note 1771177
Requires BW cube compression
Columnstore index is re-created during BW cube compression
Cube compression still faster (compared with b-trees)
Define columnstore property by cube, using report MSSCSTORE
Conversion to columnstore using MSSCSTORE or BW process chain
Configure SQL Server parallelism using RSADMIN parameter
MSS_MAXDOP_QUERY
MSS_MAXDOP_INDEXING
17
SAP implementation 2nd wave
Planned for mid of 2014
Already in test phase, but no SAP release/SP schedule yet
Writeable columnstore on SAP BW F-fact table
No BW cube compression required
Define columnstore property by cube, using report MSSCSTORE
Row-group compression triggered by SAP after each data load.
Included in Index Repair process chain
Not recommended for very small DTPs (a few thousand rows).
This would result in many partitions and small columnstore segments
Not for real-time cubes and SAP APO
Further improvements (E-fact & F-fact tables)
Support for all SAP BW data types
Additional space savings by archive compression (use report MSSCOMPRESS)
18
1. Columnstore Overview
2. SQL Server 2012 / 2014
3. SAP implementation
4. Customer experience
5. Summary
Customer Experience
Index
Compression Size [GB] Size [KB] Data [KB] Index [KB] Data Comp. Comp. Percent Factor
Space savings NONE SQL_2005 34,8 36.481.296 14.534.760 21.946.536 NONE 10 * NONE 201 0,5
ROW SQL_2008 20,5 21.523.448 5.696.472 15.826.976 ROW 10 * ROW 119 0,8
Reduced number of indexes PAGE 17,3 18.154.648 4.214.480 13.940.168 PAGE 10 * PAGE
COLUMN-
100 1,0
Non-Clu CS SQL_2012 6,3 6.609.696 4.164.768 2.444.928 PAGE STORE 36 2,7
Better compression Clu CS SQL_2014
Clu CS + Archive
2,4
2,0
2.466.624
2.068.592
2.466.624
2.068.592
0 COLUMNSTORE
0 COLUMNSTORE_ARCHIVE
14
11
7,4
8,8
64% space saving in example
(E-fact table with 100,000,000 rows) Size [GB]
40,0
~70% and more in real scenarios 35,0
Size of e-fact table [GB]
(due to reorg effect) 30,0
25,0
Faster data load 20,0
15,0
No index re-creation on f-fact
10,0
(during DTP)
5,0
Less index maintenance on e-fact 0,0
NONE ROW PAGE Non-Clu CS Clu CS Clu CS +
(during cube compression) SQL_2005 SQL_2008 SQL_2012 SQL_2014 Archive
Database Compression
Typically no aggregate rollup needed
20
Customer Experience Maxdop
Cache Cold
4
Warm
4
Average 4,5 6,6
Factor benefit of columnstore
(compared with b-tree)
Query 1 1,3 1,5
SQL Query performance Query 2
Query 3
6,9
0,9
4,3
2,4
Query 4 3,2 1,7
Average SQL query performance in SAP BW Query 5 2,3 5,5
Query 6 6,7 14,1
by factor 3 to 6 faster (columnstore vs. b-tree) Query 7
Query 8
1,2
1,6
2,3
1,8
Query 9 1,5 1,9
Larger cubes benefit more than smaller cubes Query 10 1,3 1,5
Query 11 1,9 2,2
Query 12 1,1 1,1
Benefit is higher, if data is already in SQL cache Query 13 1,4 1,6
Query 14 1,7 2,4
(warm, in-memory) Query 15 1,3 2,1
Query 16 2,8 5,2
Query 17 32,6 62,2
Some queries may benefit a lot (> factor 50), Query 18 3,6 4,1
Query 19 4,4 2,9
some do not benefit at all Query 20 1,7 3,0
Query 21 5,0 7,4
Query 22 4,3 5,2
MaxDop > 8 does not help in most cases Query 23 3,4 2,9
Query 24 3,0 3,1
Query 25 4,3 4,2
Query 26 12,2 35,1
Query 27 4,4 4,0
Picture: Query 28 6,8 22,8
suite of test queries running against a 100,000,000 row SAP BW cube Query 29 24,0 11,4
Query 30 2,3 2,0
Factor is defined as runtime on B-tree / runtime on Columnstore
Query 31 0,8 0,8
Cold: SQL cache is empty Query 32 1,7 2,5
Warm: All tables are fully in SQL cache Query 33 2,1 2,0
Query 34 1,4 1,4
Query 35 2,5 1,9
21
Customer Experience
BW Query performance
BW Query runtime consists of many parts
SQL query runtime (SAP BW data manager time)
Time spent on SAP application server (SAP BW OLAP time)
Network time to transfer (huge) result set
Result rendering time (on client)
Customers benefit only from columnstore,
if SQL query runtime is the bottleneck
Typical exceptions with high SAP BW OLAP time (spent on application server)
Cubes with high number of uncompressed requests (better compress all requests)
Multiprovider (use better one large cube instead)
SAP BW Exceptional Aggregation
22
Customer Experience
Impact of MaxDop
MSS_MAXDOP_QUERY (Default: 2)
Typically scales great for MSS_MAXDOP_QUERY = 8
However, you need sufficient free CPU threads for each parallel running BW query:
MSS_MAXDOP_QUERY threads per SQL query 8
2 SQL queries per cube (E-fact and F-fact table) 2
n cubes per BW multiprovider 3
Results in 8 * 2 * 3 = 48 threads 48
MSS_MAXDOP_INDEXING (Default: 8)
Scales great even for high values
Hardware requirements comparable with row-store
But do not expect good performance on a productive DB server with less than 16 CPU-threads
Memory requirements similar:
Additional RAM needed for Column Store Object Pool
However, less RAM needed for Buffer Pool (dropped b-tree indexes)
23
Customer Experience
BW Aggregates
Maintaining BW aggregates is a pain
New BW queries require often new aggregates
Columnstore works great for large cubes
No need to create aggregates just for reducing # characteristics
Columnstore also works fine with applying filters
Customer experience with columnstore
aggregates are not necessary in many cases
However, highly compressed aggregates (factor 100 and more) are still useful
Take care: The SAP OLAP processor is not aware of the columnstore
SAP BW prefers aggregates (without columnstore)
over basis cube (with columnstore)
24
1. Columnstore Overview
2. SQL Server 2012 / 2014
3. SAP implementation
4. Customer experience
5. Summary
Summary
Benefit of columnstore
Dramatic disk space savings (compared with PAGE compression)
~70% space saving with non-clustered CS (SQL Server 2012)
~90% space saving with clustered CS (SQL Server 2014)
Increased SQL Query performance by factor 3 to 6
Varying benefit dependent on query and cube design
Highest benefit for large BW cubes
BW queries can only benefit, if DB was the bottleneck
Runs on existing environment
Moderate CPU and memory resource requirements
DBAs can keep working with familiar environment
Easy to implement (create index by SAP)
Get rid of SAP BW aggregates in many cases
26
Summary
Related Content
Column-store indexes, described in SQL Server Books Online:
http://msdn.microsoft.com/en-us/library/gg492088.aspx
xVelocity technology, described in SQL Server Books Online:
http://msdn.microsoft.com/en-us/library/hh922900.aspx
SAP BW related issues in blog "SAP On SQL Server":
http://blogs.msdn.com/b/saponsqlserver/archive/tags/bw/
SAP Community Network "SAP On SQL Server":
http://scn.sap.com/community/sqlserver
27
