SQL Server 2016 Release Notes

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

This article describes limitations and issues with SQL Server 2016 releases, including service packs. For information on what's new, see What's New in SQL Server 2016.

Download from Evaluation Center Download SQL Server 2016 from the Evaluation Center

Azure Virtual Machine small Have an Azure account? Then go Here to spin up a Virtual Machine with SQL Server 2016 SP1 already installed.

Download SSMS To get the latest version of SQL Server Management Studio, see Download SQL Server Management Studio (SSMS).

SQL Server 2016 Service Pack 1 (SP1)

info_tip SQL Server 2016 SP1 includes all the fixes up to SQL Server 2016 RTM CU3 including Security Update MS16-136. It contains a roll-up of solutions provided in SQL Server 2016 cumulative updates up to and includes the latest Cumulative Update - CU3 and Security Update MS16-136 released on November 8th, 2016.

The following features are available in the Standard, Web, Express, and Local DB editions of SQL Server SP1 (except as noted):

  • Always encrypted
  • Changed data capture (not available in Express)
  • Columnstore
  • Compression
  • Dynamic data masking
  • Fine grain auditing
  • In Memory OLTP (not availabe in Local DB)
  • Multiple filestream containers (not available in Local DB)
  • Partitioning
  • Polybase
  • Row level security

The following table summarizes key improvements provided in SQL Server 2016 SP1.

Feature Description For more information
Bulk insert into heaps with auto TABLOCK under TF 715 Trace Flag 715 enables table lock for bulk load operations into heap with no non-clustered indexes. Migrating SAP workloads to SQL Server just got 2.5x faster
CREATE OR ALTER Deploy objects such as Stored Procedures, Triggers, User-Defined Functions, and Views. SQL Server Database Engine Blog
DROP TABLE support for replication DROP TABLE DDL support for replication to allow replication articles to be dropped. KB 3170123
Filestream RsFx Driver signing The Filestream RsFx driver is signed and certified using Windows Hardware Developer Center Dashboard portal (Dev Portal) allowing SQL Server 2016 SP1 Filestream RsFx driver to be installed on Windows Server 2016/Windows 10 without any issue. Migrating SAP workloads to SQL Server just got 2.5x faster
LPIM to SQL service account - programmatic identification Allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time. Developers Choice: Programmatically identify LPIM and IFI privileges in SQL Server
Manual Change Tracking Cleanup New stored procedure cleans the change tracking internal table on demand. KB 3173157
Parallel INSERT..SELECT Changes for Local temp tables New Parallel INSERT in INSERT..SELECT operations. SQL Server Customer Advisory Team
Showplan XML Extended diagnostics including grant warning and maximum memory enabled for a query, enabled trace flags, and also surfaces other diagnostic information. KB 3190761
Storage class memory Boost the transaction processing using Storage Class Memory in Windows Server 2016, resulting in the ability to accelerate transaction commit times by orders of magnitude. SQL Server Database Engine Blog
USE HINT Use the query option, OPTION(USE HINT('<option>')) to alter query optimizer behavior using supported query level hints. Unlike QUERYTRACEON, the USE HINT option does not require sysadmin privileges. Developers Choice: USE HINT query hints
XEvent additions New XEvents and Perfmon diagnostics capabilities improve latency troubleshooting. Extended Events

In addition, note the following fixes:

  • Based on feedback from DBAs and SQL community, starting SQL 2016 SP1, the Hekaton logging messages are reduced to minimal.
  • Review new Trace flags.
  • The full versions of the WideWorldImporters sample databases now work with Standard Edition and Express Edition, starting SQL Server 2016 SP1 and are available on Github. No changes are needed in the sample. The database backups created at RTM for Enterprise edition work with Standard and Express in SP1.

The SQL Server 2016 SP1 installation may require reboot post installation. As a best practice, we recommend to plan and perform a reboot following the installation of SQL Server 2016 SP1.

Download pages and more information

SQL Server 2016 Release - General Availability (GA)

repl_icon_warn Install Patch Requirement (GA)

Issue and customer impact: Microsoft has identified a problem that affects the Microsoft VC++ 2013 Runtime binaries that are installed as a prerequisite by SQL Server 2016. An update is available to fix this problem. If this update to the VC runtime binaries is not installed, SQL Server 2016 may experience stability issues in certain scenarios. Before you in stall SQL Server 2016, check to see if the computer needs the patch described in KB 3164398. The patch is also included in Cumulative Update Package 1 (CU1) for SQL Server 2016 RTM.

Resolution: Use one of the following solutions:

  • Install KB 3138367 - Update for Visual C++ 2013 and Visual C++ Redistributable Package. The KB is the preferred resolution. You can install this before or after you install SQL Server 2016.

    If SQL Server 2016 is already installed, do the following steps in order:

    1. Download the appropriate vcredist_*exe.
    2. Stop the SQL Server service for all instances of the database engine.
    3. Install KB 3138367.
    4. Reboot the computer.
    • Install KB 3164398 - Critical Update for SQL Server 2016 MSVCRT prerequisites.

      If you use KB 3164398, you can install during SQL Server installation, through Microsoft Update, or from Microsoft Download Center.

      • During SQL Server 2016 Installation: If the computer running SQL Server setup has internet access, SQL Server setup checks for the update as part of the overall SQL Server installation. If you accept the update, setup downloads and update the binaries during installation.

      • Microsoft Update: The update is available from Microsoft Update as a critical non-security SQL Server 2016 update. Installing through Microsoft update, after SQL Server 2016 requires the server to be restarted following the update.

      • Download Center: Finally, the update is available from the Microsoft Download Center. You can download the software for the update and install it on servers after they have SQL Server 2016.

Stretch Database

Problem with a specific character in a database or table name

Issue and customer impact: Attempting to enable Stretch Database on a database or a table fails with an error. The issue occurs when the name of the object includes a character that's treated as a different character when converted from lower case to upper case. An example of a character that causes this issue is the character "ƒ" (created by typing ALT+159).

Workaround: If you want to enable Stretch Database on the database or the table, the only option is to rename the object and remove the problem character.

Problem with an index that uses the INCLUDE keyword

Issue and customer impact: Attempting to enable Stretch Database on a table that has an index that uses the INCLUDE keyword to include additional columns in the index fails with an error.

Workaround: Drop the index that uses the INCLUDE keyword, enable Stretch Database on the table, then recreate the index. If you do this, be sure to follow your organization's maintenance practices and policies to ensure minimal or no impact to users of the affected table.

Query Store

Problem with automatic data cleanup on editions other than Enterprise and Developer

Issue and customer impact: Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, if data is not purged manually, space used by the Query Store will grow over time until configured limit is reached. If not mitigated, this issue will also fill up disk space allocated for the error logs, as every attempt to execute cleanup produces a dump file. Cleanup activation period depends on the workload frequency, but it is no longer than 15 min.

Workaround: If you plan to use Query Store on editions other than Enterprise and Developer, you need to explicitly turn off cleanup policies. It can be done either from SQL Server Management Studio (Database Properties page) or via Transact-SQL script:

ALTER DATABASE <database name> SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 0), SIZE_BASED_CLEANUP_MODE = OFF)

Additionally, consider manual cleanup options to prevent Query Store from transitioning to read-only mode. For example, run the following query to periodically clean entire data space:

ALTER DATABASE <database name> SET QUERY_STORE CLEAR

Also, execute the following Query Store stored procedures periodically to clean runtime statistics, specific queries or plans:

  • sp_query_store_reset_exec_stats

  • sp_query_store_remove_plan

  • sp_query_store_remove_query

Product Documentation (GA)

Issue and customer impact: A downloadable version of the SQL Server 2016 documentation is not yet available. When you use Help Library Manager to attempt to Install content from online, you see the SQL Server 2012 and SQL Server 2014 documentation but there are no options for SQL Server 2016 documentation.

Workaround: Use one of the following work-arounds:

Manage Help Settings for SQL Server

  • Use the option Choose online or local help and configure help for "I want to use online help".

  • Use the option Install content from online and download the SQL Server 2014 Content.

    F1 Help: By design when you press F1 in SQL Server Management Studio, the online version of the F1 Help article is displayed in the browser. The issues is browser-based help even when you have configured and installed local Help.

Updating content:
In SQL Server Management Studio and Visual Studio, the Help Viewer application may freeze (hang) during the process of adding the documentation. To resolve this issue, complete the following steps. For more information about this issue, see Visual Studio Help Viewer freezes.

  • Open the %LOCALAPPDATA%\Microsoft\HelpViewer2.2\HlpViewer_SSMS16_en-US.settings | HlpViewer_VisualStudio14_en-US.settings file in Notepad and change the date in the following code to some date in the future.
     Cache LastRefreshed="12/31/2017 00:00:00"    

Additional Information

info_tip Get Help

MS_Logo_X-Small