·
5 min read

SQL Server 2016 Community Technology Preview 2.3 is available

The SQL Server engineering team is pleased to announce the immediate availability of SQL Server 2016 August monthly public preview release CTP 2.3. This release fully embraced the “Cloud First” tenet, as this release build was deployed to SQL Azure Database service first, running in production prior to the release build.

To learn more about the release, visit the SQL Server 2016 preview page. To experience the new, exciting features in SQL Server 2016 and the new rapid release model, download the preview or try the preview by using a virtual machine in Microsoft Azure and start evaluating the impact these new innovations can have for your business. Have questions? Join the discussion of the new SQL Server 2016 capabilities at MSDN and Stack Overflow. If you run into an issue or would like to make a suggestion, you can let us know using Microsoft’s Connect tool. We look forward to hearing from you!

This preview release is packed with new features and improvements….

Row Level Security is now supported with In-memory OLTP tables. Users can now apply row-level security policies to memory-optimized tables. In addition to SCHEMABINDING, predicate functions and inline TVFs should include the compilation hint WITH NATIVE_COMPILATION. UDFs created with this hint can be used in both native modules and interop queries, and as security predicates on both memory-optimized and disk-based tables. UDFs created without this hint can only be used in interop queries, and as predicates on disk-based tables. The following built-in security functions are supported by In-memory OLTP, intended for use in RLS predicates:

  • 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()

An exciting Core Engine Scalability improvement is made available in this release – dynamically partition thread safe memory objects by NUMA node or by CPU. This improvement will enable higher scalability of high concurrency workloads running on NUMA hardware. Thread safe memory objects (of type CmemThread) will be dynamically promoted to be partitioned by NUMA node or by CPU based on workload characteristics and contention factor. In SQL 2012 and SQL 2014, TF8048 is needed to promote memory objects that are partitioned by node to be partitioned by CPU. This improvement not only eliminated the need for the trace flag, and also dynamically determine partition based on contention.

CTP2.3 includes In-memory data warehouse (columnStore) performance optimizations, NCCI can now be created on tables with triggers, enabled with CDC/Change Tracking. For better insight of Column Store, three new fields to the sys.dm_db_column_store_row_group_physical_stats dynamic management view:

transition_to_compressed_state tinyint

Operation that caused transition to compressed state

0 => UNKNOWN_UPGRADED_FROM_PREVIOUS _VERSION

1 => NOT_APPLICABLE

2 => INDEX_BUILD

3 => TUPLE_MOVER

4 => REORG_NORMAL

5 => REORG_FORCED

6 => BULKLOAD

7 => MERGE
transition_to_compressed_state_desc nvarchar(60)

NOT_APPLICABLE -> It is not applicable for delta store or this compressed rowgroup existed before database upgrade

INDEX_BUILD -> The rowgroup was compressed as part of index create/rebuild

TUPLE_MOVER => The rowgroup was compressed as part of background Tuple Mover

REORG_NORMAL => The closed delta rowgroup was compressed as part of invocation of closed delta rowgroups. One implication is that TM is falling behind

REORG_FORCED => The open rowgroup was compressed

BULKLOAD -> The rowgroup was compressed as part of bulkload operation

MERGE -> The rowgroup was compressed as part of MERGE operation that compressed one or more compressed rowgroups.
has_vertipaq_optimization Bit

Vertipaq optimization is used when rowgroups are compressed into columnar storage to improve compression achieved by rearranging the order of the rows in the row group. This optimization is automatically applied when building column store index or when directly bulk loading data into columnar format or when data transitions from delta store to columnar storage format. However, this optimization is skipped for delta rowgroups when there are one or more NCIs on the column store index to minimize changes needed to maintain mapping index

1 => Yes

0    => No

SQL Server Analysis Service (SSAS) ships several exciting performance improvements in this release:

  • DAX query performance improvement, optimizations to the Tabular engine. These optimizations are mostly focused around reducing the “chattiness” between client tools that use DAX and the server (and eventually the storage engine for both VertiPaq and DirectQuery storage modes). In addition to query optimizations, measure executions have been streamlined as well.
  • DirectQuery enhancements. It now generates simpler queries that will provide better performance.
  • Now includes support for variables in DAX. Variables can now store the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.

SQL Server Reporting Services (SSRS) includes a couple of enhancements:

  • An updated version of Report Builder with a modern theme.
  • Report rendering for modern browsers built on HTML5 standards.

SQL Server Integration Service (SSIS) released oData v4 protocol support, SSIS Error Column support, and advanced logging levels in CTP 2.3. Previous SSIS catalog only provides four kinds of logging level None, Basic, Performance and Verbose. The new advanced logging level remove the inflexibility of previous logging levels:

  • New mechanism to collect the event context when events are fired
  • New ssis_logreader role with permission to access all the view related to SSISDB operation logs
  • New logging level customization to define logs and events collection
  • Allow the collection of event context such as variable value, task properties and connection string
  • New RuntimeLineage logging level to collect data for dynamic lineage. This function is provided through lineage library. There is a known issue that this feature doesn’t work upgrading from earlier preview build of SQL2016, the fix is work-in-progress.

Continue the momentum, Master Data Service added three improvements in this release:

  • Many to many derived hierarchy
  • Excel Addin Business rule management
  • Merge conflicts

Query Execution with improved diagnostics for memory grant usage in this release. The following new XEvents added to facilitate better diagnostics of memory grant usage and issues. Showplan xml is extended to include memory grant usage per thread and iterator (additions in “RunTimeCountersPerThread” element).

  1. query_memory_grant_blocking
  2. query_memory_grant_resource_semaphores
  3. query_memory_grant_usage (details on ideal vs granted vs used memory)

DBCC CHECKDB Improvements in this release include: 1) Persisted computed columns and filtered indexes validation. Persisted computed columns are frequently used. DBCC CHECKDB can take long time to complete with persisted columns. This improvement provides an option to do persisted column validation under EXTENDED_LOGICAL_CHECKS; 2) Performance improvement when validating a table with thousands of partitions.

Avatar photo
SQL Server Team

Related Posts