Top New Microsoft SQL Server 2014 Features

Microsoft released new SQL Server 2014 version on 1st of April 2014. SQL Server 2014 was released with on two years of release of SQL Server 2012. Most famous and talked feature of SQL Server 2014 is In-Memory OLTP (In-Memory Optimization). In SQL Server 2012, most famous feature was AlwaysON Availability Groups. There are many other new features introduced in SQL Server 2014 along with the In-Memory OLTP.

Below is list of some of the top features released with SQL Server 2014.

In-Memory OLTP (In-Memory Optimization) – As the name says, it is designed to greatly improve the performance on Online Transactional Processing (OLTP) applications, by keeping all the data in memory thus reducing the physical IO, which was the cause of slowness for most of the applications. In-Memory OLTP engine has been developed and has been integrated with the SQL Server Database Engine as one component, rather than having to install as a separate component.

In order to use SQL Server 2014 In-Memory OLTP, we need to identify highly transactional tables and convert them as memory optimized tables. Memory-optimized-tables supports all the ACID properties which are Atomicity, Consistency, Isolation, and Durability. In-Memory Tables can be accessed using regular Transact-SQL in the similar way as we access the disk-based tables. In one query we can perform DML operations on both In-memory optimized tables and disk-based tables as well. Regular Stored procedures can access In-Memory tables and regular tables. Natively compiled stored procedures introduced on SQL Server 2014 is used specifically to work with In-Memory optimized tables only, which provide additional performance benefits. The In-Memory OLTP engine is designed to handle extremely high session concurrency for OLTP type of transactions driven system. It achieves this by using latch-free data structures and optimistic, multi-version concurrency control, which results in predictable, very fast, low latency, high throughput for OLTP transactions. There are other factors too which affect the performance gains. Microsoft in their official documentation mentioned that commonly we see 5-to-20 times performance improvements and can be increased further by following recommended In-Memory query optimization techniques. More information can be found in Microsoft official website.

Enhancements to AlwaysOn Feature – SQL Server 2014 contains many new enhancements for AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups compare to SQL Server 2012.

– Maximum number of secondary replicas has been increased from 4 to 8.
– When secondary replicas are disconnected with primary replica or when Secondary replicas is not part of the Quorum, readable secondary replicas still remain available for read only workloads.
– Cluster Shared Volumes (CSVs) whose support to SQL Server was introduced with SQL Server 2014 can now be used in Failover cluster instances (FCIs) in SQL Server 2014 as cluster disks.
– Existing AlwaysON Availability Group DMV’s are enhanced and new DMV’s and functions are introduced as well for better monitoring and troubleshooting.
– Using “Add Azure Replica”, we can use Azure server as a secondary replica and add Azure databases as the secondary AlwaysON Availability Group databases for Read_Only purposes in hybrid IT environment.

Enhancements to Resource Governor – Previous versions of the Resource Governor allowed us to specify the limits for the amount of CPU and memory for a particular application request. With SQL Server 2014, now we can also control the I/O limits for a particular application request. MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are provided to control the physical I/O’s for a particular user threads.

Enhancement to Statistics – Statistics is the main resource and used to calculate the cost for a query and to create best execute plan. From SQL Server 2014, CREATE STATISTICS and statistic related T-SQL command now allows statistics to be created per partition by using the INCREMENTAL option.

Buffer Pool Extension – With SQL Server 2014 Buffer Pool Extension we can use solid-state drives (SSD) as Non-Volatile RAM which extends the BufferPool region allowing to pageout small workloads to SSD’s, as I/O operations between RAM and SSD’s are fast enough, the SSD’s will server as a backup memory. This enhancement significantly improves the I/O throughput for Read heavy workloads.

Enhancements to Columnstore Indexes – Allows DML operations as well on ColumnStore indees, which was not allowed before. This was possible as now we can create clustered columnstore index which improves data compression and query performance for data warehousing workloads. Since the clustered columnstore index is updateable, the workload can perform DML operations like insert, update, and delete. Another enhancement include, allowing SHOWPLAN to display the information related to ColumnStore Indexes as well in the query plan.

Enhancements to ONLINE Rebuild Index Operations – Starting SQL Server 2014, we can specify whether SPID or Session which is performing ONLINE rebuild index operation can now run at a lower priority compared to application SPID’s or sessions, thus reducing the blocking of application sessions. We can also configure online rebuild operation to kill itself, if it is blocking for more than certain duration. WAIT_AT_LOW_PRIORITY option allows us to specify how long the rebuild process should wait for the necessary locks and if it does not get the locks by that time, then to terminate rebuild session.

Delayed Durability – Delayed Durabile is also known as lazy commit. By default, SQL Server uses “fully durable transaction commits” which are synchronous and notifies a commit transaction as successful and return control to the client only when the associated log records for the transaction is written to disk. In “Delayed durable transaction”, commits are asynchronous and notifies a commit transaction as successful even before the log records for the transaction are written to disk, thus reducing any latency to wait for the log record to be flushed to the physical disk. Writing the transaction log entries to disk is mandatory for a transaction to be durable, so even in Delayed durable the transactions only become durable later when transaction log of that transaction are flushed to physical disk. This is to be only used for some applications where loss of some transactions is acceptable.

Enhancements to Cardinality Estimation – Cardinality Estimation logic has been redesigned in SQL Server 2014 which improved in generating quality query plans, thus improving query performance.

Enhancements to Backup and Restore – There are few enhancements to Backup and Restore process in SQL Server 2014 which include, adding GUI feature of performing a backup to a URL, thus allowing us to backup to or restore from Windows Azure Blob storage service. Another enhancement is the ability to encrypt the data while creating a backup, resulting in an encrypted backup file.

Hope this was helpful.

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