This Blog provides information about running SAP Applications on the Microsoft Platform. The Blog is written by people who are working with SAP on the Microsoft Platform for decades.
As you dear reader realized, there was not that much activity on our blog over the last few months. Reason for the lower output of blogs certainly has to do with work that the team is performing right now in different projects with SAP. One of the projects we were working on hard is SQL Server 2014. Something we want to go into details in a few articles on this blog site.
Overall SQL Server 2014 is a development which mainly focused in the following areas.
In September CTP2 of SQL Server 2014 got released. This was the last CTP before we will publish SQL Server 2014 to the public in the first half of 2014. As usual all the preparations with SAP are in place to start the evaluation process as soon as SQL Server 2014 is released to the public. Details about the supported SAP releases will be discussed in a later blog entry. But it is not planned to be massively different from what is supported with SQL Server 2012.
When looking at some of the features as Column Store Indexes or our In-Memory-OLTP one will see differences to some of our competitors in this space. Especially to some that also have Column Store and/or in-memory functionality. Background for these differences have to do with some overarching principles or facts taken into account when developing new functionality for SQL Server.
As such can be listed:
Given these four items as principles, solutions as they showed up in SQL Server 2012 and will show up in SQL Server 2014 might look differently than the solutions presented by some of our competitors. Some competitive products were created without any installed customer base or do have a different customer base where the main hardware used is not commodity based, but more based on proprietary hardware with a complete different price point and also no strings attached to any IaaS Service offers.
That just as background on the features we delivered and are going to deliver. Now let’s look at the first functionality which will change in SQL Server 2014
As reported in this Blog location in several articles, SQL Server 2012 introduced a Column Store Index which is fully supported by SAP BW. In the order, these are the articles published so far:
Summarizing and giving a bit more background on what was and is going to be delivered with our Column Store Index functionality we look at the following characteristics:
An excellent graphics of how the Column Store Indexes of SQL Server are structured and stored on storage can be found in graphics #1 in this document:
Since SQL Server 2012 Column Store Indexes were released with SAP, there were many customers applying it already. Many customers tested it with competitive products underneath SAP BW workload and decided that SQL Server Column Store Indexes fulfilled most of their criteria of:
As you see there are quite a bit of positive experiences that we could collect. Some of those experiences also went into further extensions to SQL Server 2014. The extensions to SQL Server 2014 in regards to Column Store Indexes can be listed like:
These are the most important changes which will enable a wider range of scenarios to be covered with SQL Server 2014 Column Store Indexes. What remained unchanged is that the Column Store Index data has no requirement to be in-memory resident only. But as before the Column Store Index data is stored in the SQL Server data files.
As we meanwhile all learned, column oriented data representation have some downsides compared to row oriented representation. Not too surprising given that over the last 25 years where one spent hundreds of developer years to really tune the row oriented data representation in RDBMS systems to its absolute best. There still remain a few areas where column oriented representation lag in performance behind the row oriented in certain usage scenarios. Some of these cases are:
How it works can be demonstrated nicely with the simple graphics below:
Additionally to just have the clustered index structure which includes the column oriented data, there also is a row oriented table aside which includes so called ‘deltas’. As additional construct of Column Store Indexes we introduced a ‘Delete Bitmap’. The different modification operations are handled like:
At a point in time the delta store is merged in the background with the column oriented structures. Means the rows move from a row oriented form into a column oriented form organized in segments and row groups. The merge usually is driven by a threshold of rows in the delta store. Specifically for SAP BW a SQL command got introduced that allows to start a merge after a Delta Loads to the F-Fact table of a BW cube.
Again more details of the implementation can be read in:
Assuming a fact table of 100 million rows, of a SAP BW cube, we made the following space measurements with the different versions of SQL Server. The BW cube implementation against the different versions of SQL Server changed over time from an indexing schema as well in order to leverage functionality like SQL Server Column Store Indexes. This also demonstrates to a great deal the progress in reduction of infrastructure investments like storage, especially for storage infrastructure. Or to keep such investments limited despite highly increasing data volumes.
As visible the Fact table occupied around 35GB with SQL Server 2005. The introduction of Page Dictionary compression and its subsequent usage by SAP NetWeaver applications provided a 50% reduction of volume already. Leveraging the read-only non-clustered Column Store Index of SQL Server reduced the space again dramatically to 6.3 GB. This reduction is also related to a drop of other non-clustered row oriented indexes against a typical Fact table. Using the same SAP BW implementation, but just using the clustered Column Store Index instead of the non-clustered one again reduces the volume. Reason is that the data is now held in the column oriented representation only. This reduces the volume down to 2.4GB.
Quite impressive steps going from SQL Server 2005 even to e.g. SQL Server 2008 R2 which provides Page Dictionary compression (including UCS2 compression). But even more impressive is what the move to Column Store Indexes can achieve in SAP BW. The effects are certainly boosted by the fact that one can rid of a lot of row oriented indexes on the SAP BW cubes.
As mentioned earlier compression rates can vary. Some of our customers observed better factors using SQL Server 2012 Column Store Index. But as with all the compression algorithms in the RDBMS systems, the factors of compression are majorly influenced by the nature of the data. This is true for the traditional PAGE dictionary compression of SQL Server (and other RDBMS) as well as the compression factors which can be achieved with storing data in a column oriented manner. In the case of SAP BW, the factor also is highly dependent on the number of SAP BW Aggregates which we usually drop when applying the Column Store Index to a fact table. Therefore numbers can vary.
This article only should give a small overview on SQL Server Column Store Indexes and its extensions in SQL Server 2014. Details about the implementation into SAP BW, releases covered, Support Packages required will, as always, be delivered by Martin in form of SAP Notes or blog articles at a time when we are coming close to release.
In the next article, we will look into the new SQL Server Cardinality Estimation logic as it will be released with SQL Server 2014 and it applies for the SAP NetWeaver architecture or not.
When there is a Delete operation, if the row is in DeltaStore and not merged with the ColumnStore then the DB engine directly deletes the row Physically. But if the merge has happened and the row is in ColumnStore (not in DeltaStore) then the row is just Logically Deleted by makring an entry in Delete Bitmap, and an Index Rebuild operation is required to remove the row Physically.