SQL Server 2014 has been launched on April the 1st and we’re very excited about this new version as it promises to be a great one.
In this article, we will show you what are its new features and how they can help you become more efficient in your work through a more performant system.
We’re all excited about the new SQL Server 2014 and its new previewed features, but how do they actually help you in your daily tasks?
We will try to show you why it’s important to take a look at these new features and consider trying out the 2014 edition.
SQL Server 2014 new features
In-Memory OLTP
The most talked about and most awaited feature of SQL Server 2014 is most undoubtedly the new In-Memory OLTP engine, internally called Hekaton. It was announced at PASS 2012 and was warmly welcomed by the industry.
Hekaton comes from Greek and means 100 (hundred) which was the aim of performance improvement for this feature over previous methods of accessing and manipulating data. Actual performance improvements depend on many factors that may vary from client to client, but the most common improvements in terms of performance are between 5x and 20x FASTER!
In-Memory OLTP or online-transaction processing is a database engine that is memory-optimized specifically for OLTP operations and is part of SQL Server’s engine.
This engine has been designed to deliver increased concurrency and it has a brand new mechanism to control and eliminate delays due to locking with an optimistic concurrency control mechanism.
The OLTP tables are copied to memory and are made durable by writing transaction logs to disk. Also, the new engine has an improved lock-free mechanism that can process transactions for the in-memory tables.
The performance of stored procedures is also improved due to compiling these stored procedures into DLL native code. Currently, stored procedures are being interpreted, an operation that adds a few clocks to their execution, but by compiling them to native Win64 code, they are made to be directly executable, and thus their performance is maximized and their execution time is minimized.
If you want to evaluate how the new In-Memory OLTP engine improves your database’s performance, then Microsoft has a new Analysis, Migrate, and Report tool (AMR).
The in-memory OLTP engine will work just fine with commodity hardware, but this feature does have some limitations. First of all, not all data types are going to be supported for the memory-optimized tables. Some of these data types are:
-
-
- geography
- hierarchy
- text
- ntext
- image
- xml
- varchar(max)
-
Moreover, other standard SQL Server features cannot be combined with the In-Memory OLTP feature. These features include:
-
-
- database mirroring
- snapshots
- triggers
- clustered indexes
- computed columns
- identity columns
- constraints such as FOREIGN KEY, CHECK, and UNIQUE
- FILESTREAM storage
-
In terms of OS’s which support this feature, you can use either Windows Server 2012 or Windows Server 2012 R2 and also Windows Server 2008 R2 SP2. Also, you will have to use either one of the Enterprise, Developer, or Evaluation editions of SQL Server 2014 in order to have access to In-Memory OLTP.
SQL Server Data Files in Windows Azure
SQL Server Data Files in Windows Azure will enable you to store native SQL Server database files as Blob files on Windows Azure.
This feature will give you the possibility to create a SQL Server database that is running either on-premises or inside a virtual machine in Azure, with dedicated storage in Azure Blob Storage.
Host a SQL Server Database in a Windows Azure Virtual Machine
Just as the title says, you are able to deploy a SQL Server database to a Virtual Machine that resides in Azure. You can find more information and guidelines about this, here.
Backup and Restore Enhancements
In terms of enhancements brought to these features, we have a detailed article that already covers these areas, here.
But just to name a few of the improvements, the new SQL Server 2014 edition brings to the table new encryption mechanisms for your backups, managed backups to Azure, and the ability to backup to URL straight from SSMS.
New Design for Cardinality Estimation
The new estimation logic which was designed for the cardinality estimator has improved the quality of the query execution plans and thus has a direct impact on the performance of queries.
The new estimator has incorporated algorithms and assumptions which work with modern OLTP and data warehouse workloads.
Although this new feature can show improvements for most of the queries, there might be some queries that show a regression when compared to the previous version of the cardinality estimator. You can performance tune and follow some testing recommendations, which you can find in this article.
Delayed Durability
With SQL Server 2014 you have a new ability introduced to the table. The reduced latency gained from designating all or some of the transactions as delayed durable.
Delayed durability in the context of a transaction is when a transaction will return the control to the client before the transaction log has been saved to disk. This durability feature can be controlled at multiple levels, database, COMMIT, or ATOMIC blocks. You can find more detailed information about this feature, here.
Resource Governor Enhancements for Physical IO Control
The improved Resource Governor feature will allow you to set limits in terms of the CPU that is being used, physical IO operations, and memory that are being used from within a resource pool by incoming applications which request these resources.
In SQL Server 2014 you will now be able to use two new settings to take better control of your resource pool’s physical IO calls which are issued for user threads with MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME.
For more information on this please review this article on Resource Governor Resource Pool and on one how to create a RESOURCE POOL with T-SQL.
The setting MAX_OUTSTANDING_IO_PER_VOLUME which is available through ALTER RESOURCE GOVERNOR will set the maximum number of IO operations per disk volume.
This feature can be used to fine-tune resource governance and to IO the details of your disk volume and also it can be used to limit the number of IO operations that are being issued at the boundary of your SQL Server instance.
Columnstore Indexes
Columnstore indexes is another card in the suite of high-performance technologies that use in-memory processing.
The columnstore indexes were introduced by Microsoft in SQL Server 2012 in order to provide important improvements in terms of performance for data warehouse-style queries.
Microsoft declared that for some queries, the columnstore indexes can show improvements of up to 1000% (10 times). Although in SQL Server 2012 this feature was available there was a restriction where the underlying table has to be read-only. In SQL Server 2014 this restriction has been eliminated.
The new columnstore index gives you the possibility to update the underlying table without dropping the columnstore index like you were forced to do in previous versions. Also, the columnstore index in SQL Server 2014 has to use all columns from the table and cannot be combined with another index.
-
-
- Clustered columnstore indexes
-
By using clustered columnstore indexes you will improve your data compression the performance of your query for warehousing workloads that mainly target bulk load operations and queries that are read-only.
Because the clustered columnstore index can be updated, then the workload can perform more delete, update and insert operations. For more detailed information please review the following links about Columnstore Indexes and how to use Clustered Columnstore Indexes.
-
-
- SHOWPLAN
-
SHOWPLAN will display information related to columnstore indexes. There are two properties, EstimatedExecutionMode and ActualExecutionMode which can have one of two values: Batch or Row.
The Storage property can also have one of two values: RowStore or ColumnStore.
-
-
-
Archival data compression
-
-
The ALTER INDEX command ran with REBUILD has a new COLUMNSTORE_ARCHIVE compression option for your data. By specifying this option you will further compress the partitions of a columnstore index that you will also specify in the query.
This option should be used for archival or other cases in which you require a small storage size for your data and you cannot afford to add more time for storage and retrieval.
These would be the most important features that were introduced and/or improved upon in SQL Server 2014.
But, there are also a few other areas where SQL Server 2014 brings a few changes.
T-SQL Enhancements in SQL Server 2014
In terms of enhancements for T-SQL, SQL Server 2014 edition makes a few changes to the following:
-
-
- Inline specification of CLUSTERED and NONCLUSTERED indexes
-
This type of inline specification of CLUSTERED and NONCLUSTERED indexes is now available for tables that are disk-based. By creating a table with an inline index is the same as creating a table which is followed by a CREATE INDEX statement.
-
-
- SELECT INTO statement
-
This statement has been improved and now it can be run in parallel, but the database compatibility level must be at least 110.
Security improvements in SQL Server 2014
There have also been some changes in terms of security permissions for SQL Server 2014.
-
-
- CONNECT ANY DATABASE permission
-
This is new server-level permission. You should grant this permission to a user who must have the ability to connect to all of the databases that currently exist and to all databases that will be created in the future. This does not include other permissions, just the ability to connect to any database in the system.
You can combine this permission with the SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow auditors to view all of your data on all databases or all database states on your SQL Server 2014 instance.
-
-
- IMPERSONATE ANY LOGIN permission
-
This permission is also new server-level permission and when it is granted it allows a process from the middle-tier to impersonate the account of the client that is trying to connect to it, as it is trying to connect to the database.
If this permission is denied, a high privileged login can be stopped from impersonating any other logins.
As an example, login with CONTROL SERVER permission can be disallowed from impersonating other logins.
-
-
- SELECT ALL USER SECURABLES permission
-
Another new server-level permission, which when granted allows a login, for example, an auditor, to view data in all of the databases that the user can connect to.
Now that we have gone over the most important changes and additions to SQL Server 2014, there comes a question: Should I upgrade?
Should you upgrade?
Well, moving to a new release is most of the time a difficult decision for organizations. But in spite of this, SQL Server 2014’s new engine with In-Memory OLTP and its promise to have a major improvement on your application’s performance will offer a very compelling reason to make the step and upgrade for the customers which use SQL Server to support applications that utilize OLTP.
A great way to preview the type of performance improvement you might benefit from SQL Server 2014’s new OLTP capability is to download the Evolution Edition and use the AMR tool and analyze the workload in a production similar environment.
The AMR tool has support for collecting data from instances of 2008 and later versions. This is how you can get a good idea of the improvements you can get and also the changes you might need in order to implement this.
In-Memory OLTP is a strong point of SQL Server 2014 and it promises to be a big boost in database application performance, which is why you should really give it a good thought.