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.


In-Memory OLTP (In-Memory Optimization) Frequently Asked Question and Answers (FAQ) in Interviews Part 1

In-Memory OLTP was introduced in SQL Server 2014 and the goal of it is significantly improving the OLTP database applications performance. In-Memory OLTP is a memory-optimized database engine code which has been integrated with the SQL Server engine code, thus making SQL Server competent to improve the OTLP applications performance. It is very common in interviews to ask questions about new and popular features of SQL Server like AlwaysON Availability Groups, In-Memory OLTP, etc. Proving strong knowledge on new features will showcase the interest you have in updating your skills and adapting quickly, thus provides better possibilities of clearing the interview. Not only from an interview perspective, it is important as a DBA to update our skills quickly, otherwise there wont be much market value to old skills, which become old very quickly. If a client asked a question, we should be able to atleast know the very basics, rather than proving ourselves outdated.

Below are some of the common questions which may be asked in interviews regarding In-Memory OLTP.

In which version of SQL Server was In-Memory OLTP introduced? or What is the top feature in SQL Server 2014
In-Memory OLTP introduced was introduced with SQL Server 2014 version.

Which SQL Server 2014 Edition include In-Memory OLTP?
In-Memory OLTP is available in SQL Server 2014 Enterprise Edition, Enterprise Evaluation and Developer Edition.

Does In-Memory OLTP requires applications to be modified?
Yes, the table schema has to be modified and changes to the application are required as well.

Can we create the In-Memory Tables in default FileGroup?
No, In-Memory Tables cannot be stored in default or regular filegroups, we need to create separate filegroup and files to store In-Memory Tables.

Can we create multiple in-memory filegroups and files?
We can create only one filegroup for storing In-Memory tables, but the filegroup can have multiple files and can be placed on different drives.

Can my database store regular tables as well as In-Memory tables?
Yes, we can have both regular and in-memory tables in same database.

Will my data persist even after restarting my SQL Server?
If we create In-Memory table using option DURABILITY = SCHEMA_AND_DATA, then we will have data retained even after restart of SQL Server. If SCHEMA_ONLY option is specified, then we will loose all the data.

Can we create clustered index on In-Memory Table
No, Clustered indexes are not supported on In-Memory Tables.

Can we create Non-Clustered Indexes on In-Memory Tables?
Yes, but you need to create the Non-Clustered Indexes during the time of creation of the In-Memory Table itself, we cannot add new indexes, alter existing indexes or drop any indexes on In-Memory Tables, so need to plan the indexes well before hand.

Does Non-Clustered Indexes on In-Memory Tables take storage on the disk?
No, Non-Clustered Indexes on In-Memory Tables are only in memory, and they are not persisted on the disk. When SQL Server is stopped or Database is taken offline, the indexes are lost. Also, any operations on these indexes are not logged to transaction log file, as no recovery is required for the indexes. During startup of database, indexes are populated into the memory as part of recovery process.

Can we add covering indexes for In-Memory tables?
No, Memory-optimized indexes are inherently covering, meaning all columns are virtually included in the index itself, thus bookmark lookups are not required for memory-optimized tables.

If we cannot alter the indexes, then how do we reduce the fragmentation for In-Memory indexes?
Indexes do not persist on physical storage, they are recreated every time SQL Server restarts or databases is brought online, so there wont be any index fragmentation in in-memory indexes.

What are different kinds of Indexes supported for In-Memory tables?
We can create two kinds of in-memory indexes for In-Memory tables, which are Nonclustered Hash Index and Nonclustered Range Index.

Nonclustered Hash Index is used for queries which are going to perform equi-joins

Nonclustered Range Index is used for queries which performs range scans and ordered scans

What happens when we create in-memory table?
When we run the DDL for creating the In-Memory table, it will be converted into a in-memory table DDL and is loaded into SQL Server memory. We can find what all In-Memory tables are created and loaded in memory by running below query

Can I use regular T-SQL queries and Stored Procedures to access In-Memory tables?
Yes, we can use regular T-SQL queries and Stored Procedures to access In-Memory tables. Also, In-memory OLTP engine introduces a new type of stored procedure called natively compiled stored procedure, which can oly access In-Memory tables. When we create the natively compiled stored procedures, they query plan gets created during the compile time itself. These stored procedures are compiled by Microsoft’s Visual C/C++ compiler to native code and provides maximum performance benefits and is the preferred way of accessing In-Memory tables. At this time, there is limited T-SQL support for these stored procedures. When this SP gets compiled, DLL will be created and loaded into SQL Server Memory. We can verify the same by running above mentioned SQL query.

How does In-Memory tables provide high performance?
In-Memory tables reside completely in memory, so the access to the in-memory data is always very fast, as there is no requirement of physical IO from disk to memory. Using natively compiled stored procedures provide additional performance boost as they are per-compiled and no permission checks need to be performed later, as it is done during the time of compilation itself.

Can we create database to hold In-Memory tables using SSMS GUI?
Yes, SQL Server 2014 GUI supports creation of database with In-Memory filegroup and filestream data file for In-Memory usage. But we cannot create In-Memory table using GUI, we need to use T-SQL commands for that.

How do I know if a Table or Stored Procedure are candidates to be migrated as In-Memory OLTP?
Transaction performance reports tool tell us which tables in our database will benefit if ported into In-Memory OLTP. Transaction performance collector tool is available in SQL Server 2014 Management Studio, which helps us to evaluate, if In-Memory OLTP will improve our database application’s performance. Transaction performance analysis report also tells how much work we must have to do in order to enable In-Memory OLTP in your application.

How do you migrate regular table into In-Memory table?
We can use the memory optimization advisor tool which helps us in migrate the disk-based database table into In-Memory OLTP.

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.