|
The single most awaited feature of SQL Server 2014 - Hekaton or the in-memory OLTP Database probably over shadowed the other advancements that SQL Server had to offer in the
new release. We would touch of Hekaton itself a little bit here. We would also like to point out some of the features that are pretty nifty when it comes to pushing performance
to the next level.
Microsoft turned the assault on OLTP by the in-memory Hekaton, a database engine that is not a replacement, but an addition to the traditional database. Unlike other competitors,
this in-memory system is built into the core rather than an additional bolt on feature. From our poking around with the new system, we found that this works really well on high
concurrent read/write OLTP system ranging between 100 to 200GB of data. Specifications indicates a good 10 fold performance increase (with a prescribed memory in place), while
fully complying to ACID properties. New locking implementations that makes Hekaton spin-lock free, increasing internal performance by multiple folds. Here are some of the
misconceptions about Hekaton that are not really true!!
- Hekaton is an in-memory technology; no data gets persisted
- Hekaton is an appliance meaning it only works on certain machines designed for it
- Hekaton provides 1000X performance gain
- Hekaton is a Microsoft answer for no-sql
SQL Server 2014 has an option of delaying durability in exchange for performance gains. Once a transaction in need of logging is executed, the control returns right away and
the logging is done asynchronously (Sun Micro systems introduced asynchronous writes decades ago on solaris). Worst case, a 64K block of logging might get lost should a failure
occur right in the middle of the logging process. A lot of systems can be built immune to this (especially given the maximum extent of data that can be lost), taking advantage
of this feature to a great extent.
SQL Server 2014 has introduced the option of natively compiling Stored Procedures, In-Memory optimized tables and table types. By compiling these programming constructs into
native code unlike traditional TSQL which is interpreted, these can be run at much faster speeds. DBAs do not have to maintain any natively compiled code, these are automatically
generated as DLLs and are maintained by SQL server. Some objects including the tables are recompiled every time the server restarts.
Among the other enhancements that SQL server has done with the new release, some of the features that would interest programmers are mentioned below. This is a subset of the list
of actual enhancements that were made available with the 2014 release.
- Online index rebuilding including rebuilding index for a single partition
- Improved visual query plan including plans for long running query, delayed durability etc.
- New cardinality estimation
- Columnstore index enhancements
|
