New features and enhancements in SQL Server 2016 SP1

SQL Server 2016 SP1 is released as announced by Microsoft. It comes with a bunch of new features and enhancements as a result of customer and community feedback. In this article, I will introduce you to these new features and enhancements.

Enterprise features added to Standard, Web, Express and Local DB editions

In SQL Server 2016 SP1, a group of features that were available only in the Enterprise Edition, are now available now in the Standard, Web, Express, and Local DB SQL Server Editions. The features list includes Database Snapshot, ColumnStore, Table Partitioning, Compression, Always Encrypted, Fine Grained Auditing, Multiple Filestream Containers and PolyBase. For Change Data Capture, it becomes available only in the Standard and Web SQL Server Editions as this feature requires SQL Server Agent which is not available in the Express and Local DB Editions. For In Memory OLTP feature, it becomes available in all SQL Server Editions except for the Local DB Edition as it requires creating filestream file groups that is not possible in Local DB due to insufficient permissions.

The main goal for this change is to allow the developers to develop and build the applications that depends on these features on any SQL Server Edition installed in the customer’s environment. But you may ask why would I still need the SQL Server Enterprise Edition if Microsoft provides all these features in the other cheaper Editions? The answer is the RAM and CPU limitations that still exists in these SQL Server Editions, where we are limited with 16 cores and 128 GB of RAM in the Standard Edition for example. If you need to go over these values, you still need to buy the SQL Server Enterprise Edition.

Using Windows Server 2016 Storage Class Memory to boost the Transaction processing

In a heavily transactional loaded system, transaction log committing is one of the most significant performance issue that affects overall performance. SQL Server 2016 SP1 comes with a new feature that allows employing the Storage Class Memory that is supported in Windows Server 2016 to speed up the transactions committing process by orders of magnitude.

CREATE OR ALTER new statement

SQL Server 2016 SP1 introduces a new CREATE OR ALTER T-SQL statement for modules, that allows us to run a script for database objects such as a view, stored procedure, function or trigger without the need to know if this database object exists or not, where it will work as a normal CREATE statement if the object is not exist or it will work as a normal ALTER statement if the object already exists.

If you try to create a stored procedure that already exists:

CREATE PROCEDURE CreateOrALterDemo

BEGIN

PRINT N'Hello from SQLShack.com';

END

You will see the below error:

But you can run the below CRAETE OR ALTER statements many times without any error:

USE SQLShackDemo

CREATE OR ALTER PROCEDURE CreateOrALterDemo

BEGIN

PRINT N'Hello from SQLShack.com';

END

USE HINT query option

As a replacement for the OPTION(QUERYTRACEON) query hint statement, that required sysadmin permission to be executed, SQL Server 2016 SP1 introduces OPTION(USE HINT (’ ’)) query hint without the need to have sysadmin privileges or remember the trace flag number, with 9 supported hints that can be listed by querying the sys.dm_exec_valid_use_hints system object with the below sorted result:

Where using the ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS hint is equivalent to turning on the trace flag 9476, ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES hint is equivalent to enabling trace flag 4137 and 9471, DISABLE_PARAMETER_SNIFFING hint is equivalent to enabling trace flag 4136, DISABLE_OPTIMIZER_ROWGOAL hint is equivalent to enabling trace flag 4138, DISABLE_OPTIMIZED_NESTED_LOOP hint is equivalent to enabling trace flag 2340, ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS hint is equivalent to enabling trace flag 2389, ENABLE_QUERY_OPTIMIZER_HOTFIXES hint is equivalent to enabling trace flag 4199, FORCE_DEFAULT_CARDINALITY_ESTIMATION hint is equivalent to enabling trace flag 2312 and FORCE_LEGACY_CARDINALITY_ESTIMATION hint is equivalent to enabling trace flag 9481.

DBCC CLONEDATABASE

DBCC CLONEDATABASE statement is used to create an empty copy of the user database with its statistics for troubleshooting purposes. In SQL Server 2016 SP1, DBCC CLONEDATABASE supports cloning the CLR, Filestream, Filetable, In-Memory OLTP and Query Store objects. New options are available now to choose between the Query Store only, statistics only, or schema only without statistics or query store.

The below query will create a default cloned copy of the SQLShackDemo database with schema, statistics and query store metadata:

DBCC CLONEDATABASE (SQLShackDemo, SQLShackDemoClone)

Where the following query will exclude the statistics when creating a cloned copy of the database:

DBCC CLONEDATABASE (SQLShackDemo, SQLShackDemoClone) WITH NO_STATISTICS

And the below one will exclude the query store when creating a cloned copy of the database:

DBCC CLONEDATABASE (SQLShackDemo, SQLShackDemoClone) WITH NO_QUERYSTORE

The last statement will include the schema only when creating a cloned copy of the database:

DBCC CLONEDATABASE (SQLShackDemo, SQLShackDemoClone) WITH NO_STATISTICS,NO_QUERYSTORE

Lock Page in Memory and instant file initialization information

It is applicable now in SQL Server 2016 SP1 to check if the Lock Page in Memory model is enabled by querying the sql_memory_model from the sys.dm_os_sys_info system DMV, where 1 indicates CONVENTIONAL memory model, 2 indicates LOCK_PAGES and 3 indicates LARGE_PAGES as follows:

SELECT sql_memory_model, sql_memory_model_desc

FROM sys.dm_os_sys_info

The result in our case will be like:

Also the instant file initialization can be checked by querying the sys.dm_server_services system DMV for the instant_file_initialization_enabled value:

SELECT instant_file_initialization_enabled

FROM sys.dm_server_services;

The result in our case will be like:

TempDB Check errorlog message

A new errorlog message displayed when the SQL Server service restarted indicating that the tempdb files are not configured with the same size and auto growth settings, displaying the number of tempdb files too as follows:

Change Tracking manual cleanup

If the size of the change tracking tables become uncontrollable, ifthe automatic cleanup job is not running sufficiently fast to keep up, a new stored procedure sys.sp_flush_CT_internal_table_on_demand can be used to manually clean the change tracking tables in SQL Server 2016 SP1.

Less In-Memory OLTP logging

In SQL Server 2016, In-Memory OLTP started logging additional information to the SQL Server Errorlog to make it easier to troubleshoot it, which in some cases was overwhelming the Errorlog with these excessive messages. In SQL Server 2016 SP1, these In-Memory OLTP logging messages are reduced to .

Lightweight Query Profiling

In SQL Server 2016 SP1, per-operator query execution statistics performance overhead is reduced by turning on the new lightweight query profiling feature. It can be enabled by turning on the trace flag 7412 globally or it will be enabled automatically if the extended events session is running with query_thread_profile. Once the lightweight profiling feature is enabled, the sys.dm_exec_query_profiles can be used to monitors the real time query progress while the query is in execution, the live query statistics feature can be used in the SQL Server Management Studio and the sys.dm_exec_query_statistics_xml new DMF can be used to return the query execution plan for the running requests, by providing the session ID only.

ShowPlan enhancements

Starting from SQL Server 2016 SP1, a new enhancement was added to the ShowPlan by providing information such as information about the enabled trace flags as the below plan output:

Also a MemoryGrantWarning will be included in the ShowPlan information if the SQL Server Engine detects that the memory grant is not sufficient. The query level memory information is provided too, within the generated XML plan output as the below snapshot:

Information about the EstimatedRowsRead, parameters data types, query elapsed time, top waits and tempdb spills are also included within the XML plan output depending on the query execution.

Parallel INSERT…SELECT for Local Temp Tables

In SQL Server 2016, the parallel INSERT…SELECT to the local temp tables is enabled by default, without the need to use the TABLOCK hint such as the case of INSERT into user table, improving the query performance. But for the heavy and concurrent workload, the parallel insert will cause a regression. Starting from SQL Server 2016 SP1, the parallel operation of the INSERT… SELECT to the local temp tables is disabled by default and requires TABLOCK hint to enable it.

DROP TABLE DLL support for Replication Articles

In SQL Server 2016 SP1, the table that acts as article in the transactional replication publication can be dropped from the database and the publication if the Allow_Drop property is set to TRUE on all the publications that the table is article in it. If the table is dropped, the log reader agent will perform a cleanup command for the distribution database to clean the dropped table’s metadata.

New incremental statistics DMF

A new DMF sys.dm_db_incremental_stats_properties introduced in SQL Server 2016 SP1 that is used to return the per-partition incremental statistics properties for specific database table.

Performance monitoring enhancement

In SQL Server 2016 SP1, new Extended Events and Perfmon diagnostics capability are added to troubleshoot the Always On Availability Groups more efficiently. New two BIGINT Extended Events columns equivalent to query_hash and query_plan_hash added also to provide better correlation between the Extended events and the DMVs.

Conclusion

In this article we went through the new features introduced in SQL Server 2016 SP1 and the enhancements to the current features that are both valuable and useful.

The most valuable update in this service pack is that many Enterprise features are available now in the Standard, Web, Express, and Local DB SQL Server Editions. This allows a consistent programmability surface area for developers and organizations across SQL Server editions, enabling them to build advanced applications that scale across different SQL Server Editions. Download the SP1 from the link mentioned below and enjoy testing the new features and enhancements.

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen