Performance Enhancements in SQL Server 2014


There has been few new versions of SQL Server released of recent which include SQL Server 2012 and the latest being SQL Server 2014. There are many new features introduced with SQL Server 2014 and there has been many enhancements to the existing features of database engine, SSRS, SSIS and SSAS. Below are list of some of the important Performance Enhancements in SQL Server 2014.

The most important and most talked feature in SQL Server 2014 is In-memory OLTP, which is a new lock-free, latch-free, optimistic concurrency approach of storing the user data in memory. In some specific scenarios the improvement in performance and/or throughput can be about 40x. Another important feature is Transactions with Delayed Durability. A delayed durable transaction returns control to the client before the transaction log record is written to disk which may increase the transaction performance significantly. Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.

Other enhancements include support of up to 640 cores and 4 TB of RAM on a physical box, however this limit may be lower in a virtual machine, due to operating system and Hyper-V limitations, not SQL Server itself.
Another enhancement is reduction of eager writes to tempdb. The database engine will try to defer or completely avoid any physical writes of data that it assumes will be transient and short-lived. This means less writes in tempdb. There have been significant Improvements to the SQL Server Query Optimizer which include improvement of the component of the database engine that creates and optimizes query plans.
Another improvement include support of clustered columnstore indexes, also many of the data type limitations have been removed, and the index is now writable. New archival compression algorithm, with a much greater compression rate at a slightly higher CPU cost though.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Leave a Reply

Your email address will not be published. Required fields are marked *