Top 3 Features I’m Excited About in SQL Server 2014

Guest blog post by: SQL Server MVP Adam Jorgensen – PASS Executive Vice President, Finance & Governance, and President of Pragmatic Works – has been leading innovative SQL Server and Business Intelligence organizations for over a decade. His passion is finding new and creative avenues for clients and the community to embrace innovation and lower barriers to implementation. You can read his blog at AjBigData.com and follow him on Twitter at @AJBigData.

* * * * *

SQL Server 2014 is almost here! Many SQL Server professionals have been following the progress of this new release for a long time, and others have been so busy with day-to-day fire-fighting they haven’t had a chance to look at what’s coming. As the SQL Server team joins with PASS Virtual Chapters on a free “Countdown to SQL Server 2014” webinar series, I thought it would be a great time to highlight some of the features I’m most excited about.

Among the host of SQL Server 2014 capabilities that will enable innovative, new application and data platform opportunities, here are three of my favorites I can’t wait for the community to try out:

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 DesignThe 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. You can read more about the new estimator in Books Online: What’s New (Database Engine) and in the white paper “Testing cardinality estimation models in SQL Server.”

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.

These are just a few of the SQL Server 2014 features I think will be game-changers in many organizations, helping us deliver even higher performing and more highly scalable data systems. Which features are you most excited about? Let us know how you plan to use them. And I hope you’ll join PASS and Microsoft to learn more about this new release and how to take full advantage of it – sign up for your favorite webinars today.