New Features in SQL Server 2016 Service Pack 1

By: Aaron Bertrand   |   Updated: 2016-11-16   |   Comments   |   Related: > SQL Server 2016

Problem

Service Pack 1 for SQL Server 2016 was released today, and if you are running Standard or Express Edition, you are running out of excuses to upgrade. As I explained in a blog post today, Microsoft has announced at the Connect(); // 2016 conference (and on the SQL Server Team Blog) that they have opened up many Enterprise features to Standard, Web, Express, and even LocalDB. But what new features have been added?

Solution

I'm going to touch on several new features that are available within all editions of Service Pack 1, because there are some valuable things here, and they may get lost in all the fanfare about so many Enterprise features being enabled for everyone.

CREATE OR ALTER

In SQL Server 2016, they delivered new DROP IF EXISTS syntax, which allows you to run a DROP command without checking for the existence of the object first. In SP1, we get CREATE OR ALTER for modules - this is much more useful, as it allows you to deploy the script for a view, procedure, function, or trigger without needing to know if it already exists, and without worrying about losing permissions or dependencies if your currents logic issues a DROP and then a CREATE. This batch will be able to run multiple times without error (go ahead, hit F5 as much as you like):

CREATE OR ALTER PROCEDURE dbo.TestOne
 AS
 BEGIN
   PRINT N'hi';
 END
 GO

 CREATE OR ALTER FUNCTION dbo.TestTwo(@i int)
 RETURNS int
 AS
 BEGIN
   RETURN (@i+1);
 END
 GO

 CREATE OR ALTER FUNCTION dbo.TestTwo(@i int)
 RETURNS int
 WITH SCHEMABINDING -- can also be used to add/remove SCHEMABINDING
 AS
 BEGIN
   RETURN (@i+1);
 END
 GO

You can't use this approach to change a function type to a different type, for example from a scalar function to an inline table-valued function, just like you couldn't with a regular old ALTER. If you try to change that scalar function to a table-valued function:

CREATE OR ALTER FUNCTION dbo.TestTwo(@i int)
 RETURNS TABLE
 WITH SCHEMABINDING
 AS
   RETURN (SELECT x = @i+1);
 GO

You get this error:

Note that if you have an indexed view, a subsequent CREATE OR ALTER behaves just like a normal ALTER would - it modifies the schema-bound view, as you would expect, which doesn't affect permissions, but does drop indexes.

A new USE HINT query option

This looks like the beginning of a replacement for OPTION (QUERYTRACEON), which requires sysadmin privileges. OPTION (USE HINT('')) does not require sysadmin, and also doesn't make you memorize trace flag numbers (instead, you get to memorize verbose strings :-)). Here is a quick example:

 SELECT [column] 
   FROM dbo.[table] 
   WHERE x = 1
   OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));

Currently there are 9 hints supported (you can see them listed in sys.dm_exec_valid_use_hints), and these are the trace flags they emulate:

DBCC CLONEDATABASE

As an easier way to create an empty database with statistics, this functionality was recently added to SQL Server 2014 SP2 (see posts on this by Melissa Connors and Erin Stellato). There is a new property for DATABASEPROPERTYEX called isClone, and the clone includes CLR, Filestream, FileTable, In-Memory OLTP, and Query Store objects. There are options for NO_STATISTICS and NO_QUERYSTORE, which are self-explanatory; these options do not exist in SQL Server 2014 SP2.

Are Lock Pages in Memory and Instant File Initialization enabled?

We've used all kinds of ways to determine if the current instance's service account has the ability to use Lock Pages in Memory and Instant File Initialization; now there are DMV columns to make it much easier.

SELECT sql_memory_model, sql_memory_model_desc
   FROM sys.dm_os_sys_info;

   /*
     possible values:
     ----   ------------
        1   CONVENTIONAL
        2   LOCK_PAGES
        3   LARGE_PAGES
   */

 SELECT instant_file_initialization_enabled
   FROM sys.dm_server_services;

   /*
     possible values:
     ----
        Y
        N
   */

Manual Change Tracking Cleanup

In some cases, the automatic background cleanup of Change Tracking does not work fast enough, and the growth of the side tables becomes unmanageable. There is a new stored procedure, sys.sp_flush_CT_internal_table_on_demand, which can be called manually or on a schedule. This procedure was already added to SQL Server 2014 SP2 (see KB #3173157), and you should expect that KB article to be updated to reflect that the procedure is now in SQL Server 2016 SP1.

Quieter In-Memory OLTP

SQL Server 2016 RTM started logging additional information to the SQL error log to help support troubleshooting In-Memory OLTP; however, in some cases this was flooding the log. This has been dialed back by default in Service Pack 1, so you should see the log revert to SQL Server 2014 behavior.

Enhanced tempdb visibility

On service start, a new message gets written to the error log, indicating when the data files for tempdb are not configured with the same size and autogrow settings. The message also indicates the number of files, so that issue is easy to spot too. Here is an example:

This is described for SQL Server 2014 SP2 in KB #3170020.

Several ShowPlan additions

There have a bunch of enhancements to the information included in ShowPlan output, which you might imagine will eventually be consumed by Management Studio (in fact some already are), and 3rd party tools like Plan Explorer. Many of these were recently added to SQL Server 2014 SP2:

• There is a new EstimatedRowsRead attribute to highlight issues with residual pushdown predicates (see Rob Farley's Connect item and KB #3107397). Example:

 <RelOp ... EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" ...>

• Information about enabled trace flags is now included (see KB #3170115). This is all worthy of a separate post, but a quick example of the trace flag information:

 <TraceFlags IsCompileTime="true">
   <TraceFlag Value="9130" Scope="Session" />
 </TraceFlags>
 <TraceFlags IsCompileTime="false">
   <TraceFlag Value="8649" Scope="Global" />
 </TraceFlags>

• Memory grant warnings are added to actual showplan output. From KB #3172997: "When SQL Server detects a scenario in which memory grant usage is inefficient, the MemoryGrantWarning element is added to actual Showplan XML. This element contains information about initial request, granted memory, and maximum used grant." Also see KB #3170116.
• Some additional memory information (see KB #3170112) and query-level runtime information can be seen in the following ShowPlan XML output:

 <MemoryGrantInfo ... MaxQueryMemory="660568" />
 <OptimizerHardwareDependentProperties ... MaxCompileMemory="2626808" />
 <QueryTimeStats CpuTime="0" ElapsedTime="0" />

• New information about parameter data types is now included in the output, for example:

 <ParameterList>
   <ColumnReference Column="@a" ParameterDataType="int" ...
   <ColumnReference Column="@b" ParameterDataType="decimal(12,2)" ...
 </ParameterList>

• There is improved information about tempdb spills in actual ShowPlan output, including information about threads, amount of memory, and number of writes to tempdb. For more information, see KB #3107400.

Lighter query profiling

SQL Server 2014 added a DMV, sys.dm_exec_query_profiles, allowing you to monitor the progress and metrics of each operator in a running query, as long as one of the SET SHOWPLAN options or SET STATISTICS PROFILE were enabled. This could not be enabled globally because of the overhead involved and was recommended for isolated tuning and troubleshooting efforts.

You can now get per-operator query execution metrics without full showplan overhead. If you enable trace flag 7412 globally, or have an Extended Events session running with query_thread_profile, you can use sys.dm_exec_query_profiles, view live query statistics, and use the new sys.dm_exec_query_statistics_xml DMF. The DMF takes a session ID and lets you see a query someone else is currently running; it works with both this new lightweight method, and also if the query is using the heavier collection method.

Other improved diagnostics

• Extended Events and PerfMon expanded to help better troubleshoot Availability Group latency (see KB #3173156 for the 2014 SP2 story).
• New sys.dm_db_incremental_stats_properties DMF to expose per-partition stats details - see KB #3170114. (Erin Stellato shows a workaround here.)
• New BIGINT columns to help better correlate queries (by query_hash/query_plan_hash) between Extended Events (which use UINT64) and DMVs (which use varbinary). You can see the full set by running the following query in SQL Server 2016 SP1:

 SELECT [object_name], [name], [type_name]
   FROM sys.dm_xe_object_columns 
   WHERE [name] like N'%plan_hash%';

Results:

Conclusion

Those are quite a few appealing new features being added to SQL Server 2016 through its first service pack (and if you fish around, you might see a couple more things). Between these new features, and so many features now being available in all editions, very few arguments remain for staying on older versions of SQL Server.

Next Steps

Follow these links for downloads, tips, and more information:
• Download Service Pack 1
• KB #3182545 : SQL Server 2016 Service Pack 1 release information
• SQL Server 2016 Service Pack 1 (SP1) released !!!
• A Big Deal : SQL Server 2016 Service Pack 1
• Many tips on SQL Server 2016

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips