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.
Microsoft已发布SQL Server 2016 SP1。 由于客户和社区的反馈，它具有许多新功能和增强功能。 在本文中，我将向您介绍这些新功能和增强功能。
企业功能已添加到Standard，Web，Express和Local DB版本中 (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.
在SQL Server 2016 SP1中，仅在企业版中可用的一组功能现在在标准版，Web版，Express版和本地DB SQL Server版中可用。 功能列表包括数据库快照，列存储，表分区，压缩，始终加密，细粒度审核，多个文件流容器和PolyBase。 对于Change Data Capture，此功能仅在Standard和Web SQL Server版本中可用，因为此功能需要在Express和Local DB版本中不可用SQL Server Agent。 对于“内存中的OLTP”功能，该功能在所有SQL Server版本中均可用，但“本地数据库版本”除外，因为它需要创建由于权限不足而无法在本地数据库中创建的文件流文件组。
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.
此更改的主要目标是允许开发人员在客户环境中安装的任何SQL Server Edition上开发和构建依赖于这些功能的应用程序。 但是您可能会问，如果Microsoft在其他便宜的版本中提供所有这些功能，为什么我仍然需要SQL Server Enterprise Edition？ 答案是这些SQL Server版本中仍然存在RAM和CPU限制，例如，在标准版中，我们受限于16个内核和128 GB RAM。 如果需要查看这些值，则仍然需要购买SQL Server Enterprise Edition。
使用Windows Server 2016存储类内存来增强事务处理 (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.
在具有大量事务负载的系统中，事务日志提交是影响整体性能的最重要的性能问题之一。 SQL Server 2016 SP1带有一项新功能，该功能允许使用Windows Server 2016中支持的存储类内存来将事务提交过程加快几个数量级。
创建或更改新语句 (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.
SQL Server 2016 SP1为模块引入了新的CREATE OR ALTER T-SQL语句，该语句使我们能够为数据库对象（例如视图，存储过程，函数或触发器）运行脚本，而无需知道此数据库对象是否存在，如果该对象不存在，它将用作普通的CREATE语句；如果该对象已经存在，它将用作普通的ALTER语句。
If you try to create a stored procedure that already exists:
如果您尝试创建一个已经存在的存储过程：
CREATE PROCEDURE CreateOrALterDemo
AS
BEGIN
PRINT N'Hello from SQLShack.com';
END
GO
You will see the below error:
您将看到以下错误：
But you can run the below CRAETE OR ALTER statements many times without any error:
但是您可以多次运行以下CRAETE或ALTER语句，而不会出现任何错误：
USE SQLShackDemo
GO
CREATE OR ALTER PROCEDURE CreateOrALterDemo
AS
BEGIN
PRINT N'Hello from SQLShack.com';
END
GO
使用提示查询选项 (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:
作为需要执行sysadmin权限的OPTION（QUERYTRACEON）查询提示语句的替代，SQL Server 2016 SP1引入了OPTION（USE HINT（''））查询提示，而无需具有sysadmin特权或记住跟踪标志号，具有9个受支持的提示，可以通过使用以下排序结果查询sys.dm_exec_valid_use_hints系统对象来列出这些提示：
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.
凡使用ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS提示相当于打开跟踪标志9476，ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES提示相当于打开跟踪标志4137和9471 ，DISABLE_PARAMETER_SNIFFING提示相当于打开跟踪标志4136 ，DISABLE_OPTIMIZER_ROWGOAL提示相当于打开跟踪标志4138 ，DISABLE_OPTIMIZED_NESTED_LOOP提示是相当于使跟踪标志2340 ，ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS暗示相当于使跟踪标志2389 ，ENABLE_QUERY_OPTIMIZER_HOTFIXES暗示相当于使跟踪标志4199 ，FORCE_DEFAULT_CARDINALITY_ESTIMATION暗示相当于使跟踪标志2312和FORCE_LEGACY_CARDINALITY_ESTIMATION暗示相当于使跟踪标志9481 。
DBCC CLONEDATABASE (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.
DBCC CLONEDATABASE语句用于创建带有其统计信息的用户数据库的空副本，以进行故障排除。 在SQL Server 2016 SP1中，DBCC CLONEDATABASE支持克隆CLR，文件流，文件表，内存中OLTP和查询存储对象。 现在可以使用新选项在仅查询存储，仅统计信息或仅具有统计信息或查询存储的模式之间进行选择。
The below query will create a default cloned copy of the SQLShackDemo database with schema, statistics and query store metadata:
以下查询将创建具有模式，统计信息和查询存储元数据SQLShackDemo数据库的默认克隆副本：
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:
现在适用于SQL Server 2016 SP1，以通过从sys.dm_os_sys_info系统DMV中查询sql_memory_model来检查是否启用了内存中锁定页面模型，其中1表示常规内存模型，2表示LOCK_PAGES，3表示LARGE_PAGES，如下所示：
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:
也可以通过查询sys.dm_server_services系统DMV中的Instant_file_initialization_enabled值来检查即时文件初始化：
SELECT instant_file_initialization_enabled
FROM sys.dm_server_services;
The result in our case will be like:
在我们的案例中，结果将是：
TempDB检查错误日志消息 (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:
SQL Server服务重新启动时显示新的错误日志消息，表明tempdb文件未配置为具有相同的大小和自动增长设置，同时也显示tempdb文件的数量，如下所示：
更改跟踪手动清理 (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.
如果更改跟踪表的大小变得不可控制，或者自动清理作业的运行速度不足以保持同步，则可以使用新的存储过程sys.sp_flush_CT_internal_table_on_demand手动清理SQL Server 2016 SP1中的更改跟踪表。
更少的内存中OLTP日志记录 (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 .
在SQL Server 2016中，内存中OLTP开始将其他信息记录到SQL Server错误日志中，以便更轻松地对其进行故障排除，在某些情况下，这些过多的消息使错误日志不堪重负。 在SQL Server 2016 SP1中，这些内存中OLTP日志记录消息被减少为。
轻量级查询分析 (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.
在SQL Server 2016 SP1中，通过启用新的轻量级查询分析功能，可以减少每个操作员查询执行统计信息的性能开销。 可以通过全局打开跟踪标志7412来启用它，如果扩展事件会话正在使用query_thread_profile运行，它将自动启用。 启用轻量级分析功能后，可以使用sys.dm_exec_query_profiles监视查询执行过程中的实时查询进度，可以在SQL Server Management Studio和sys.dm_exec_query_statistics_xml新的DMF中使用实时查询统计信息功能。通过仅提供会话ID，可用于返回正在运行的请求的查询执行计划。
ShowPlan增强功能 (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:
从SQL Server 2016 SP1开始，通过提供信息（例如有关以下计划输出的已启用跟踪标志的信息）为ShowPlan添加了新的增强功能：
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:
如果SQL Server引擎检测到内存授予不足，还将在ShowPlan信息中包含MemoryGrantWarning。 在生成的XML计划输出中，还提供了查询级别的内存信息，如下所示：
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.
根据查询执行情况，有关XML计划输出中还包括有关EstimatedRowsRead，参数数据类型，查询经过时间，等待时间和tempdb溢出的信息。
并行INSERT…SELECT用于本地临时表 (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.
在SQL Server 2016中，默认情况下启用对本地临时表的并行INSERT…SELECT，而无需使用TABLOCK提示（例如在用户表中插入INSERT的情况），从而提高了查询性能。 但是对于繁重的并发工作负载，并行插入将导致回归。 从SQL Server 2016 SP1开始，默认情况下将禁用INSERT…SELECT对本地临时表的并行操作，并且需要TABLOCK提示将其启用。
DROP TABLE DLL对复制文章的支持 (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.
在SQL Server 2016 SP1中，如果在表为其中项目的所有发布上的Allow_Drop属性设置为TRUE，则可以从数据库和发布中删除充当事务复制发布中项目的表。 如果删除了表，则日志读取器代理将对分发数据库执行清理命令，以清理删除的表的元数据。
新的增量统计DMF (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.
SQL Server 2016 SP1中引入的新DMF sys.dm_db_incremental_stats_properties用于返回特定数据库表的按分区增量统计信息属性。
性能监控增强 (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.
在SQL Server 2016 SP1中，添加了新的扩展事件和Perfmon诊断功能，以更有效地对AlwaysOn可用性组进行故障排除。 添加了两个新的BIGINT扩展事件列，它们等效于query_hash和query_plan_hash，以提供扩展事件和DMV之间的更好关联。
结论 (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.
在本文中，我们介绍了SQL Server 2016 SP1中引入的新功能以及对既有价值又有用的当前功能的增强。
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.
此Service Pack中最有价值的更新是Standard，Web，Express和Local DB SQL Server Editions现在提供了许多企业功能。 这为跨SQL Server版本的开发人员和组织提供了一致的可编程性表面积，使他们能够构建可跨不同SQL Server版本进行扩展的高级应用程序。 从下面提到的链接下载SP1，并享受对新功能和增强功能的测试。
有用的链接： (Useful Links:)
- sys.dm_exec_query_statistics_xml (Transact-SQL) sys.dm_exec_query_statistics_xml（Transact-SQL）
- sys.dm_exec_query_profiles (Transact-SQL) sys.dm_exec_query_profiles（Transact-SQL）
- SQL Server 2016 Service Pack 1 (SP1) released !!! SQL Server 2016 Service Pack 1（SP1）已发布!!!
- Supports DROP TABLE DDL for articles that are included in transactional replication in SQL Server 2014 or in SQL Server 2016 SP1 为SQL Server 2014或SQL Server 2016 SP1中的事务复制中包含的文章支持DROP TABLE DDL
- sys.dm_db_incremental_stats_properties (Transact-SQL) sys.dm_db_incremental_stats_properties（Transact-SQL）
- Download Microsoft SQL Server 2016 Service Pack 1 (SP1) 下载Microsoft SQL Server 2016 Service Pack 1（SP1）
翻译自: https://www.sqlshack.com/new-features-enhancements-sql-server-2016-sp1/
1302
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
