New Features of SQL Server 2014 (1): Memory Database

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 name of the memory is Random Access MemoryRAM), so 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, there will be no lock in the memory optimization table. 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, so that no logs are suitable for intermediate ETL result operations, 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 cannot be compatible with memory optimized tables, but 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.

Edit recommendations]

1. Restore SQL Server simple mode by mistake Delete heap table records
2. Microsoft SQL Server Data Engine and Analysis Service
3. SQL Server Data Mining Rules for product Recommendation 1
4. SQL Server advanced content: subqueries and table links
5. Data Compression in SQL Server 2008