Top 9 Features of SQL Server 2016

SQL Server - SeniorDBA

Technology is always improving. Microsoft SQL Server 2016 includes many new and improved features that will provide users with greater availability, better performance, and more security. The Microsoft IT Enterprise Services BI team has identified their top eight features and enhancements:

  • One programming surface across all editions – With November’s SQL Server 2016 Service Pack 1 (SP1), you can switch from Express to Standard, or Standard to Enterprise, and you don’t have to rework code to take advantage of additional features.
  • In-Memory OLTP helps ESBI meet their users’ business requirements for increased agility.
  • Columnstore Indexes reduce the amount of time it takes to run and render SRSS reporting data.
  • Temporal data reduces the amount of support tickets received from the field due to inaccurate data.
  • Row-Level Security provides a more reliable and standardized method to easily control which users can access data.
  • Dynamic Data Masking helps limit exposure of sensitive data, preventing users who should not have access to the data from viewing it.
  • Query Store provides better insight into the performance differences caused by changes in query plans.
  • Active Query Statistics allows a view of active query execution plans and helps identify and fix blocking issues while queries are running.
  • SQL Stretch Database helps improve performance to frequently used data while preserving access to archived data.

You can read additional details of these features here.

Microsoft Announces SQL Server 2016 SP1

SQL Server - SeniorDBA

Microsoft has announced the SQL Server 2016 Service Pack 1 (SP1), is now available. With this service pack Microsoft has make some key innovations more accessible to organizations across all SQL Server editions. Developers and application partners can now have similar features across all editions.

The Microsoft blog posting lists capabilities in SQL Server 2016 SP1:
  • Faster transaction performance from In-memory OLTP, faster query performance from In-memory ColumnStore, and the ability to combine the two for real-time Hybrid Transactional and Analytical Processing, also known as Operational Analytics.
  • Data warehousing or data mart performance features such as partitioning, compression, change data capture, database snapshot, and the ability to query across structured and unstructured data with a single node of PolyBase.
  • The innovative security feature Always Encrypted for encryption at rest and in motion, as well as fine-grained auditing which captures more detailed audit information for your compliance reporting needs. In addition, row-level security and dynamic data masking are being made available to Express edition for the first time.

Changes In The Way Databases Use Memory



The way database systems use server memory is changing, with disk-based data stores being slowly replaced by memory-based data stores. With memory prices being more affordable and solid-state drives still being the limiting factor in disk-based performance, vendors are seeing short-term gains in operational databases that can be loaded into memory.

There is an article by Maria Zakourdaev that explores this subject:

According to the Gartner (the research company that provides independent technology reports) by the end of 2015 all enterprise DBMS will use memory optimizations and most of this transformation will happen this year. 

The Big Vendors, Microsoft, Oracle, SAP and IBM are still Leaders on the field, all others are way behind. I believe that the true Leaders are always aware of all the trends in the database world, they adjust to them and adopt the new ideas, which sometimes means buying emerging breakthrough solutions.

Oracle have understood finally that having several database products is not too easy for their customers. They have announced an In-Memory Database option for Oracle 12c, it should be available this July. As opposed to TimesTen, which is a separate caching product, an in-Memory option will be totally integrated into the 12c version. It will use the in-memory column-store format which is usually being used for data analysis but they say that this feature will speed up also OLTP workloads. They will keep the data simultaneously in two formats – in the old row-based and in the in-memory column-based. Both formats will be transactionally consistent.

SAP has a High performance Analytic Appliance (HANA) DBMS since 2011. It supports 2 formats and stores in memory both row-based tabular data and column-based tables. Naturally, tabular orientation is recommended more for OLTP environments and column-store for OLAP-like queries. SAP HANA can be scaled out by adding nodes, sharing the same storage. In such case the master node will store row-based data and statistics on the rest of the data.

IBM DB2 10.5 has a BLU acceleration. This is a memory-resident index which helps to find data, stored in columnar form on disk. There is no scale out solution yet but they claim that it will come soon. IBM BLU is not really for OLTP environments, as opposite to SQL Server: for writes IBM recommends “to commit 10,000 rows at a time”. Transactions are using locks to make changes to the data and latches to access shared structures.

Microsoft SQL 2014 has a new query engine “in-memory OLTP” (“Hekaton”)  where the row-based data resides completely in memory keeping a copy of the data on the disk for durability purposes. Indexes on memory-resident tables live only in memory. Queries can span all 3 existing query engines: relational traditional query engine where the data sits on disk, column-store query engine, where data is using both memory and disk and this 3rd new query engine that manages memory resident data. Hekaton tables are not using locks and latches for concurrency and locking management, it’s using a combination of timestamps and row versioning.

You should read the entire article to get a better understanding of this subject. You can also read more about the Microsoft SQL Server solution in SQL Server 2014 here.

Video of using in-memory OLTP in SQL Server 2014.

Watch David DeWitt, a technical fellow at Microsoft, describe how in-memory OLTP is implemented, through a technical overview of our design choices that enable applications to achieve an up-to 30X performance boost compared to traditional disk-based databases. This talk covers why traditional approaches are insufficient to achieve the performance gains possible through a combination of latch and lock free OLTP transactions, compiled stored procedures and optimizations in our query engine.


David DeWitt is a technical fellow at Microsoft and currently manages the Jim Gray Systems Lab.


Overview of in-memory OLTP as discussed at TechEd 2013 by Jos de Bruijn:

10 Great Features in SQL Server 2014

The latest version of SQL Server is targeted at becoming the leading database solution from Microsoft. Microsoft has focused on many of the OLTP performance problems from many angles: slow disk performance issues with in-memory tables, slow log performance issues with delayed durability, and maintenance concurrency issues with lock priority. This new SQL Server release stops excessive I/O with new Resource Governor I/O controls, addresses some memory issues with an SSD buffer pool extension, and availability issues with the Azure cloud integration.


1. In-Memory OLTP

For OLTP, the most exciting new feature in SQL Server 2014 is In-Memory OLTP, which allows you to move individual tables to special in-memory structures. The performance boost is reported as big as 30x. There are a number of limitations and special requirements for these tables, so they won’t work under every circumstance. This is better than other in-memory solutions that require the entire database to be placed in memory. You can get more performance by converting existing stored procedures into in-memory procedures, too. You can read a whitepaper from Microsoft Research here.

2. Backup to Azure

Quite often small shops without a full-time Database Administrator don’t discover that their backups aren’t being handled properly until it’s too late. The Managed Backup feature automatically backs up your database (or your instance) based on your defined recovery interval and workload patterns. When the system determines the data has changed significantly enough, it takes a backup to Azure. This feature only works with Azure blob storage. But since your backups are already offsite, there’s no need to worry about tapes. This may not solve all your incident recovery needs or be 100% effective, but for those small shops it is a better solution than doing nothing.

3. SQL Server Data Files in Azure

Data Files in Azure is just what it sounds like: Your database runs locally in your data center, while the database files themselves live in an Azure blob container. This can offer advantages in DR and migration. But depending on the size of the database and its workload, the potential performance cost of pushing the data for every transaction across the Internet could be prohibitive. A better use of this feature may be to store the data files in an Azure VM in the same data center. This can also get you around the current limitation of having only 16 mounted disks in an Azure VM.

4. Updateable columnstore indexes

Updatable columnstore indexes in SQL Server 2014 bring a dramatic boost to data warehouse performance.  The caveat is that you must have a clustered columnstore index on the table. Non-clustered columnstores aren’t supported.

5. Resource Governor for I/O

Disk I/O is typically the most constrained resource of a database system, and often a large or rogue query will take up more precious I/O resources than you can afford. Microsoft has finally given us some control over runaway I/O. With Resource Governor for I/O, you can now put queries into their own resource pool and limit the amount of I/O per volume they’re allowed.

MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME set the minimum and maximum reads or writes per second allowed by a process in a disk volume.

  • MIN_IOPS_PER_VOLUME reserves a minimum number of I/O transactions per second
  • MAX_IOPS_PER_VOLUME provides a maximum number of I/O transactions per second.

This maximum doesn’t limit the number of I/O operations a query can perform, but merely keeps it from monopolizing a disk. This way your large queries can still run, but other things will run as well.

A good use of I/O control is to reserve some IOPS for administrators to be able to investigate issues when the disks are overloaded.

6. Delayed durability

In SQL Server, changes to data are written to the log first. This is called write ahead logging (WAL). Control isn’t returned to the application until the log record has been written to disk (a process referred to as “hardening”). Delayed durability allows you to return control back to the application before the log is hardened. This can speed up transactions if you have issues with log performance. Nothing is free, though, and here you sacrifice recoverability. Should the database go down before the log is committed to disk, then you lose those transactions forever. It may be worth the risk if your log performance is severely degrading application response times.

7. SSD buffer pool extension

Creating a buffer pool extension for SQL Server 2014 is like being able to define a different page file in Windows. As data pages move into memory, they begin to fill up the buffer pool. If the buffer pool fills up, the less frequently used pages will be paged to disk. Then when they’re needed again, they’ll be swapped with something else in the buffer pool and moved back into memory. The buffer pool extension option allows you to define an SSD as a buffer file location. Because SSD is so much faster than spinning disk, the paging is considerably quicker, which increases performance dramatically in some cases. You can define a buffer pool extension file up to 32 times the size of your memory.

8. Backup Encryption

One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.

9. Lock priority of online operations

You can now specify a lock priority for online re-indexing. In previous versions of SQL Server, long-running queries could block re-indexing operations, chewing up your maintenance window while your re-index sits waiting. In SQL Server 2014, you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over.

10. AlwaysOn Enhancements

Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.


Transact-SQL Language Enhancements in SQL Server 2014

With the latest version of SQL Server, named SQL Server 2014, there a still a few Transact-SQL language enhancements you should be aware of when using the new product.

  • Inline specification of CLUSTERED and NONCLUSTERED – Inline specification of CLUSTERED and NONCLUSTERED indexes is now allowed for disk-based tables. Creating a table with inline indexes is equivalent to issuing a create table followed by corresponding CREATE INDEX statements. Included columns and filter conditions are not supported with inline indexes.
  • SELECT … INTO – The SELECT … INTO statement is improved and can now operate in parallel. The database compatibility level must be at least 110.
  • Transact-SQL Enhancements for In-Memory OLTP
    • The following Transact-SQL statements are modified to support In-Memory OLTP:
      • ALTER DATABASE File and Filegroup Options (Transact-SQL) (added MEMORY_OPTIMIZED_DATA)
      • CREATE PROCEDURE (Transact-SQL)
      • CREATE TABLE (SQL Server)
      • CREATE TYPE (Transact-SQL)
      • DECLARE @local_variable (Transact-SQL)