Monday, December 1, 2014

What's new in SQL Server 2014

http://msdn.microsoft.com/en-us/library/bb500435.aspx

1. Delayed Durability
SQL Server 2014 lets us mark certain transactions as delayed durable, meaning control returns to the client before the log record is written to disk, as opposed to fully durable, which hardens the transaction log to disk before returning control to the client. Also called Lazy Commit, delayed durability can help reduce latency related to log I/O by keeping the transaction log records in memory and writing to the transaction log in batches.  If you’re experiencing log I/O contention, it can also help reduce waits in the system. This setting – which we can control at the database, commit, or atomic level – provides for many new scalability opportunities and challenges. I’m looking forward to the solutions our community will create to leverage this capability.
2. New Cardinality Estimation Design
The cardinality estimator has been redesigned in SQL Server 2014 to improve query plan quality and query performance. This new estimator calculates cardinality, essentially the number of rows the optimizer processes in an operation, using assumptions and algorithms that support modern OLTP and data warehousing workloads. The Microsoft SQL Server engineering team did a lot of research on these workloads to deliver a modern algorithm set that is customer tested and proven. 
3. Clustered Columnstore Indexes
Since SQL Server 2012 introduced nonclustered columnstore indexes, many of us have been looking forward to clustered columnstore indexes and seeing this new SQL Server 2014 feature in action in our own environments. A clustered columnstore index will improve data compression and query performance for many data warehousing workloads, especially in read-heavy and bulk-loading scenarios. And because the CCI is updatable, we can perform Selects, Inserts, Updates, and Deletes on these tables while still getting the performance of a clustered columnstore.