Key features of SQL Server CTP2.3

Key features of SQL Server CTP2.3

Database-related Enhancements

Row level security already supports in-memory OLTP tables. Users can now implement Row-level security policies on memory-optimized tables.

In addition, schemabinding, predicate functions, and inline table-valued functions contain native_compilation compilation options.

UDFs user-defined functions that use the native_compilation compilation option can be used in local modules and exchange queries, like memory-optimized tables and disk tables.

UDFs user-defined functions that do not use the native_compilation compilation option can only use Exchange queries.

In-memory OLTP has supported the following built-in security functions, adding support for RLS

• Is_member ({' Group ' | ' Role '})
• Is_rolemember (' role ' [, ' Database_principal '])
• Is_srvrolemember (' role ' [, ' Login '])
• Original_login ()
• Session_user
• Current_User
• SUSER_ID ([' Login '])
• SUSER_SID ([' Login '] [, Param2])
• SUSER_SNAME ([Server_user_sid])
• System_user
• Suser_name
• USER
• USER_ID ([' User '])
• USER_NAME ([id])
• Context_info ()

Another core engine extension upgrade is the dynamic partitioning of thread-safe memory objects based on NUMA nodes or CPUs

This improvement will enable higher parallel loads to be met on NUMA hardware. A thread-Safe memory object (type CmemThread) dynamically improves the partition of a NUMA node or CPU based on load characteristics and contention.

In SQL2012 and SQL2014, you need to turn on the trace flag TF 8048来 Raise the Memory object in the partition node in the CPU partition. This improvement not only requires no trace flags, but also dynamically adjusts partitions based on CPU contention.

CTP2.3 includes performance optimizations for the In-memory Data Warehouse (Columnstore columnStore), Ncci can now be created via triggers, or CDC and change tracking can be allowed.

To better analyze the Columnstore index, three columns were added to the DMV in Sys.dm_db_column_store_row_group_physical_stats: Transition_to_compressed_state, Transition_to_compressed_state_desc,has_vertipaq_optimization

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

Enhancements to SQL Server Analysis Service (SSAS)

Dax query performance is improved to optimize the tabular engine. ‘

DirectQuery Enhanced, now DirectQuery can generate simpler queries with better performance.

Dax supports variables that can store the result of an expression as a named variable, so you can pass one argument to another measure expression.

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

SQL Server Reporting Services (SSRS) Enhancements

Report Builder already contains the modern theme.

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

SQL Server Integration Service (SSIS) Enhancements

OData V4 protocol support, SSIS Error column support, advanced log level support.

The previous SSIS catalog provided only four log levels: None, Basic, performance, Verbose

New advanced Log level removes previously inflexible log levels

New mechanism: The event context can be collected when events are triggered

New Ssis_logreader role that allows access to all view-related SSISDB action logs

New log Custom Level definition logs and events collection

Allow collection of event contexts, such as variable values, task properties, connection strings

Enhancements to Master Data Service (MDS)

Three properties added

Many to many inheritance hierarchy

Excel plugins for Business rules management

Merge conflicts

Enhancements to query execution

Enhanced Memory grant Usage diagnostics

The following extended event Xevents has been added to better diagnose memory grant usage and issues.

Showplan XML adds memory grant usage extension for each thread and iterator (in Runtimecountersperthread node)

Query_memory_grant_blocking
Query_memory_grant_resource_semaphores
Query_memory_grant_usage (details on ideal vs granted vs used memory)

DBCC CHECKDB Enhancements

1. Validating persisted computed columns and filtered indexes

2, when validating a large table with thousands of partitions, the DBCC CHECKDB did a performance improvement, speed will be accelerated

Key features of SQL Server CTP2.3