SQL Server 2014 new features

New In-Memory OLTP Engine

               By far the most important new feature in SQL Server 2014 is the In-Memory OLTP engine (formerly code-named Hekaton). By moving select tables and stored procedures into memory, you can drastically reduce I/O and improve performance of your OLTP applications.

               The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays. The In-Memory OLTP tables are copied into memory and made durable by transaction log writes to disk. An all-new lock-free engine processes the transactions for memory-resident tables. Stored procedure performance is improved by compiling the stored procedures into native code DLLs. 

               Some of the data types that aren't supported for memory-optimized tables include geography, hierarchyid, image, text, ntext, varchar(max), and xml. In addition, several database features can't be used with the new In-Memory OLTP capability. Database mirroring, snapshots, computed columns, triggers, clustered indexes, identity columns, FILESTREAM storage, and FOREIGN KEY, CHECK, and UNIQUE constraints aren't supported.  In-Memory OLTP won't be supported on the SQL Server 2014 Standard edition.

Updateable Columnstore Indexes

               Columnstore indexes are another of Microsoft's high performance in-memory technologies. Microsoft introduced the columnstore index in SQL Server 2012 to provide significantly improved performance for data warehousing types of queries. The new updateable columnstore index enables updates to be performed to the underlying table without first needing to drop the columnstore index. A SQL Server 2014 columnstore index must use all of the columns in the table, and it can't be combined with other indexes.

Delayed Durability

               Delayed Durability (DD to shorten) is basically asynchronous committing. What this means is that SQL Server is NOT going to wait until the log records of a given transaction are hardened on permanent storage, instead it’s going to return from the COMMIT instruction as soon as the records are placed in the log buffer in memory.

               Since DD doesn’t make SQL wait for permanent storage it can increase throughput by removing WRITELOG waits. Also, when used, SQL will accumulate delayed durable records in memory until the log buffer is full or until a fully durable COMMIT comes in. This means that in general, it will be doing less IO to the log compared to the same workload with DD turned OFF.

               Indeed, with DD there is a possibility that you could have data loss. This is because there are moments in time where a server could crash while some delayed durable records were in memory but not yet hardened to disk. My recommendation to our clients is this one: think of what data you would want to have MOST of it, but would be fine if you lost SOME of it.