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.
Besides Column Store Index, another large investment and focus for SQL Server 2014 is a feature which is in development for 4-5 years already. The origin task was to make SQL Server factors more efficient in high-end OLTP scenarios. Whereas efficiency did not primarily mean making the query execution faster, but being able to run factors more workload on the same hardware as one is able to run with the ‘traditional’ SQL Server RDBMS engine. With the desired effect to reduce hardware and server investment of customers by factors.
As one worked on this challenge and analyzed a lot of customer workloads, three clear directions emerged to achieve such an ambitious efficiency improvements:
Over time one became more specific more specific in regards of targeted scenarios:
OLAP scenarios were not at all in focus when developing this new functionality. One also had the feeling that OLAP scenarios would be sufficiently covered with SQL Server’s Column Store Indexes and the in-memory optimized representation of the column store index data.
See also: http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/16/in-memory-oltp-q-amp-a-myths-and-realities.aspx
The philosophy guiding the design the in-memory components is consistent with the approach employed with SQL Server over the last 20 years:
A great article by Dave Campbell (CTP Cloud and Enterprise Division at Microsoft) related to this philosophy can be found here: http://blogs.technet.com/b/dataplatforminsider/archive/2012/11/08/breakthrough-performance-with-in-memory-technologies.aspx
The design approach for SQL Server has meant that Microsoft may not have been first to market but has meant that SQL Server functionality is easily deployed.
In principle, In-Memory OLTP as released in SQL Server 2014 addresses two dimensions:
The functionality of ‘In-memory OLTP’ as it is presenting itself in SQL Server 2014 could be described with the following key points:
An article that gives more background on some of the key points above can be found here:
If we look at a rough block diagram of SQL Server as it exists so far, one could show a very rough block diagram like this:
The graphics above demonstrates 3 tables which are storage backed. As one sees parts of those tables and indexes are represented in the Buffer Pool. We also abstracted SQL Server’s other components down to Query Parser, Catalog, Optimizer and the Query Execution. Everyone of those components does have quite a few subcomponents. To keep the graphics as simple as possible, we didn’t add the special memory area used for Column Store Indexes, but restricted our assumption that the 3 tables demonstrated all will be traditional row oriented tables without Column Store Indexes. Therefore having the Bufferpool in the graphics is sufficient.
Let’s move a few tables to In-Memory OLTP now and the graphics looks like:
Since the data of the in-memory resident tables are not block oriented, nor are those segment organized as the Column Store Indexes one needs a new Cache area to keep the ‘memory optimized’ tables. This area is marked light blue on the left hand side of the graphics. As usual SQL server will balance the sizes of the different caches automatically with the requirement that the data of the in-memory optimized tables need to fit into the new cache area. Another new component when leveraging In-Memory OLTP for a given database is a Filegroup which basically holds Filestream data resulting out of a process that is called continuous checkpoint (http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/11/in-memory-oltp-how-durability-is-achieved-for-memory-optimized-tables.aspx ).
We also would like to point out that the transaction log does not have the symbols for indexes of table T1 and T2 anymore. As mentioned earlier, changes to indexes of in-memory resident tables are not logged.
Querying tables from traditional applications which query traditional tables as well as in-memory resident tables would look like:
The red circle marks an interoperation layer between the traditional Query Execution functionality and in-memory resident tables. Means it really doesn’t matter for the queries of the existing applications whether the tables are storage backed tables as they always were or whether those are in-memory resident. Given the fact that the in-memory resident tables will be accessed with Snapshot Transaction Isolation Level might have some impact, especially for transactions that span traditional pages and in-memory resident pages.
Looking at optimizing and accelerating high-end OLTP scenarios, keeping the data in-memory, avoiding latching and blocking is a great thing and can accelerate quite a bit. But a good chunk could be gained as well by executing business logic on the RDBMS backend. However execution of that business logic will only be minimally more efficient in regards of CPU consumption by the fact that data in in-memory resident. Even worse if one, like some application vendors so feverishly propagate pushed more business logic execution to the RDBMS backend, one will again generate more load on those backends, and will push larger servers and higher investments into server infrastructure. Or drastically stated, one returns to a ‘mainframe like’ configuration again. But certainly not a configuration which is suitable for inexpensive Cloud IaaS services. Therefore one of the major components of in-Memory OLTP is the native compilation of Stored Procedures containing business logic as seen in this graphic:
This graphics show he components of the In-memory OLTP compiler which takes a query plan of a Stored Procedure and compiles it to binary code which is going to be stored in a DLL on the file system and registered to SQL Server like a CLR assembly. The compilation of such a Stored Procedures does not happen at run- or execution time, but happens at creation time. It got mentioned earlier that such natively compiled Stored Procedures are able to run against the in-memory resident tables only. Accessing an in-memory resident table through natively compiled Stored procedures as well as traditionally executed queries at the same time is possible. However we left the interoperation layer out of this graphics to keep it simple.
Besides the basic technologies as explained above, in-memory OLTP has many more interesting details which are explained in a series of publications which are best found under these sources (some of them references before already):
The best evidence on whether In-Memory OLTP keeps its promises in order to drastically increase workload throughput on given hardware is given by customers. From SQL Server side we were working with several customers already in early stages of In-Memory OLTP. One of them published a great overview of their experiences with this article: http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/30/sql-server-2014-in-memory-oltp-bwin-migration-and-production-experience.aspx
The usage of In-Memory OLTP as described by bwin is one of the typical usage cases for which In-Memory OLTP was designed and where it shows its full potential and value. It also confirmed and strengthened the basic design of In-Memory OLTP to not require the whole database content to be in-memory resident. All customers we were working with so far confirmed that they only required a subset of tables to be in-memory resident and not the complete database.
It got mentioned earlier already. One of the culprits of SAP Netweaver to use In-Memory OLTP is the fact that with the first release of the functionality mainly Snapshot Transaction Isolation Level is used (http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/01/in-memory-oltp-programmability-concurrency-and-transaction-isolation-for-memory-optimized-tables.aspx ). The upside of this transaction isolation level is that it avoids all kinds of blocking even between modifiers and readers. However that also requires conflict detection between two transactions which read a row value and now both try to change it in parallel. Something which usually is blocked with Committed Read or Read Committed Snapshot Isolation Level used by SAP NetWeaver working against the different RDBMS. The conflict resolution to avoid two modifiers as implemented in In-Memory OLTP is to allow only one open transaction modifying a specific row. If the same row should be changed in a second transaction without a commit by the first transaction, the second transaction would be completely rolled back.
Where would this hit in the SAP NetWeaver space?
Especially with certain business transactions running in parallel batches with overlapping data, scenarios where we encounter blocking locks between modifications made by different SAP workprocesses are common. Means a solution which would kill transactions of concurrent modifiers are not presenting a viable way to handle such situations.
Besides that In-Memory OLTP is missing support for Multiple Active Result Sets (MARS). Something which got introduced with SQL Server 2005 and which enabled SAP NetWeaver processes to reduce the number of connection per process to two connections.
Do we miss out on huge opportunities not supporting In-Memory OLTP with SAP NetWeaver?
Short answer, in this state of the SAP NetWeaver architecture is a clear NO. As the reader can imagine, we did quite some tests during development with and around different type of SAP workload and In-Memory OLTP. Paired with our good insights in customer scalability problems, our conclusion was that we are not missing out here:
Is Microsoft interested to have SAP NetWeaver working In-Memory OLTP?
Microsoft is interested in widening the scenarios where In-Memory OLTP could be used. As such further enhancements to Column Store Indexes as well as to In-Memory OLTP are going to be made. Changes and extensions to those feature areas also having SAP workload in mind.
That is it for Part 5. In part 6, we will talk more about SQL Server 2014 Backup Extensions
Nice Article! Here is also a very clear guide on what In-Memory(hekaton) is and how to use it, very recommended reading sqlturbo.com/the-use-cases-for-sql-server-2014-in-memory-oltp