MS SQL Server 2014: 5 important new or enhanced features

Microsoft have released SQL Server 2014 with many enhanced features comparing with old versions. In this article, I'll introduce 5 new important features that you can consider to upgrade for your organization.

1. Buffer Pool Extensions

No doubt, it is the most significant new feature of SQL Server 2014, especially it is available for Standard 64 bit version. It is recommended for write-heavy applications. I have tested it, it helps to increase 30% - 50% writing performance, for reading (select) it seems can't help much. Below is the illustration of high-level architectural overview of the buffer pool relative to other SQL Server components.

Basically, this feature requires a SSD disk to host its extension file. The performance is very depended on SSD, some manufacturers have optimized their product to use with SQL Server 2014 (e.g. Fusion) so their product can get better performance.

To enable this feature, you can use the following command:
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(
   FILENAME = 'D:\BufferPoolExtensionFile.BPE',
   SIZE = 40 GB
)
GO

In which, D is SSD, min of SIZE = max_server_memory configuration, an optimal ratio SIZE:max_server_memory  is 4:1 to 8:1.

To check buffer pool configuration, run:
SELECT path, file_id, state, state_description, current_size_in_kb 
FROM sys.dm_os_buffer_pool_extension_configuration;

2. In-Memory OLTP Engine

It is also know in formerly project Hekaton of Microsoft. This feature is available only for Enterprise 64 bit version. By moving tables and stored procedures used frequently into memory, it reduces I/O and improve performance of your OLTP applications. Microsoft states that some applications can expect up to a 30x performance improvement. However, this feature just supports limited types of data, so you may need to re-design your tables and stored procedures to load them into memory. It is not an easy work. I have tested this feature and seen that it helps increasing 4x performance in my case with heavy processing data. In the case I do a simple query and get just 1 row data, it is slower than normal. So be careful what you should put into the memory.

3. Columnstore Indexes For All

It is first introduced in SQL Server 2012 to provide significantly improved performance for data warehousing types of queries. Microsoft states that for some types of queries, columnstore indexes can provide up to 10x performance improvements. However, in the original implementation of the columnstore indexes, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction. The new updateable columnstore index enables updates to be performed to the underlying table without first needing to drop the columnstore index. A SQL Server 2014 columnstore index must use all of the columns in the table, and it can't be combined with other indexes.

4. Windows Server 2012 Integration

SQL Server 2014 enhances integration with Windows Server 2012 as also as Windows Server 2012 R2. SQL Server 2014 Enterprise will have the ability to scale up to 640 logical processors and 4TB of memory in a physical environment (128GB for SQL Server 2014 Standard ). In the case of using virtual machine, it can scale up to 64 virtual processors and 1TB of memory.

Its Resource Governor provides a new capability to manage application storage I/O utilization. First introduced with SQL Server 2008, the Resource Governor originally enabled you to limit the amount of CPU and memory that a given workload can consume. Now SQL Server 2014 extends the reach of the Resources Governor so that you can now manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool, allowing you to have more predictable application performance.

SQL Server 2014 also supports new Storage Spaces feature of Windows Server 2012. With Storage Spaces, you can create pools of tiered storage to improve application availability and performance. In addition, SQL Server 2014 can take advantage of Server Message Block (SMB) 3.0 enhancements to achieve high-performance database storage on Windows Server 2012 R2 and Windows Server 2012 file shares. Many enhancements were made to SMB 3.0, with the most notable being SMB Transparent Failover and SMB Direct. The new SMB Transparent Failover feature provides highly reliable SMB storage that's fully supported for applications like SQL Server and Hyper-V. With the new SMB Direct feature, you can leverage the NIC's Remote Direct Memory Access (RDMA) feature to provide access speeds for SMB file shares nearing the access speed for local resources.

5. Enhanced AlwaysOn Availability Groups

This feature has been enhanced with support for additional secondary replicas and Windows Azure integration. First introduced with SQL Server 2012, AlwaysOn Availability Groups boosted SQL Server availability by providing the ability to protect multiple databases with up to four secondary replicas. In SQL Server 2014, Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now available for read-only workloads, even when the primary replica is unavailable. SQL Server 2014 also provides Windows Azure AlwaysOn integration. This new integration feature enables you to create asynchronous availability group replicas in Windows Azure for disaster recovery. In the event of a local database outage, you can run your SQL Server databases from Windows Azure VMs. The new Windows Azure AlwaysOn availability options are fully integrated into SQL Server Management Studio (SSMS).

Do you think what else? All comments are welcome.