New Features in SQL Server 2014

There are a number of new features available in SQL Server 2014 but this article will go into more detail regarding the less popular ones.  The well publicised In Memory OLTP Engine and the ability to update a column store index are probably the most used features and provide lots of performance benefits.  However, there are lots of other blogs out there that will give you a breakdown of advantages, disadvantages, useful techniques, etc.

SQL Server 2014 did not go through such a drastic makeover as SQL Server 2012.  The environment has been further stabilised and grown to give users more functionality and a better handle on standard processes.  The below additions to SQL Server 2014 will be discussed:

– Database Backups
– Resource Governor for I/O
– SSD Buffer Pool Extension
– Incremental statistics

NOTE
This Article DOES NOT explain how to implement a feature.  The ‘Further Reading’ section provides a website that
provides more detailed information on each subject.

Database Backups

• Encryption.
  • SQL Server now supports built-in database encryption – no need for third party tools.
  • Can use either a certificate or asymmetric key to backup a database.
  • Supported encryption algorithms are for Advanced Encryption Standard (AES):
    • AES 128-bit.
    • AES 192-bit.
    • AES 256-bit.
    • Triple DES (3DES).
• Managed backup to Azure Blob Storage.
  • Automatically backs up a database (or an instance) based on:
    • Defined recovery intervals.
    • Workload patterns.
  • When the system determines that data has changed enough, a backup is taken on Azure.
  • Azure backup integration is fully built into SQL Management Studio (SSMS).
  • Managed backups are ONLY compatible with blob storage and dependant on Internet access to take and restore backups.

Resource Governor for I/O

Disk I/O (Input/Output) can cause a lot of problems in a database system, especially when running large or problematic queries.  SQL Server 2014 (more specifically, Resource Governor) enables more control over I/O:

• Queries put into their own resource pool – limiting I/O per volume.
• Minimum/Maximum reads or writes (per second) in a disk volume are controlled by:
  • MIN_IOPS_PER_VOLUME
    • Reserves a minimum number of I/O transactions per second
  • MAX_IOPS_PER_VOLUME
    • Sets a maximum but more importantly, prevents the hard disk from being monopolised.
    • Large queries will run but enable other processes to function in parallel.
• Good I/O control will reserve Input/Output Operations Per Second (IOPS), in order for administrators to investigate overloaded or bad performing disks.

SSD Buffer Pool Extension

A buffer pool extension for SQL Server 2014 is similar to defining a different page file for Windows.  As various data pages move into memory, the buffer pool begins to fill up.  Once it is full, less frequently used pages are added to disk.  When they are needed again, the pages are swapped with another page in the buffer pool and allocated back to memory.

• Buffer pool extension allows a user to define a Solid State Drive (SSD) as a buffer file location.
  • SSD is much faster than a standard spinning disk, therefore, increasing performance considerably.
  • Buffer pool extension file:
    • Up to 32 times the size of physical memory.
    • Increased random I/O throughput.
    • Increased transaction throughput.
    • Caching that can take advantage of low-cost memory drives.
  • No risk of data loss. Buffer Pool Extension (BPE) only deals with clean pages.
  • SQL syntax is very simple to use and maintain.

Incremental statistics

Typically, statistics (stats) have been pretty useless and costly in SQL Server.  Whenever stats need to be rebuilt, the whole table requires updating.  For example, a table with 100 million records but only 5 millions updates would need a statistics update for the whole 100 million rows.

Incremental statistics can alleviate the above example, as well as offer some other advantages:

• Update changed rows and merge them with existing rows
  • Can result in drastically improved query performance.
  • Used in conjunction with table partitioning.
  • Final statistics object is updated – not re-created.
• Percentage of data changes to trigger an automatic update of statistics now works at the partition level.
  • Now only 20% of rows changed per partition are required.
• Default setting for incremental statistics is OFF.

Conclusion

There are many other new/improved features in SQL Server 2014, that have not been discussed in this blog.  The 5 features included here are the ones which could help increase SQL Server performance, efficiency and reliability.  You should think about these elements before building a data warehouse or Analysis Service cube, as it could save a lot of time and money further down the line.

I would encourage all interested parties to read up on the other exciting additions to SQL Server 2014.  Simply typing any of the below keywords into Google will bring up a number of related articles.

– Updateable Columnstore Indexes
– In-Memory OLTP Engine
– AlwaysOn Enhancements
– Power BI for Office 365 Integration
– Power View for Multidimensional Models
– Delayed durability

For any questions related to this blog, please contact Callum Green at callum.green@adatis.co.uk.

Further Reading

For further information on the new features (and associated functionality), navigate to the below website links:

1. Database Backups
   Encryption Types – http://msdn.microsoft.com/en-GB/library/ms345262.aspx
   Managed Azure backups – http://msdn.microsoft.com/en-gb/library/dn449491.aspx
2. Resource Governor for I/O
   Configuration and SQL Server 2014 implementation examples – http://www.databasejournal.com/features/mssql/restricting-io-using-sql-server-2014-resource-governor.html
3. SSD Buffer Pool
   Benefits, features and SQL Syntax for Buffer Pool Extension – http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/25/buffer-pool-extension-to-ssds-in-sql-server-2014.aspx
4. Incremental Statistics
   Improvements made to statistics and using alongside table partitioning – http://sqlperformance.com/2014/02/sql-statistics/2014-incremental-statistics
   Scenarios when Incremental Statistics are not available or possible (half way down the page)  – http://msdn.microsoft.com/en-us/library/ms188038.aspx