Uncover new features of SQL Server 2014 (1)-memory database, sql2014

Uncover new features of SQL Server 2014 (1)-memory database, sql2014

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 look at its appearance.

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.

Figure 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, 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.

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.

Figure 3. Memory optimized File Group

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

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.

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.

Figure 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.

Figure 7. 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.

In-memory databases

Compared with the disk, the data read and write speed in the memory is several orders of magnitude higher. storing data in the memory can greatly improve the performance of applications compared to accessing data from the disk. At the same time, the memory database abandons the traditional method of disk data management, and re-designs the architecture in the memory based on all the data, data caching, fast algorithms, and parallel operations are also improved accordingly. Therefore, the data processing speed is much faster than that of traditional databases, generally more than 10 times. The biggest feature of a memory database is its "master copy" or "working version" resident memory, that is, active transactions only deal with the memory copy of the real-time memory database. Obviously, it requires a large amount of memory, but not all databases are stored in the memory at any time, that is, the memory database system still needs to process I/O.

Memory databases exchange real-time data processing at the cost of memory resources. Both memory databases and disk databases are the relational database products required by every enterprise in the information society, the disk database solves the problems of large storage capacity and data analysis, while the memory database solves the problems of real-time processing and high concurrency. The two are complementary, and the real-time transaction processing performance of the memory database is far better than that of the disk database. However, his data security has not reached the level of the disk database.
The memory database uses the physical memory as the first storage medium for data and the disk as a backup. With the development of the telecom business, the system has high requirements for real-time performance and flexible business modification. In this case, the demand for memory databases is also increasing. Disk databases store data in the memory for processing. The manageability and data security and reliability of this method are not guaranteed. The memory database is designed to improve this weakness.

In fact, memory databases are not a fashionable technology. They appeared in the late 1960s s, but they did not develop until the late 1990s s due to market demands. As a new generation of databases, Altibase products have moved towards hybrid databases. Its version Altibase 4.0 already has a set of built-in disk databases. Once users buy Altibase memory databases, they no longer need to purchase disk databases. It stores hot data (frequently used, frequently accessed, and frequently computed data) in the memory database, and places historical data in the disk database, which can further reduce investment for users.
For memory databases, part of the same database can be stored on disks, while the other part is stored in memory. You can choose to store data in a memory table to provide real-time data access. If the access time is not urgent or the memory space occupied by the data is too large, you can store the data in the disk table.

For example, when a mobile phone user starts to call, if the hybrid Data Management Engine Based on the memory database technology is applied, the user's service options are retrieved from the memory table and the user's identity is verified immediately, archive the call list and billing list to the disk table. Thus, the balance between speed and resource usage is achieved.

A very important feature of the Memory Database technology is that it can implement full transaction processing for the data in the memory, which is completely different only by placing the data in the memory in arrays and other forms. In addition, the memory database is unrelated to the application. Obviously, this architecture is rational. The memory engine can use the same database for query and archiving, and the memory and disk tables also use the same access method. Storage Options are completely transparent to application developers.

For memory databases, data is managed in memory, not just as a database cache. Unlike other databases that cache disk data blocks to the primary storage, the memory engine of the memory database uses the data structures and algorithms specially designed for Random Access to the memory, this design avoids cache database performance problems frequently caused by the use of sorting commands. The memory database reduces disk I/O and achieves a processing speed comparable to that of traditional databases dominated by disk I/O.

Therefore, the application of the memory database technology can greatly improve the speed of the database, which provides powerful support for database applications that require high-speed response, such as telecommunications and finance.

Because most of the data is stored in the memory for operations, the memory database performance is much higher than that of the disk database.

What are the new features of SQL Server 2008 R2?

Trusted and scalable platforms

Offline business applications (LOB) are the key business of the IT and business departments. The key to secure and reliable storage, centralized management, and data distribution to users is these LOB applications. SQL Server 2008 provides a high-performance database platform, a reliable and scalable enterprise platform, and is easy to manage. SQL Server 2008 R2 will help the IT Department provide the most advanced and familiar SQL Server Management Tool Platform today, more cost-effective scalability.

Hardware Innovation

SQL Server 2008 R2 helps you minimize the total cost of ownership by leveraging the advantages of the latest hardware technology. The collaboration between Microsoft Windows Server 2008 R2 and SQL Server 2008 R2 allows customers to expand to up to 256 Logic processors.

In addition, Windows Server 2008, which supports Hyper-V technology, requires greater processing capabilities and fully utilizes the advantages of a new multi-core system. This means that the support of each physical host for multiple virtual systems reduces costs and increases scalability and flexibility of the virtual infrastructure. The real-time migration of the new Hyper-V technology allows servers to be migrated between two hosts without interrupting any services.

IT and development benefits

Allows administrators to centrally monitor and manage multiple database applications, instances or servers, accelerate development and application deployment, and provide better support, online migration is achieved through Windows Server 2008 R2 that supports the Hyper-V function.

Manage self-service business intelligence

Powerful BI tools are available to all Excel and SQL Server users and authorized users for new business accumulation and Sharing powerful business intelligence solutions, IT also enables IT to monitor and manage user-generated BI solutions.