SQL Server 2016 New Performance Features

SQL Server 2016 New Performance Features

I have already written about SQL Internals improvements on a different post. With SQL Server 2016, Microsoft has continued to invest in three major areas. They have continued to evolve the product on mission critical performance, with SQL Server 2014. Microsoft began to take the lead away from Tier 1 vendors & (Like Oracle, SAP) competitors with in-memory technology built-in. This release (SQL 2016) has further added new innovations across many mission critical components. With investments on data insights, significant investment both on premises and complimentary services via Azure has added to help you gain deeper insights across your data.  Finally Microsoft is adding new hybrid capabilities that will compliment your on-prem investments and give you the ability to take advantage of Microsoft’s hyperscale Azure cloud.

Operational Analytics

Benefits

• No or minimal data latency
• No need to setup ETL, directly query SQL Tables without significant performance impact.
• No additional investments or a need to setup a separate data warehouse for reporting needs.
• Data Warehouse queries can be run on in-memory OLTP workload
  with no application changes. These operations have minimal impact on OLTP workload.

CHALLENGES

• Analytics queries are highly resource intensive and can cause blocking issues.
• Minimizing impact on operational workloads
• Sub-optimal execution of analytics on relational schema

Examples

• Enterprise resource planning (ERP): Inventory, orders, and sales
• Machine or Factory data from operations on the factory floor
• Online stores such as Amazon, Flipkart or Snapdeal etc.
• Stock market & derivatives trading Data.

Using Availability Groups instead of data warehouse

You can now Load balance Always On availability groups for Mission Critical Operational Workloads typically configured for High Availability.

You can offload analytics to readable secondary replica

Support for Index Maintenance

source: https://msdn.microsoft.com/en-us/library/dn935013(v=sql.130).aspx

In-memory OLTP enhancements

ALTER support

• SQL 2016 now provides full schema change support: add/alter/drop column/constraint
• Add/drop index supported is now supported.

Surface area improvements

• Almost full T-SQL coverage including scaler user-defined functions
• Improved scaling and bigger hard limits. In-memory OLTP engine has been enhanced to scale linearly on servers up to 4 sockets
• Increased size allowed for durable tables; more sockets

Other improvements

• MARS support. SQL Server 2005 introduced support for multiple active result sets (MARS) in applications accessing the Database Engine.

Setup MARS connection for memory optimized tables using the MultipleActiveResultsSets=True in your connection string

• Lightweight migration reports

Support for Transparent Data Encryption

• In SQL Server 2016 CTP2, the storage for memory-optimized tables will be encrypted as part of enabling TDE on the database
• The process to enable is the same. Simply follow the same steps as you would for a disk-based database

Management Data Warehouse (MDW) is now depreciated and now has been replaced with lightweight reports available in SSMS without setting up MDW.

Query Store

• Query store provides DBAs with data insight on query plan choice and performance.
• Simplified performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans.
• You can use Query plan to automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.
• Unlike DMV’s which shows you point in time information, Query Store separates data by time dimension, allowing you to see database usage patterns and understand when query plan changes happened on the server. 

With Query Store you can:

•  get full history of query execution
•  quickly pinpoint the most expensive queries
•  get all queries that regressed
• easily force better plan from history with a single line of T-SQL
• safely do server restart or upgrade

Here is the management dashboard

Natively supporting Java Script Object Notation (JSON)

• SQL 2016 can now natively accept JSON, easily parse and store it in a relational format.
• You can export relational easily as JSON
• You can correlate relational and non-relational data.
• Azure DocumentDB integration.See TechNet Virtual Lab “Exploring SQL Server 2016 support for JSON data” – http://go.microsoft.com/?linkid=9898458

Temporal

Query back in time

How system-time works?

Source: https://msdn.microsoft.com/en-us/library/dn935015(v=sql.130).aspx

You can query point in time:

SELECT * FROM Department FOR SYSTEM_TIME

AS OF ‘2006.01.01’

In the next post, I will talk about the security enhancements in SQL Server 2016.