SQL Server 2014 comes with a set of enhancements as compared to its processors that brings a lot of business benefits to the applications in OLTP and OLAP environments. This article highlights a few key enhancements that are built into the product.

Performance Enhancements

These are performance enhancements that can help your workload run faster.

• In-Memory OLTP:  This is a new feature allows the database to handle in-memory operations in OLTP scenarios (resolve issues in high concurrency situations). This component is called as “Hekaton”.  Microsoft has released an in-memory component called ‘xVelocity’ catering to OALP requirements, along with SQL Server 2012.
• On-line indexing at the partition level: This feature allows index rebuilding done at a partition level. Also, the index statistics can be managed at the partition level which will be a huge improvement in performance
• Updatable Column Store Indexes (CSI):  The CSI feature has been introduced in 2012. The limitation with CSI was that the table cannot be modified once a CSI is created.  In order to update table information the index needed to be dropped or disabled and then rebuilt the index. This new feature provides the ability to load or delete data from the table with Column Store indexes.
• Buffer Pool Extension to Solid State Drives (SSDs). Ability of each node to have its own SSD or SSD Array for buffering (just like you would with TempDB) and thus increase in the performance by means of faster paging.  In this way, one can cache frequently used data on SSDs. This feature can be best leveraged in case of read-heavy OLTP workloads. 
• Resource Management. Resource Governor can control  I/O along with CPU and Memory (provided by the previous versions).

Improved Scalability

SQL Server has imcreased the amount of hardware it can use.

• Ability to scale up to 640 logical processors and 4TB of memory in a physical environment
• Ability to scale to 64 virtual processors and 1TB of memory when running in a virtual machine (VM).

High-Availability Enhancements

• AlwaysOn Availability Groups (AG) get more secondary nodes: Always On supports now up to 8 secondary nodes instead of 4 (is the case with SQL 2012). Of course, Enterprise Edition is needed.
• AlwaysOn Availability Group’s Readable Secondary will be ON-LINE (more reliable). In SQL 2012, if the primary drops offline, the readable replica databases drop offline. In SQL 2014, the secondary remain online and readable when the primaries aren’t available.

• Azure Integrated AlwaysOn Availability Groups:  Uses Azure VMs as AlwaysOn AG replicas. This replicas can be created asynchronously on cloud (Azure platform) that saves from paying for expensive offsite datacenter space with machines that sit idle all the time.  

Backup Enhancements

• Smart Backup to Azure (Windows Azure Integrated Backup): Another new backup feature is Smart Backups. With Smart Backups SQL Server determines whether a full or incremental backup is needed and backs up accordingly to Azure.
•  Azure backup feature and the Azure AlwaysOn Availability options are completely integrated into SSMS.

Microsoft® in-memory database engine

Microsoft® implemented an in-memory transactional engine with the project code name “Hekaton*”. Hekaton is expected to dramatically improve the throughput and latency of SQL Server’s on-line transaction processing (OLTP) capabilities. Hekaton is designed to meet the requirements of the most demanding OLTP applications for financial services companies, online gaming and other companies which have extremely demanding TP requirements. This product achieves breakthrough improvement in TP capabilities without requiring a separate data management product or a new programming model. It’s still SQL Server!

Note*: Hekaton is from the Greek word ?κατ?ν for “hundred”. The design goal for the Hekaton original proof of concept prototype was to achieve 100x speeds (possibly)  for certain TP operations.

Key Features:

• Implements a row-based technology squarely focused on transaction processing (TP) workloads. However, the xVelocity* and Hekaton in-memory approaches are NOT mutually exclusive. The combination of Hekaton and SQL Server’s existing xVelocity column store index and xVelocity analytics engine, will result in a great combination.
• Hekaton (In-memory TP engine) and xVelocity column store index will be built-in to SQL Server, rather than a separate data engine, which is a conscious design choice

Note*: xVelocity is OLAP version of in-memory database released along with SQL Server 2012.

Technology Implementation:

• Hekaton works by providing in-application memory storage for the most often used tables in SQL Server. Identifies tables that are most accessed, and will store them in the system’s main memory for faster access time.
• Hekaton compiles T-SQL stored procedures directly into native code for faster execution.
• Hekaton uses a new concurrency control mechanism developed by Microsoft® team and researchers from the University of Wisconsin using lock-free data structures for better scalability across multiple cores, avoiding locks while preserving ACID transaction integrity.

These features may be available already outside SQL Server such as own Power Pivot and Power View. However, the biggest difference is that Hekaton is built directly into SQL Server, so that there are no extensions, downloads, or interfaces that can slow down the very program meant to help increase your speed.

A few challenges with the implementation of in-memory OLTP database are as follows.

• Need to change your data model. Need to bring significant changes to the traditional OLTP model. For ex: Identity fields aren’t supported, may have to use a GUID as a primary clustered key.
• Need to change application code to replace ad-hoc SQL queries with stored procedure calls. Hekaton works best with stored procedures, as the stored procedures can compile into native code.
• Since the processing happens in in-memory only, if there is any sudden growth in the Hekaton tables, we can cache less of your other tables. We may run out of memory.