Discover what new features are in SQL Server 2014 (1)-Memory database _mssql

Brief introduction

SQL Server 2014 offers a number of exciting new features, but one of the most anticipated features I think is the memory database. When I attended the opening of SQL Pass Summit 2012 in Seattle last year, Microsoft announced that a memory database engine, code-named Hekaton, would be included in the next SQL Server version. Now with the advent of 2014CTP1, we can finally glimpse its face.

Memory Database

In traditional database tables, the structure of tables and indexes is b-tree due to the physical structure of the disk, which makes the index very weak in a large concurrent OLTP environment, although there are many ways to solve such problems, such as optimistic concurrency control, application caching, distributed, and so on. But costs will remain slightly higher. With these years of hardware development, the server now has hundreds of G memory is not uncommon, in addition, due to the maturity of NUMA architecture, also eliminates the CPU access to memory bottleneck problem, so the memory database to appear.

The scientific name of memory is called Random Access Memory (RAM), so as its characteristics, is random access, so for memory, the corresponding data structure will also be hash-index, and the concurrent isolation method is also corresponding to the MVCC, So the memory database can handle more concurrency and requests under the same hardware resources, and will not be blocked by locks, and SQL Server 2014 is a powerful feature, unlike Oracle's TimesTen, which requires extra pay, so combining SSD as Buffer Pool characteristics, the resulting effect will be well worth looking forward to.

Representation of SQL Server memory database

The Hekaton engine in SQL Server consists of two parts: a Memory tuning table and a local compilation stored procedure. Although Hekaton is integrated into the relational database engine, the way to access them is transparent to the client, which means that the presence of the Hekaton engine is not known from the client application's point of view. As shown in Figure 1.

Figure 1. The client app does not perceive the presence of the Hekaton engine

First of all, the memory tuning table will no longer have the concept of a lock (although the previous version has the concept of snapshot isolation of this optimistic concurrency control, but snapshot isolation still needs to modify the data lock), in addition to the Memory optimization table Hash-index structure makes random read and write speed greatly increased, In addition, the memory tuning table can be set to a Non-persistent memory optimization table, thus there is no log (suitable for the ETL intermediate result operation, but there is the risk of data loss)

Now let's look at creating a memory tuning table:

First, the Memory tuning table requires a special filegroup in the database to store the checkpoint file for the memory tuning table, unlike a traditional MDF or LDF file, which is a directory rather than a file because the checkpoint file is only attached, not modified, As shown in Figure 2.

Figure 2. Special filegroups required by the memory tuning table

Let's look at the memory-optimized filegroup as shown in Figure 3.

Figure 3. Memory optimized Filegroup

With the filegroup, we then create a memory tuning table, as shown in Figure 4.

Figure 4. Creating a Memory tuning table

Currently SSMS does not support creating a memory tuning table for the UI interface, so you can only create the memory tuning table through T-SQL, as shown in Figure 5.

Figure 5. Creating a Memory tuning table using code

Once the table is created, you can query the data, and it is worth noting that the query Memory tuning table requires snapshot isolation levels or hint, which is different from snapshot isolation, as shown in Figure 6.

Figure 6. Query Memory optimization table need to be prompted

In addition, the statement that creates the table shows that the hash index of the SQL Server 2014 Memory Tuning table currently supports only fixed bucket sizes and does not support the dynamic allocation of bucket sizes, so you need to be aware of this here.

Features that are incompatible with the memory database

Currently, database mirroring and replication are not compatible with the memory tuning table, but AlwaysOn, log shipping, backup restore is full support.

Performance test

It's a bunch of theories, and everyone may be looking depressed. Let me do a simple performance test to compare the use of the memory tuning table + Local compile stored procedure with the traditional B-tree table, as shown in Figure 7, the Memory optimization table + Local compiled stored procedure as shown in Figure 8.

Fig. 7. Traditional B-tree Tables

Figure 8. Memory Tuning table + local compilation stored procedures

Therefore, it is not difficult to see that the memory tuning table + Local compilation stored procedures have a performance improvement of nearly dozens of times times.