SQL Server 2016 Service Pack 1 – What’s New

With the release of the most epic Service Pack 1 for SQL Server 2016, the biggest attention of everyone should be drawn to the programmability surface that was brought on the same level for Enterprise, Standard, Web & Express Edition! Plus, where possible, even the LocalDB has a hugely extended set of the programability features!

But here are other Service Pack improvements for the engine that are hugely appreciated:

– Optimised Alter Index operation for the In-Memory Columnstore Indexes! We can add or remove a Columnstore Index with the same effectiveness as a Hash or Nonclustered BW-Tree one! The detailed blog post will follow.

– CREATE OR ALTER. I almost cried when I found out that it was implemented. I was asking, begging, threatening, crying for years to get this in the SQL Server. Now, I can finally have future project deployments of those who are not using SSDT running with much less problems.
Now we can modify and deploy objects like Stored Procedures, Triggers, User-Defined Functions, and Views without any fear. Just “Make it so!”

– USE HINT query option without SysAdmin privileges. A brand new option on the query level is available in SQL Server 2016 SP1 that allows to control the query optimizer behaviour, that was controllable with the trace flags requiring SysAdmin access rights previously.
This functionality is logically similar to OPTION(QUERYTRACEON) functionality, but there are no more “magic” numbers, but descriptive hints, which as already mentioned allows you to avoid sysadmin privileges requirement.
The usage example would be here:

select top 10 ProductKey, sum(sales.SalesAmount)
	from dbo.FactOnlineSales sales
	group by ProductKey
	order by sum(sales.SalesAmount) desc
	option ( recompile, USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

Which would actually disable the Row Goals for the specified query.

For finding out all available query hints, you can query the DMV sys.dm_exec_valid_use_hints, which will contain the name of the available hints for the current instance and version of SQL Server.
On my current test instance I can see the following enabled query hints
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES
DISABLE_OPTIMIZED_NESTED_LOOP
DISABLE_OPTIMIZER_ROWGOAL
DISABLE_PARAMETER_SNIFFING
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS
ENABLE_QUERY_OPTIMIZER_HOTFIXES
FORCE_DEFAULT_CARDINALITY_ESTIMATION
FORCE_LEGACY_CARDINALITY_ESTIMATION

I expect that all of this options to be documented as the SQL Tiger team have been doing an amazing job in regarding of the improvements and documentation of the SQL Server.

Notice that the DMV sys.dm_exec_valid_use_hints is also already available on Azure SQL Databases for some time, but at the moment of writing of this article, it appears to be empty with no hints available.

– Total IO from the executed query operations.

From the below query,

set statistics io on

select top 10 ProductKey, sum(sales.SalesAmount)
	from dbo.FactOnlineSales sales
	group by ProductKey
	order by sum(sales.SalesAmount) desc
	option ( recompile, USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

Here is the output:

(10 row(s) affected)
Table 'FactOnlineSales'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 6161, lob physical reads 4, lob read-ahead reads 13344.
Table 'FactOnlineSales'. Segment reads 13, segment skipped 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Total logical reads 6534, physical reads 3014, writes 0.

Incredible, we do not have to do custom sum on the executed operations – a very simple but extremely useful improvement!

Database Cloning – Clone database is a new DBCC command added that allows power users to trouble shoot existing production databases by cloning the schema and metadata, without the data.
The description of the SQL Server 2016 SP1 functionality can be found in the respective KB:
https://support.microsoft.com/en-us/kb/3177838.
In SQL 2016 Service Pack 1, DBCC CLONEDATABASE also supports cloning of CLR, Filestream/Filetable, Hekaton and Query store objects. DBCC CLONEDATABASE in SQL 2016 SP1 allows you to create pure schema only clone without statistics or query store.

– Identification of the “Lock Pages In Memory” for the SQL Server Service Account. There is a new column with this information in the sys.dm_os_sys_info DMV.

– Identification of the “Instant File Initialisation” for the SQL Server Service Account. There is a new column with this information in the sys.dm_server_services DMV.

– TempDB configuration information in the error log. The Error Log will indicate the number of TempDB files and their respective sizes and growth settings.

– Noisy Hekaton messages are removed from the error log.

– New Extended Events and Perfmon diagnostics capability added to troubleshoot latency more efficiently

– New cleanup stored procedure sp_flush_CT_internal_table_on_demand introduced to clean the change tracking internal table on demand

– Execution Plan Detailed Information Improvements.
Related to the information within the execution plan there are huge improvements with so many great details, such as:
– memory grants warnings
– max memory enabled for the query
– enabled trace flags
– cpu time per iterator
– elapsed time per iterator
– top waits

– New DMF for incremental statistics: sys.dm_db_incremental_stats_properties

– New DMF for obtaining the actual query execution plan with actual number of rows for the query that is still being executed : sys.dm_exec_query_statistics_xml. This is a very good improvement, and it will greatly improve the troubleshooting capability of the currently running queries.

– Lightweight per iterator query execution profiling – Dramatically reduce performance overhead of collecting per-operator query execution statistics such as actual number of rows. This feature can be enabled either using global startup TF 7412, or is automatically turned on when an XE session containing query_thread_profile is enabled. When the lightweight profiling is on, the information in sys.dm_exec_query_profiles is also available, enabling the Live Query Statistics feature in SSMS and new DMF sys.dm_exec_query_statistics_xml.

There are more things that were added to this incredible Service Pack and so I recommend the official announcement & documentation for discovering them all!