Top 9 Features of SQL Server 2016

SQL Server - SeniorDBA

Technology is always improving. Microsoft SQL Server 2016 includes many new and improved features that will provide users with greater availability, better performance, and more security. The Microsoft IT Enterprise Services BI team has identified their top eight features and enhancements:

  • One programming surface across all editions – With November’s SQL Server 2016 Service Pack 1 (SP1), you can switch from Express to Standard, or Standard to Enterprise, and you don’t have to rework code to take advantage of additional features.
  • In-Memory OLTP helps ESBI meet their users’ business requirements for increased agility.
  • Columnstore Indexes reduce the amount of time it takes to run and render SRSS reporting data.
  • Temporal data reduces the amount of support tickets received from the field due to inaccurate data.
  • Row-Level Security provides a more reliable and standardized method to easily control which users can access data.
  • Dynamic Data Masking helps limit exposure of sensitive data, preventing users who should not have access to the data from viewing it.
  • Query Store provides better insight into the performance differences caused by changes in query plans.
  • Active Query Statistics allows a view of active query execution plans and helps identify and fix blocking issues while queries are running.
  • SQL Stretch Database helps improve performance to frequently used data while preserving access to archived data.

You can read additional details of these features here.

New SQL Server Sample Database: WideWorldImporters

The sample database for SQL Server known as AdventureWorks, which has been around since the SQL Server 2005 days, had been replaced with a new sample database called WideWorldImporters. It is intended to showcase some of the newer SQL Server 2016 features, capabilities, and performance enhancements.

Microsoft says:

  • WideWorldImporters is the main database for transaction processing (OLTP – OnLine Transaction Processing) and operational analytics (HTAP – Hybrid Transactional/Analytics Processing). Here are some examples of the use of SQL Server capabilities with this database:
    • Real-time operational analytics of sales data is enabled through the use of nonclustered columnstore indexes.
    • Archive tables can be stretched to Azure for long-term retention, reducing storage cost and improving manageability.
    • Query Store is used to keep track of query performance.
    • Temporal tables are used to conveniently keep track of the history of reference data, as well as some of the main entities.
    • JSON is used to enable AJAX calls to some of the key tables, and also to extend the relational schema to record such things as application settings and user preferences.
    • Advanced security features like Always Encrypted, Row-Level Security and Dynamic Data Masking are used to secure data.
    • In-Memory OLTP is used to optimize the performance of table-valued parameters (TVPs) and to optimize ingestion of sensor data.
    • Clustered columnstore indexes are used to reduce the storage footprint of large tables with insert-only workload.
    • Partitioning is used to improve the manageability of large tables.
    • For more detail, see WideWorldImporters use of SQL Server features and capabilities.
  • WideWorldImportersDW is the main database for data warehousing and analytics (OLAP – OnLine Analytics Processing). The data in this database is derived from the transactional database WideWorldImporters, but it uses a schema that is specifically optimized for analytics. Here are some examples of the use of SQL Server capabilities with this database:
    • Clustered columnstore indexes are used to reduce the storage footprint and improve query performance for the fact tables.
    • PolyBase is used to correlate data in the local database with a public data set in Azure Blog storage.
    • In-Memory OLTP is used to improve the performance of the ETL process.
    • Partitioning is used to improve manageability of the fact tables, which can grow very large in a data warehouse.
    • For more detail, see WideWorldImportersDW use of SQL Server features and capabilities.
  • A SQL Server Integration Services (SSIS) package, Daily ETL.ispac, is used to move data from the OLTP database WideWorldImporters to the OLAP database WideWorldImportersDW. The package is designed to use bulk T-SQL statements wherever possible to enhance performance. For details about the ETL workflow, see the WideWorldImportersDW ETL workflow.

Download Link

Why The SQL Server Query Store is Awesome

SQL Server

With the newest version of SQL Server, a new feature named the “Query Store” can help you diagnose performance problems that are related to plan changes. The Query Store in SQL Server 2016 is described as a service that monitors query plans with a full history of query execution. It is unclear of the full feature will be available in the Standard edition, but it  will definitely be available as part of the Enterprise edition.

You can read more about what it does in this article by Joey D’Antoni, we find an example of how you might use this new feature, and why that makes it so awesome.

I got an email from one of my clients (these guys are brave and already live on 2016—they along with Microsoft have that much confidence in the code) about a query that was running poorly over the weekend (worse than in the older environment). A little bit about their environment—it’s largely a data warehouse type solution, with the goal of delivering data sets to their clients. In the upgrade to 2016, we did a rearchitecture that heavily leveraged clustered columnstore indexes, and took advantage of Availability Groups for scale out reads. So remember when one of your customers would email you about something that was slow over the weekend, and you would desperately scour the plan cache, possibly writing xQuery to look for the needle in the haystack of a query that was performing poorly?

Enter the Query Store. I opened up the database, and find the Query Store in Object Explorer.

 

 

SQL Server 2016 Community Technology Preview 2.2

SQL Server

Microsoft announced the SQL Server 2016 Community Technology Preview 2.2 was released along with Upgrade Advisor Preview. You can download the preview here. Microsoft has released some updated notes to keep you informed on their progress:

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:

  • Treemap
  • Subburst charts 

You can read more about how SQL Server 2016 works here.

Top 5 New Features in SQL Server 2016

SQL Server

The next version of SQL Server will be available soon, and I’m looking at what features I’m excited to use. This is a list of the top 5 features I’m looking forward to using in SQL Server 2016, in no particular order.

  1. JSON Support – The way this is implemented in SQL Server 2016 is similar to the way XML support is built in with FOR JSON and OPENJSON.
  2. Always Encrypted – SQL Server has supported both column-level encryption, encryption at rest, and encryption in transit for a while. However these had to be configured independently and were frequently misconfigured. Always Encrypted is new functionality through the use of an enhanced client library at the application so the data stays encrypted while stored in the database and while in transit.
  3. In-Memory Enhancements – In SQL Server 2016 this feature is vastly improved over what was possible in SQL Server 2014, adding the support for foreign keys, unique constraints, and parallelism. Also tables are supported with sizes up to 2TB, which is significantly larger than the old 256GB limit is SQL Server 2014.
  4. Query Store – The Query Store feature maintains a history of query execution plans (configured at the individual database level) with their performance data, and quickly identifies queries that have gotten slower. This allows administrators (or developers) to force the use of an older, better plan if it is needed.
  5. Row-Level Security – A feature that SQL Server has lacked natively is the ability to provide row-level security. This feature restricts which users can view row data in a table, based on a function. SQL Server 2016 introduces this feature, which doesn’t include inserts and inserts, but it is a great first step.

What features are you excited to start evaluating and moving into your production environment?

SQL Server 2016: Query Store Details

The next version of SQL Server is currently available for testing, and it is called SQL Server 2016. One of the new cool features is called the Query Store. It is an interesting feature that should help with query performance troubleshooting. In this article by Borko Novakovic, we get some details on how this new feature is designed to work.

Similar to an airplane’s flight data recorder, Query Store collects and presents detailed historic information about all queries, greatly simplifying performance forensics by reducing the time to diagnose and resolve issues.

Collected data is separated by time windows, allowing you to observe changes in query performance over time. If a query generates different plans, you can use Query Store to analyze the plan changes, identify possible performance degradation, and even force the query processor to use a particular plan for your query.

Query Store is accessible through Transact-SQL. To analyze collected data and manage the feature you must install SQL Server Management Studio or SQL Server Data Tools.

Query Store in SQL Server 2016

SQL Server

SQL Server has the ability to store the current execution plan, but the next version of SQL Server also promises a new feature that also stores the history of a particular query plan. This should be helpful in solving performance problems related to changes in execution plans. This new feature, called the Query Store, can help you with troubleshoot performance problems related to plan changes. It will be available soon on SQL Azure and on the 2016 version of SQL Server. It is expected to be available on the Enterprise Edition of SQL Server 2016, but it is still not yet known if it will be available on other versions like Standard Express editions.

During your troubleshooting you might discover a database query is slow, but the next problem is understanding why a query is slow. Not every query problem is related to plan changes, but the Query Store can help you understand if the plan has changed.

There is a great video from Microsoft that will show you some additional aspects of this new feature.