Summary of key features of SQL Server 2016 CTP2.3, 2016ctp2. 3

Summary of key features of SQL Server 2016 CTP2.3, 2016ctp2. 3

SQL Server 2016 brings new ground-breaking in-memory performance and analysis functions for key task processing. Comprehensive Security features-Always Encrypted technology helps protect your data

Database enhancements

Row Level Security supports In-memory OLTP tables. You can now implement the row-level security policy on the memory optimization table.

In addition, SCHEMABINDING, predicate, and inline Table value functions must all contain NATIVE_COMPILATION compilation options.

UDFs with NATIVE_COMPILATION compilation options can be used in local modules and exchange queries, just like memory optimization tables and disk tables.

UDFs without the NATIVE_COMPILATION compilation option can only use exchange queries.

In-memory OLTP already supports the following built-in security functions, and adds support for Recursive.

IS_MEMBER({‘group' | ‘role'})IS_ROLEMEMBER (‘role' [, ‘database_principal'])IS_SRVROLEMEMBER (‘role' [, ‘login'])ORIGINAL_LOGIN()SESSION_USERCURRENT_USERSUSER_ID([‘login'])SUSER_SID([‘login'] [, Param2])SUSER_SNAME([server_user_sid])SYSTEM_USERSUSER_NAMEUSERUSER_ID([‘user'])USER_NAME([id])CONTEXT_INFO()

Another core engine expansion improvement is: Dynamic partition thread Security Memory Object Based on NUMA node or CPU

This improvement will enable the NUMA hardware to cope with higher parallel loads. Thread-Safe Memory Object (type CmemThread) dynamically improves partitions for NUMA nodes or CPUs based on Load Characteristics and contention.

In SQL2012 and SQL2014, You need to enable the trace mark TF 8048 to improve the memory objects in the partition nodes in the CPU partition. This improvement not only does not require tracking marks, but also allows you to dynamically adjust partitions Based on CPU contention.

CTP2.3 includes performance optimization for the In-memory Data Warehouse (columnar storage columnStore). NCCI can now be created through triggers, or CDC and Change Tracking can be used ).

To better store indexes for analysis columnsSys. dm_db_column_store_row_group_physical_statsThree columns are added to the DMV:

Transition_to_compressed_state, transition_to_compressed_state_desc, has_vertipaq_optimization

Nonclustered Columnstore Index (NCCI)
Clustered Columnstore Index (CCI)

SQL Server Analysis Service (SSAS) Enhancement

DAX improves query performance and optimizes the Tabular engine. '

DirectQuery is enhanced. DirectQuery can now generate simpler queries with better performance.

DAX supports variables. variables can store the results of an expression as named variables. Therefore, you can pass a parameter to other measure expressions.

Once the result value of the variable expression is calculated, the value is not changed, even if the variable references another expression.

Enhanced SQL Server Reporting Services (SSRS)

Report Builder already contains the modern topic.

For the latest browsers, report rendering supports the HTML5 standard.

SQL Server Integration Service (SSIS) Enhancement

Supported by oData v4 protocol, SSIS error column, and advanced log level.

Previously, the SSIS directory provided only four log levels: None, Basic, Performance, and Verbose.

The new advanced Log Level removes the previously inflexible Log Level

New Mechanism: event context can be collected when events is triggered.

The new ssis_logreader role allows access to all view-related SSISDB operation logs.

New custom log and events collection

Allows event context collection, such as variable values, task attributes, and connection strings.

Master Data Service (MDS) Enhancement

Added three features

Hierarchy of inheritance from orders to orders

Excel plug-in for Business Rule Management

Merge conflicts

Query execution Enhancement

Enhanced memory grant usage Diagnosis

Added the following extended Event XEvents to better diagnose memory granting usage and problems.

Showplan xml adds the memory grant usage extension for each thread and iterator (in the RunTimeCountersPerThread node)

query_memory_grant_blockingquery_memory_grant_resource_semaphoresquery_memory_grant_usage (details on ideal vs granted vs used memory)

Dbcc checkdb Enhancement

1. Verify the persistent computing columns and filter indexes.

2. When a large table with thousands of partitions is verified, the performance of dbcc checkdb is improved, and the speed is faster.

The above is a summary of the key features of SQL Server 2016 CTP2.3.