SQL Server 2016 Community Technology Preview 2.2 is available

July 28, 2015

Product
SQL Server 2016

The SQL Server engineering team is committed to SQL Server 2016 monthly public preview release rhythm. Following CTP 2.1 release last month, we are excited to announce the immediate availability of SQL Server 2016 CTP 2.2 release for download. This incremental release includes new capabilities for query store, stretch database, core engine, temporal, MDS, reporting services and engine scalability improvements. Learn about these improvements below.

The Stretch Database enables you dynamically stretch the cold transactional data to Azure SQL so your operational data is always at hand, no matter the size, and you can benefit from the low cost of using Azure. Improvements in this release include:

Row Level Security (RLS) enabled
Stretch Database Advisor now available to analyze existing database tables, discovering and evaluating candidates for stretch by adjustable table size thresholds
- Bundled with SQL Server 2016 Upgrade Advisor Preview 1, Stretch Database Advisor is available for download here or through the Web Platform Installer

First released in SQL 2008 R2, the Master Data Services (MDS) is the SQL Server solution for master data management. We are making significant investments in SQL 2016 to advance strategic capabilities in the data management space. Improvements in this release include:

Sync entity between models allow you to setup sync relationship to sync entity from another model. Steps:
1. Go to Admin, Sync Entity page
2. Click Add
3. Choose target model, version and entity, source model, version and entity
4. Choose sync type to be on-demand or auto sync
5. Click Save
SCD Type-2 support enables creation of SCD type-2 subscription view for Member transaction log type entities. Steps:
1. Go to Admin, Entity page
2. Choose transaction log type
3. If transaction log type is Member, go to Integration, Create Views page
4. Create a view of SCD Type-2 then History View can be chosen from format type
Compound Keys index support includes custom attributes to improve the performance or enforce constraints. NOTE: In this release Entity Based Staging batches need to start by calling stored procedures directly instead of using the web UI. Steps:
1. Go to Admin, Entity page
2. Click Add on Customer Indexes
3. Choose the columns and click Save to create the index

The query “flight recorder,” Query Store, captures current and historical query plans and execution metrics, enabling you to easily monitor and troubleshoot query performance issues. Query Store has been made available since the first SQL 2016 community technical preview. Improvements in this release include:

Automatically switch to READ_ONLY mode when the reaches the defined max size limit and store collecting new query plans and run time stats. You will be able to detect that by looking at readonly_reason from sys.database_query_store_options. Value 65536 indicates when Query Store has reached the defined size limit.
Query Store UI enhancements and bug fixes
No force plan recompiles after MAX_PLANS_PER_QUERY is hit. The max_plans_per_query value can be examined from sys.database_query_store_options. NOTE: Force plan recompile could have potential performance overhead.

Temporal enables handling and analyzing database records that changes over time. Improvements in this release include:

Full support column with ROWVERSION (TIMESTAMP) to support UPDATE operation on ROWVERSION column in temporal table
COLUMNPROPERTY exposes ‘ishidden’ property
select columnproperty (object_id (‘dbo.sample_table’), ‘SysStartTime’, ‘ishidden’)
Several improvements in SQL Server Management Studio:
- Syntax highlighting for temporal keywords
- Transact-SQL client side validations
- Script table as DROP TO includes DROP script for history table
- SSMS surfaces IsHidden information in column Properties dialog

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

query_memory_grant_blocking
query_memory_grant_resource_semaphores
query_memory_grant_usage (details on ideal vs granted vs used memory)

Core Engine Scalability improvements 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 partitioned by node, to be partitioned by CPU. This improvement not only eliminated the need for the trace flag, but also dynamically determined partition based on contention.

DBCC CHECKDB improvements in this release include:

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.
Performance improvements when validating a table with thousands of partitions.

Reporting Services added treemap and subburst charts. Report authors can now create two additional chart types: