New Features of SQLServer2014 (1)-Memory Database

Introduction SQLServer2014 provides many exciting new features, but one of the most anticipated features is the memory database. When I attended the opening ceremony of SQLPASSSummit2012 in Seattle last year, Microsoft announced that it would include the Hekaton memory database engine in the next SQLServer version. Now with

Introduction SQL Server 2014 provides many exciting new features, but one of the most anticipated features is the memory database. When I attended the opening ceremony of SQL pass Summit 2012 in Seattle last year, Microsoft announced that it would include the Hekaton memory database engine in the next SQL Server version. Now with

Introduction

SQL Server 2014 provides many exciting new features, but one of the most anticipated features is the memory database. When I attended the opening ceremony of SQL pass Summit 2012 in Seattle last year, Microsoft announced that it would include the Hekaton memory database engine in the next SQL Server version. Now with the arrival of 2014CTP1, we can finally have a look at the Hong Kong virtual host.

 

Memory Database

In traditional database tables, due to the limitation of the physical structure of disks, the structure of tables and indexes is B-Tree, which makes such indexes very weak in the highly concurrent OLTP environment, although there are many ways to solve such problems, such as optimistic concurrency control, application caching, and distributed. However, the cost is still slightly higher. With the development of hardware over the years, it is not uncommon for a server to have several hundred GB of memory. Due to the maturity of the NUMA architecture, it also eliminates the bottleneck of multi-CPU access to memory, therefore, memory databases are available.

The Learning name of the Memory is Random Access Memory (RAM). Therefore, it is randomly accessed like its features. Therefore, for the Memory, the corresponding data structure will also be Hash-Index, the concurrency isolation method also converts to MVCC. Therefore, the memory database can Handle more concurrency and requests under the same hardware resources without being blocked by locks, SQL Server 2014 integrates this powerful feature and does not require additional fees like Oracle's TimesTen. Therefore, combined with the SSD AS Buffer Pool feature, the results will be very much worth looking forward.

 

SQL Server Memory Database Representation

The Hekaton engine of SQL Server consists of two parts: Memory Optimization table and local compilation and storage process. Although Hekaton is integrated into the relational database engine, its access method is transparent to the client, which means that the existence of the Hekaton engine is unknown from the client application perspective. 1.

The client APP does not perceive the existence of the Hekaton engine.

 

First, the memory optimization table will no longer have the lock concept (although the previous version has the optimistic concurrency control concept of Snapshot isolation, Snapshot isolation still needs to be locked when data is modified ), in addition, the Hash-Index structure of the memory-optimized table greatly improves the random read/write speed. In addition, the memory-optimized table can be set to a non-persistent memory-optimized table without logs (suitable for intermediate ETL result operations, virtual Host, but there is a risk of data loss)

The following describes how to create a memory optimization table:

First, the memory optimization table requires a special file group in the database to store the CheckPoint file of the memory optimization table. Unlike the traditional mdf or ldf file, this file group is a directory rather than a file, because the CheckPoint file will only be appended and will not be modified, as shown in figure 2.

. Special file groups required by the memory optimization table

 

Let's take a look at the memory optimization file group, as shown in 3.

. Memory optimized File Group

 

With the file group, create a memory optimization table, as shown in figure 4.

. Create a memory optimization table

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

. Use code to create a memory optimization table

 

After the table is created, you can query the data. It is worth noting that the snapshot isolation level or hint is required to query the memory optimization table, which is different from the snapshot isolation level, 6.

A prompt is required to query the memory optimization table.

 

In addition, the statement for creating a table shows that the Hash Index of the SQL Server 2014 memory optimization table currently only supports a fixed Bucket size and does not support dynamic allocation of the Bucket size.

 

Incompatible with memory databases

Currently, database mirroring and replication are not compatible with memory optimized tables in Hong Kong space. However, AlwaysOn, log transmission, and backup and restoration are fully supported.

 

Performance Testing

A bunch of theories have been written above, and everyone may be depressed. Next I will perform a simple performance test to compare the stored procedure using the memory optimization table + local compilation with the traditional B-Tree table, as shown in table 7 of B-Tree, memory Optimization table + local compilation and storage process 8.

 

. Traditional B-Tree table

 

Figure 8. Memory Optimization table + local compilation and storage process

 

Therefore, it is not difficult to see that the memory optimization table + local compilation and storage process has improved performance by dozens of times.