SQL Server 2014 is the next generation of Microsoft’s information platform, with new features that deliver faster performance, expand capabilities in the cloud, and provide powerful business insights. In this blog posting I want to give you an overview about the various performance related enhancements that are introduced.
Lock Priorities
As you might know, SQL Server gives you in the Enterprise Edition Online operations, or as I call them “Almost Online Operations”. They are almost online, because internally SQL Server still has to acquire some locks, which can lead to blocking situations. For that reason SQL Server 2014 introduces Lock Priorities, where you can control how SQL Server should react, if such a blocking situation occurs.
Buffer Pool Extensions
The idea about Buffer Pool Extensions is very easy: expand the Buffer Pool with a paging file that is stored on very fast storage, like SSD drives. The Buffer Pool Extensions are coming quite handy, if you don’t have the ability to physically add more additional RAM to your database server.
Resource Governor
Resource Governor was introduced first back with SQL Server 2008, but wasn’t really a mature technology, because you had no possibility to govern I/O operations on the storage level, and you also had no chance to limit the size of the Buffer Pool for a specific workload group. With SQL Server 2014 things are changing, because you can now throttle I/O operations. Limiting Buffer Pool usage is still not possible, but hey who knows what comes in SQL Server 2016 .
Clustered ColumnStore Indexes
One of the hottest enhancements in SQL Server 2014 is the introduction of Clustered ColumnStore Indexes , which is an amazingly new way concept how to deal with ColumnStore data in SQL Server. And in addition the Clustered ColumnStore Index can be also changed directly – without using tricks like Partition Switching.
In-Memory OLTP
With In-Memory OLTP Microsoft claims that the performance of your workload can be improved up to 100x. Awesome! Everything is now stored directly in the memory, without touching your physical storage anymore (besides the transaction log, if you want). And in addition In-Memory OLTP is based on so-called Lock Free Data Structures, means locking, blocking, latching, and spinlocking is just gone. Of course, there are side-effects and even limitations with this promising approach…
Delayed Transactions
It doesn’t matter how good the throughput of your workload is, the final barrier and bottleneck is almost every time the transaction log. Because of the Write-Ahead Logging mechanism used by SQL Server, a transaction must be always written physically to the transaction log, before the transaction is committed. When your transaction log is on slow storage, your performance and throughput will suffer. For that reason SQL Server 2014 implements so-called Delayed Transactions.
Cardinality Estimation
Cardinality Estimation is the most important thing in a relational database, because these estimations are feeded into the Query Optimizer, whose job it is to produce a good-enough execution plan. With SQL Server 2014 Microsoft has rewritten the cardinality estimator completely from scratch to overcome some limitations based on the history of this very important component.