Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

What's new in SQL Server 2014? Is it worth the upgrade?

MSSQLTips author Daniel Farina By:   |   Read Comments (10)   |   Related Tips: More > SQL Server 2014
Problem

There is a great deal of information about the upcoming SQL Server 2014 version, especially about Hekaton which is the In-Memory OLTP engine feature of SQL Server 2014. But is Hekaton the only new feature in SQL Server 2014?  In this tip I will guide you through the new features that make SQL Server 2014 so outstanding.

Solution

Sometimes software vendors launch new versions of their products with minimal improvements with the sole purpose of maintaining visibility amongst competitors. Since migration of databases is a time consuming and expensive task, we as database professionals must decide what is best for our customers. That forces us to do some research about the new version's features in order to make the most accurate decision.

Let's take a look at the new features and improvements with SQL Server 2014.

SQL Server In-Memory OLTP Overview

SQL Server 2014 includes an In-Memory OLTP engine code named Hekaton. This engine provides a lock and latch free environment for OLTP workloads. It is fully integrated into SQL Server and accessed using standard T-SQL. Contrary to other products in the market, Hekaton is not a separate system, it is part of the SQL Server Database Engine.  Hekaton enables you to use both disk based tables and Memory-Optimized Tables together in the same queries and stored procedures.

Memory-Optimized tables can be defined as durable, so data persists on server restart and with schema only duration to preserve table definition alone, useful for ETL transformations and data staging.

Furthermore, Hekaton introduces Natively Compiled Stored procedures which are Transact-SQL Stored Procedures compiled to native code, to interact with Memory-Optimized Tables even more efficiently.

You can read more about Memory-Optimized tables and Natively Compiled Stored Procedures in my previous tips.

SQL Server 2014 Cloud Computing Enhancements

Since this version of SQL Server was designed with the premise of being a platform for a Hybrid Cloud it has some new and exciting features.

An on-premise SQL Server can have databases in which its data and log files are stored on Windows Azure Storage. This means that you can move your storage into the cloud while keeping all the transaction processing on your local server. Furthermore you can enable Transparent Data Encryption on databases while keeping the encryption key on the local server for added security.

You can deploy a SQL Server Database to a Windows Azure Virtual Machine with a few clicks with the SQL Server Management Studio Deploy a SQL Server Database to a Windows Azure Virtual Machine Wizard.

This release also includes the possibility to Backup and Restore to/from a URL directly with SQL Server Management Studio.

SQL Server AlwaysOn Improvements

As I told you before, this version of SQL Server was conceived as a platform for a Hybrid Cloud. So the Engineers at Microsoft had the wonderful idea of allowing Hybrid High Availability solutions like the creation of Azure replicas for Availability Groups with a simple Add Azure Replica Wizard to guide you through the process.

Also readable secondary replicas now remain available for reading on cluster quorum loss or when a primary replica is down.

Furthermore, the maximum number of replicas has been increased from 4 to 8.

SQL Server 2014 includes the possibility to use Cluster Shared Volumes as cluster shared disks in Windows Server 2012 and above on Failover Cluster Instances.

Even new Dynamic Management Views have been added to increase ease of troubleshooting. You can read about them on my previous tip "Understanding Dynamic Management Views in SQL Server 2014".

SQL Server Performance Enhancements

Several features have been added regarding performance.  Please see the following items below.

SQL Server Transactions with Delayed Durability

In order to reduce latency, transactions can be defined as delayed durable, which means that transaction returns control to the client before the Transaction Log record is written to disk.

This can be defined at the database level, COMMIT level, or ATOMIC block level in Natively Compiled Stored Procedures. Also the following Stored Procedure sys.sp_flush_log is included to flush the Transaction Log to disk in order to make previously committed transactions durable with delayed durability.

Here is a sample code.

// Set DB option to allow transactions with delayed durability.
USE [master]
GO
ALTER DATABASE [TestDB] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO
//---------------------------------------------------------------
BEGIN TRANSACTION
UPDATE dbo.SomeTable 
 SET SomeColumn = @SomeData
WHERE SomePk = @SomeID
// set current transaction with delayed durability
COMMIT TRANSACTION WITH(DELAYED_DURABILITY = ON )
// Flush transaction log to disk
EXEC sys.sp_flush_log 

SQL Server Query Optimizer

SQL Server 2014 substantially improved the component of the engine that creates and optimizes query plans.  Stay tuned for more.

SQL Server Table and Index Operations

In SQL Server 2014 single partitions can be rebuilt and additional partition switching and index rebuild operations can be performed while the table is online.

Furthermore the ability to manage lock priority of online operations for tables and indexes has been added by allowing you to use WAIT_AT_LOW_PRIORITY option.  This option enables you to specify operation locks time maximum duration and abort conditions.  Here is some additional information:

Argument

Description

MAX_DURATION

Is the wait time in minutes the online operation will wait at low priority

ABORT_AFTER_WAIT

These are the actions to be taken by the online operation when it is blocked beyond MAX_DURATION value.

NONE: Continue waiting for the lock with normal priority.

SELF: Leaves current operation without taking any action.

BLOCKERS: Kills transactions that block the online operation.

Here is a sample code.

ALTER INDEX ALL ON SomeTable REBUILD
 WITH (ONLINE = ON (
   WAIT_AT_LOW_PRIORITY (
    MAX_DURATION = 4 
    MINUTES ABORT_AFTER_WAIT = BLOCKERS
      ) 
    )
  ) 

SQL Server 2014 Incremental Option for CREATE STATISTICS

SQL Server 2014 permits statistics creation per partition by setting the INCREMENTAL option to ON in the CREATE STATISTICS statement. Here is an example.

CREATE STATISTICS SomeStatistic 
ON dbo.SomeTable
 ( 
    SomeField
 ) 
WITH FULLSCAN, INCREMENTAL =  ON;
GO

SQL Server 2014 Buffer Pool Extension

This feature enables SQL Server to be configured to use a SSD disk as an extension for the Database Buffer Pool in order to reduce latency.

SQL Server 2014 Resource Governor

With the SQL Server 2014 release, we can set constraints on the physical IO operations.  The MAX_OUTSTANDING_IO_PER_VOLUME argument has been added to the ALTER RESOURCE GOVERNOR statement allowing us to set the maximum outstanding I/O operations per disk volume giving us the ability to tune the SQL Server instance IO according to the disk IO characteristics.

Also we can set the disk IO thresholds for disk volumes on Resource Pools with these two new settings: MAX_IOPS_PER_VOLUME and MIN_IOPS_PER_VOLUME.  These options set the maximum and minimum IO operations per second respectively.

SQL Server 2014 Columnstore Index Improvements

SQL Server 2014 has added updatable Clustered Columnstore Indexes.

Another feature is the capability to compress Columnstore indexes, both clustered and nonclustered even more. For this, two arguments have been added to the REBUILD option of the ALTER INDEX statement in order to handle Columnstore Indexes compression:

Argument

Description

COLUMNSTORE

Default Columnstore indexes Compression.

COLUMNSTORE_ARCHIVE

Compress Columnstore indexes even more.

Also improvements in Batch processing mode have been made.  Stay tuned for more information.

SQL Server 2014 Security Enhancements

SQL Server 2014 includes the following security improvements: Backup Encryption and Permissions.

SQL Server 2014 Backup Encryption

Now SQL Server backup data can be encrypted during the backup creation with several new encryption algorithms like AES 128, AES 192, AES 256 and 3DES. I will cover this topic on a upcoming tip.

Permission Changes in SQL Server 2014

The following permission has been added in SQL Server 2014:

Permission

Description

CONNECT ANY DATABASE

Grants Connect permission for users on Databases that may be created in future.

IMPERSONATE ANY LOGIN

Allows or block Login impersonation. Is useful to block impersonation from high privileged logins.

SELECT ALL USER SECURABLES

Server level permission. Allows logins to perform SELECT Statements in all databases that the login has CONNECT permission.

ALTER ANY DATABASE EVENT SESSION

Database level permission

Next Steps


Last Update: 1/23/2014


About the author
MSSQLTips author Daniel Farina
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, January 23, 2014 - 2:30:31 PM - Carl Perkins Read The Tip

I like your SQL Server 2014 pointers.  Well done.


Thursday, January 23, 2014 - 6:06:58 PM - Daniel Farina Read The Tip

Hi Carl

Thank you very much for your feedback! You made my day!
I'm very glad you enjoyed my article!

Best regards!


Monday, January 27, 2014 - 11:23:05 AM - Kevin Lobo Read The Tip

Hi Daniel,

Really well written - concise and to the point for anyone debating this upgrade. Thanks.


Tuesday, January 28, 2014 - 5:19:50 PM - Daniel Farina Read The Tip

Hi Kevin!

Thank you very much for reading!

Best Regards


Wednesday, January 29, 2014 - 11:12:53 AM - Tim Read The Tip

Good stuff. Thanks man. Informative :)


Friday, January 31, 2014 - 7:10:18 AM - poshendra sarthi Read The Tip

Your article is very nice and helpful for me. thanks for nice article.


Sunday, February 02, 2014 - 1:11:53 PM - Daniel Farina Read The Tip

Hi and thanks for your support!

I'm glad you enjoyed it.

 

Best Regards!


Sunday, February 23, 2014 - 5:19:04 PM - manu Read The Tip

Nice compilation. Thanks Daniel!


Thursday, March 27, 2014 - 2:54:00 AM - Thulasi Read The Tip

It's really good article and worth to read. Thanks Daniel.


Tuesday, April 01, 2014 - 9:37:31 AM - Neil Read The Tip

Good succinct article.

Do you know of any links to any T-SQL improvements or new features in SQL Server 2014.

I can't believe how difficult it is to locate such information



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.