SQL Server 2014 delivers two different in-memory features, which are both fully integrated into the SQL Server Engine:
The columnstore is transparent to a large extent for existing applications, since it is implemented as an index. You do not need to decide for the rowstore or columnstore when creating a table. Indeed, you can even have rowstore and columnstore side-by-side. SQL Server Query Optimizer can use both stores, when there is a clustered (rowstore) index and a nonclustered columnstore index.
A database administrator can use the same, familiar environment for the columnstore as for the rowstore: The columnstore is fully integrated in the SQL Server Management Studio and uses the same backup and restore procedure. The rich high availability features of SQL Server are also available for the columnstore: Always On, Database Mirroring and Database Clustering.
The principal characteristics of the columnstore are:
A columnstore index in SQL Server 2014 can be either clustered or nonclustered:
There are two ways of loading data into a table having a CCI: Bulk Copy and INSERTs. SAP BW is only using INSERTs. Therefore we are having a closer look at this scenario. The loaded data records are inserted into the Delta Store of the open rowgroup. Once the rowgroup is full (having 1,048,576 data records) it will be closed and a new open rowgroup is created. All additional data records are inserted into the Delta Store of the new rowgroup. This results in exactly one open rowgroup and several closed rowgroups, independent of the number of data load processes and the number of rows inserted. The Tuple Mover thread runs every five minutes and compresses all closed rowgroups. Therefore all data records of the table are in columnar format, except the rows in the single, open rowgroup. The following picture shows the status of a table with a CCI, after four sequential data load processes and the online Tuple Mover had run.
The query execution plan is exactly the same, whether the data records are already compressed or still in the Delta Store. As a matter of course, the query performance is better once all rowgroups are compressed. A typical data warehouse table has hundreds of million rows. In this scenario one million uncompressed rows do not have a big impact on the query performance.
However things look different when table partitioning is used. In SAP BW, each data load is creating its own partition. Therefore there is very likely one open rowgroup per partition (per data load). As you can see in the picture below, a significant part of the loaded data records are uncompressed, even after the Tuple Mover had run:
In this scenario you have to manually compress the open rowgroup by using the following SQL command:
Unfortunately, this command is single threaded in SQL Server 2014. Therefore you may decide using a full index rebuild (which can use several database threads), if there are lots of uncompressed rowgroups in a table. In this case, the runtime of ALTER INDEX REBUILD is shorter than the runtime of ALTER INDEX REORGANIZE . However, the REBUILD needs much more system resources.
In SAP BW, ALTER INDEX REORGANIZE is always executed when repairing the indexes of an SAP BW cube. Therefore all rowgroups are manually compressed after each data load, if you include the SAP BW process chain step "Create Index" in all data load process chains (BW process chains containing a Data Transfer Process into a cube).
To get the size of the Columnstore Object Pool, execute the following SQL command:
After implementing the newest SAP Support Packages, you can see the size of the Columnstore Object Pool on the main screen of SAP transaction DBACOCKPIT :
The compression state of the individual rowgroups can be retrieved with the following SQL command:
In SAP you can see this information in DBACOCKPIT → Single Table Analysis → Columnstore Groups
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.