SQL Server 2014: The ability of relational type, the speed of NoSQL

For the first time in more than 4-5 years, Microsoft has tried to rewrite the query execution engine that was established in SQL Server version 7, released in 1998. The goal of rewriting is to provide a high speed similar to NoSQL without sacrificing the characteristics of the relational database.

At the heart of this one attempt is the Hekaton project, the Memory tuning table. Although it can still be accessed through traditional T-SQL operations, it is already implemented internally based on completely different technologies. This technology is intended to be consistent with today's three major trends in server hardware.

Falling Memory prices

Early SQL Server could host tables in memory in some way, but this feature was later proven to cause damage to performance and was subsequently discarded for nearly 10 years. At the time, it was completely unreasonable to reside the contents of the entire table in a memory system with limited capacity. Now that 64-bit processors have been widely used and memory prices are falling, it makes sense to choose to keep large databases in memory.

CPU Speed stalled

While the complexity of the CPU is still rising, its clock speed has almost stalled for nearly 10 years. Therefore, to see the increase in linear performance, in addition to the ability to take advantage of caching, also requires that the code to run more efficient.

Here, T-SQL once design decisions can play a role. In the current design, whether it is an instant query or a query based on a stored procedure, it is simply compiled into an intermediate language and then executed in an interpreted manner, instead of being transformed into machine code by Just-in-time compilation. While this approach can accept higher complexity, it also pays to reduce performance.

This has been acceptable in the past because, although data loading is increasing, the CPU's ability to process continuous data is increasing. But this is not going to work today, and Microsoft has decided to build a new execution engine that relies on fully compiled machine code.

As we pointed out in a previous report, the new execution engine only supports stored procedures. You give up the code for the dynamic query, and you get the highly optimized C code that is specifically generated for the table you're using.