Using SQL Server 2012 Column-Store with SAP BW
SAP COMMUNITY NETWORK scn.sap.com
© 2012 SAP AG 3
Column-Store in SQL Server 2012
The SQL Server 2012 introduces a new data warehouse query acceleration feature based on a new type of
index called an xVelocity memory optimized column-store. This new index, combined with enhanced query
processing features, improves data warehouse query performance by factors. For an introduction of column-
store indexes, see http://msdn.microsoft.com/en-us/library/gg492088.aspx.
Besides improved query performance, you can further benefit from using the column-store:
Space Saving
Indexes can be compressed much better when stored by columns rather than by rows. The compression
ratio depends on the actual data. Typically column-store indexes are by factors smaller than b-tree indexes.
For columns with low cardinality, the compression can be hundreds times better and more.
There is no column order in column-store indexes. For b-tree indexes, it makes a difference whether you
have an index on columns (A,B,C) or on columns (C,B,A), when using a filter on column A within a query.
This is not the case for the column-store. Therefore, you can drop all secondary b-tree indexes once you
have created a column-store index on all columns. This results in further space savings.
Query Performance
SQL Server 2012 leverages several optimizations to speed-up query performance of column-store indexes
compared with b-trees:
Index compression
As described above, column-store indexes are much smaller than b-trees. The reduced index size
results in faster index scanning.
Parallelism
The query algorithms for the column-store are optimized for parallel execution (batch mode). You
can observe best query performance, if there are always at least two CPU threads available to run
the query. Otherwise SQL Server is running the query without parallelism (row mode).
Optimized memory structures
The column-store is not using fixed sized SQL Server data pages from the buffer pool. Instead the
column-store has its own memory pool. However, you do not need to configure this pool. Everything
is done automatically by SQL Server memory management. You only have to make sure that
sufficient memory is available for SQL Server.
Fast Index creation
Index creation can be parallelized much butter for column-store indexes than for b-trees. Indeed, index
creation scales well with millions of rows and dozens of CPU threads. However, you will not benefit from
parallelism during index creation for tables having less than a few million rows.
SQL Server restrictions
SQL Server Column-Store is only available in the Enterprise Edition of SQL Server 2012. For SAP BW this is
not a limitation, because the Enterprise Edition is required anyway.
The non-clustered column-store in SQL Server 2012 is read-only. Therefore you cannot modify the data of a
table as long as a column-store index exists for this table. Before loading data into the table, you first have to
drop the column-store index. After the data load you should re-create the column-store index to optimize the
query performance again. In SAP BW, these steps are performed automatically during BW cube
compression.
The column-store supports the data type DECIMAL up to a precision of 18 digits, which is fully sufficient for a
standard SAP BW. However, there are a few SAP solutions based on SAP BW that require a precision of
more than 18 digits.
Manymanuals.com
Manymanuals.de
Manymanuals.fr
Manymanuals.it
Manymanuals.pl
Manymanuals.cz
Manymanuals.es
Manymanuals-pt.com
Komentarze do niniejszej Instrukcji