Overview of the internal mechanisms of SQL Server in-memory OLTP (i)

----------------------------I'm a split line-------------------------------

This article is translated from the Microsoft white Paper SQL Server in-memory OLTP Internals Overview: http://technet.microsoft.com/en-us/library/dn720242.aspx

----------------------------I'm a split line-------------------------------

Overview of the internal mechanisms of SQL Server in-memory OLTP

Summary:

In-memory OLTP (project named "Hekaton") is a new database engine component that is fully integrated into SQL Server. It is optimized for OLTP workloads that access memory resident data. In-memory OLTP helps OLTP workloads achieve significant performance improvements and reduce processing time. You can enable the functionality of in-memory OLTP by declaring the table as "in-memory optimization." Memory-Optimized tables fully support transactions and can be accessed using Transact-SQL. Transact-SQL stored procedures can be compiled into machine code to further improve the performance of memory-optimized tables. The engine is designed for high concurrency and minimizes blocking.

Brief introduction

When you initially design SQL Server, assume that the main memory is very expensive, so unless the data really needs to be processed, the data resides on disk. This assumption is no longer valid because memory prices have fallen sharply in the past 30 years. At the same time, multicore servers are no longer expensive, so today it takes less than $50,000 to buy a server with 32 cores and 1TB of memory. Because many (though not most) OLTP databases in the production environment can be fully loaded into 1TB of memory, we need to reassess the benefits of storing data on disk and the I/O overhead of reading data into memory for processing. In addition, the OLTP database also generates overhead when it updates data and needs to write data back to disk. Memory-optimized tables are stored in a completely different way from disk-based tables, and these new data structures help to access and process data more efficiently.

Due to this trend of more available memory and more cores, the Microsoft SQL Server team began to build a database engine optimized for a large number of primary and multi-core CPUs. This article presents a new database engine feature: A technical overview of in-memory OLTP.

For more information about in-memory OLTP, see in-Memory OLTP (in-memory optimization).

Design Considerations and Objectives

The move to develop a real memory database is driven by three basic requirements: 1) put most or all of the data required by the workload into memory, 2) for lower latency for data operations, 3) the professional database engine for a specific type of workload needs to be optimized for those workloads. Moore's law has affected the cost of memory, allowing the main memory to be large enough to meet demand (1) and partly to meet demand (2). (larger memory reduces read latency, but does not affect the latency required by traditional database systems to write to disk). Other features of in-memory OLTP greatly increase the latency of data modification operations. A system designed for a specific type of workload can perform 10 times or more times more than a general-purpose system, and it is this recognition that drives the needs of a professional database engine. The most specialized systems, including those used for complex event processing (CEP, Complex event processing), Dw/bi and OLTP, are optimized for data structures and algorithms by focusing on in-memory structures.

Microsoft has developed the functionality of in-memory OLTP mainly from the fact that the main memory size is growing at a very rapid rate and becoming cheaper. In addition, due to the prevalence of 64-bit architectures and multicore processors, it is reasonable to assume that most (though not all) OLTP databases or performance-sensitive entire working data sets can reside entirely in memory. The largest financial, online retail and airline booking system has reduced the size of many systems to between 500GB and 5TB, and the working set is significantly smaller. As of 2012, even a dual-socket server can accommodate 2TB of DRAM (Dynamic Random Access memory) by using the 32GB DIMMs (Dual in-line memory Module) (for example, IBM x3680 X5). Looking ahead, it is entirely possible to build a DRAM-based distributed system with 1-10 PB capacity in less than $5/gb cost over the next few years. It is only a matter of time before non-volatile RAM becomes viable.

If most or all of the data in an application can reside completely in memory, then the cost calculation rules that the SQL Server optimizer uses from the original version become almost completely obsolete, because the rules assume that all access to the data page may require physical reads from disk. If you do not need to read from the disk, the optimizer can use a different cost calculation algorithm. In addition, if there is no waiting time required to read the disk, other waiting statistics, such as waiting for the lock to be released, waiting for the latch to be available or waiting for the log write to complete, will become extremely large. In-memory OLTP solves all of these problems. In-memory OLTP eliminates the problem of waiting for lock release, and adopts a new multi-version optimistic concurrency control. In-memory OLTP produces much less log data than originally, and requires fewer log writes, which reduces the latency of waiting for Log writes.

Terms

The in-memory OLTP feature of SQL Server 2014 involves a range of technologies related to using memory-optimized tables. Tables relative to memory-optimized tables will be referred to as disk-based tables, which SQL Server has always provided. The terms used include:

• Memory-Optimized table: refers to a table with a new data structure that is described in detail in this article as part of in-memory OLTP.
• Disk-based tables: In contrast to memory-optimized tables, the data structures that SQL Server has been using previously have been used to read from disk and write the required 8K-size data pages as a unit.
• Natively compiled stored procedures: Refers to an object type supported by in-memory OLTP functionality, is compiled into machine code, and natively compiled stored procedures have the potential to further enhance performance compared to using only memory-optimized tables. Corresponds to an interpreted, Transact-SQL stored procedure that has been used previously by the server. Natively compiled stored procedures can only refer to memory-optimized tables.
• Cross-container transactions: Transactions that refer to both memory-optimized tables and disk-based tables.
• Interop: Refers to an interpreted type of Transact-SQL that references memory-optimized tables

Features Overview

When you use in-memory OLTP to do most of the processing of your data, you may not be aware that you are using a memory-optimized table instead of a disk-based table. However, if the data is stored in a memory-optimized table, SQL Server handles the data in a very different way. In this section, we'll look at an overview of how in-memory OLTP works and how data is processed differently than disk-based operations in SQL Server. We will also briefly introduce solutions from some of our competitors ' memory-optimized databases and point out how SQL Server's in-memory OLTP differs from them.

What's special about in-memory OLTP

Although in-memory OLTP is integrated with the SQL Server relational engine and can be accessed transparently using the same interface, its internal behavior and functionality vary greatly. Figure 1 shows an overview of the SQL Server engine that contains the in-memory OLTP components.

Figure 1 SQL Server engine with in-memory OLTP components

Note that for memory-optimized tables or disk-based tables, whether a locally compiled stored procedure or an interpreted Transact-SQL is called, the client application connects to the TDS handler in the same way. As you can see, interpreted Transact-SQL can use interop to access memory-optimized tables, but locally compiled stored procedures can only access memory-optimized tables.

Memory-Optimized tables

The most important difference between memory-optimized tables and disk-based tables is that when accessing memory-optimized tables, data pages do not need to be read from disk into the cache. All data is always stored in memory. Checkpoint file groups (data and differential file pairs) that are used only for recovery purposes are created in files that reside in a memory-optimized filegroup, and the tracking of data changes is recorded, and the checkpoint file can only be attached.

Operations on memory-optimized tables use the same transaction log as the operations on disk-based tables, and as always, the transaction logs are stored on disk. In the event of a system crash or a server shutdown, the data rows in the memory-optimized table can be rebuilt through checkpoint files and transaction logs.

By using an option named Schema_only, in-memory OLTP can choose to create a non-persistent and log-less table. The table schema will be persistent even if the data is non-persistent, as shown in this option name. These tables do not require any IO operations during transaction processing, but the data is available in memory only when SQL Server is running. As soon as SQL Server shutdown or AlwaysOn availability groups fail over, the data in these tables is lost. When the database to which they belong is restored, the table is rebuilt and there is no data in the table. These tables can be useful, for example, temporary tables in an ETL scenario or temporary tables for storing the session state of a Web server. Although the data is non-persistent, the operations on these tables conform to all the other requirements of the transaction: atomicity, isolation, and consistency. We will see the syntax for creating a non-persistent table in the section creating the table.

Indexes on memory-optimized tables

Indexes on memory-optimized tables are not stored in accordance with the traditional B-tree structure. Memory-optimized tables support nonclustered hash indexes, and nonclustered Hachiso are stored as hash tables, with a list of links in the hash table that all rows of data with the same hash value are connected to a memory-optimized nonclustered index, while memory-optimized nonclustered indexes are stored using a special bw tree. Nonclustered hash indexes are optimized for point lookups, while memory-optimized nonclustered indexes provide support for the range and row ordering of retrieved values, and optimize the performance of queries that use unequal predicates.

Each memory-optimized table must have at least one index, because it is the index that combines all the data rows into a single table. Memory-optimized tables are never stored as a collection of data rows that are not organized like disk-based heap tables.

The index is never stored on disk and is not reflected in the checkpoint file on disk, and operations in the index are never logged. As with the B-tree index on disk-based tables, indexes are maintained automatically when all modifications on the memory-optimized table occur, but once SQL Server restarts, the indexes on the memory-optimized table are rebuilt as the data is loaded into memory.

Improvements in concurrency

When accessing memory-optimized tables, SQL Server implements an optimistic version of concurrency control. Although SQL Server formerly supported optimistic concurrency control through the snapshot-based isolation level introduced in SQL Server 2005, these so-called optimistic methods do need to acquire locks during data modification operations. For memory-optimized tables, there is no need to acquire locks, so there is no waiting to be caused by blocking.

Note that this does not mean that waiting is not possible when using memory-optimized tables. There will still be other wait types, such as waiting for the log write to complete at the end of the transaction. However, when making changes to memory-optimized tables, the log records for memory-optimized tables are much more efficient than disk-based tables, so the wait time is shorter. And the data read from disk never waits, and there is no wait for the lock on the data row.

Locally compiled stored procedures

You get the best execution performance when you use natively compiled stored procedures that have memory-optimized tables. However, there are a number of limitations to the Transact-SQL language structure that is allowed within a local compilation stored procedure, relative to the rich set of features that are available for interpreted code. In addition, natively compiled stored procedures can only access memory-optimized tables and cannot reference disk-based tables.

is in-memory OLTP just an improvement in DBCC PINTABLE?

DBCC pintable is a feature provided by older versions of SQL Server, and once the data page is read from disk, any data page in the "pinned" table is not removed from memory. These data pages need to be initialized to read, so such tables will always have the cost of reading the data page for the first time. These fixed tables are not any different from any other disk-based tables. They require the same number of locks, latches, and log records, and also use the same index structure, which also requires locks and logging. Memory-optimized tables for in-memory OLTP are completely different from SQL Server disk-based tables that use different data and index structures, do not use locks, and log records that change the memory-optimized tables more efficiently than disk-based tables.

Competitor's Products

There are two types of professional engines for processing OLTP data. The first class is the main memory database. Oracle has TIMESTEN,IBM with SOLIDDB, and many other products are focused on embedded database space. The second category is the application cache or key-value storage (for example, Velocity–app Fabric cache and Gigaspaces), which leverages the memory of the application and the middle tier to reduce the workload of the database system. These caches are becoming more complex and have database features such as transactions, scope indexes, and queries (such as Gigaspaces already has these features). At the same time, database systems have caching capabilities, such as high-performance hash indexes and extensions across multi-server clusters (such as VOLTDB). The in-memory OLTP engine is intended to provide the advantages of each of these two types of engines. In-memory OLTP can be considered to have cached performance and database functionality. It supports storing tables and indexes in memory so that you can build the entire database into a complete in-memory system. It also provides high-performance indexing and logging, as well as other features to significantly improve query execution performance.

In-memory OLTP for SQL Server provides the following features that few competitor products can offer:

• Integration between memory-optimized tables and disk-based tables, migrating to a memory-resident database can be done incrementally, creating only the most critical tables and stored procedures into memory-optimized objects.
• Natively compiled stored procedures provide an order of magnitude improvement for the execution time of basic data processing operations
• Memory-optimized nonclustered hash indexes and memory-optimized nonclustered indexes are specifically optimized for primary memory access
• You do not store data on a data page, and you do not need a data page latch.
• True multi-version optimistic concurrency control with no lock or latch on any operation

The most significant difference between SQL Server in-memory OLTP and competitor product design is the integration of interoperability. In a typical high-end OLTP workload, performance bottlenecks are concentrated in specific areas, such as a small number of tables and stored procedures. Forcing the entire database to reside in memory will be expensive and inefficient. But so far, other major competitive products have adopted this approach. For SQL Server, high-performance and highly competitive areas can be migrated to in-memory OLTP, so operations on these memory-optimized tables (stored procedures) can be compiled locally to achieve maximum business processing performance.

Another key to in-memory OLTP improvements is the removal of the data page structure of the memory-optimized table. This fundamentally transforms the data manipulation algorithm from disk-based optimization to memory-based and cache-optimized. As mentioned earlier, one of the puzzles about in-memory OLTP is that it simply locks the table in the buffer pool just like the DBCC pintable. However, even when data pages are forced to reside in memory, many competing products still use the data page structure. For example, Hana for SAP still uses 16KB-size data pages to handle the storage of data rows in memory, which in essence still needs to endure the impact of data page latch contention in high-performance environments.

---------------------------to Be Continued-------------------------------

Overview of the internal mechanisms of SQL Server in-memory OLTP (i)