sql server 2016 new features

2016Compatibility level 130 Launch Event, Tehran-IRAN, June 01, 2016

Microsoft SQL Server 2016

About me@mesbahi

Amin MesbahiSenior Database Architect, Consultant and Instructor• Banking and Financial databases• National Projects with nation-wide distribution• High Concurrency Solutions• Mission-Critical Servers

Email: [email protected]: Mesbahi.netLinkedIn: ir.linkedin.com/in/aminmesbahi


AgendaWhat we’ll cover?

• Introduction• SQL Server History• Today’s SQL Server Position• Installation for SQL Server 2016• What's New in Database Engine• An Overview on:• What's New in Reporting Services• What's New in SQL Server R Services


Introduction:An overview on SQL Server 2016

• No more 32-bit Edition

• SQL Server Management Tools Installation is separated from server Installation

• Developer Edition is now FREE • Upgrade from at least SQL Server 2008 SP3

?

https://msdn.microsoft.com/en-us/library/cc645993.aspx

Microsoft SQL Server 2016 An overview on SQL Server 2016

• PROS Holdings uses SQL Server 2016’s superior performance and built-in R Service to

deliver advanced analytics more than 100x faster than before

• Load a complex schema derived from TPC-H at 1.6TB/hour, and it took just 5.3 seconds to

run a complex query (the minimum cost supplier query) on the entire 100TB database.

• For six years running, SQL Server has had the least vulnerabilities of any of the major

database platforms, based on NIST Report.


SQL Server Position among Competitors

SQL Server Position in the market

Microsoft SQL Server 2016What's New in Database Engine?

Database Engine new features

• Database Engine Feature Enhancements

• T-SQL Enhancements

• System View Enhancements

• Security Enhancements

• High Availability Enhancements

• Replication Enhancements

• Tools Enhancements


What’s Columnstore Indexesand it’s enhancements in SQL Server 2016?

Columnstore Indexes

Columnar storage

Batch Mode Processing

Memory Optimized

Compression

Rowgroups and Segment Elimination

Microsoft SQL Server 2016 Columnstore Indexes

Columnstore Index Feature

SQL Server 2012 SQL Server 2014 SQL Server 2016 SQL Database V12

Premium EditionSQL Data

Warehouse

Batch execution for multi-threaded queries

yes yes yes yes yes

Batch execution for single-threaded queries

yes yes yes

Archival compression option.

yes yes yes yes

Snapshot isolation and read-committed snapshot isolation

yes yes yes

Specify columnstore index when creating a table.

yes yes yes

What happened from 2012 to 2016?

Microsoft SQL Server 2016 Columnstore IndexesWhat happened from 2012 to 2016?

Columnstore Index Feature SQL Server 2012 SQL Server 2014 SQL Server 2016 SQL Database V12

Premium Edition SQL Data Warehouse

AlwaysOn supports columnstore indexes.

yes yes yes yes yes

AlwaysOn readable secondary supports read-only nonclustered columnstore index

yes yes yes yes yes

AlwaysOn readable secondary supports updateable columnstore indexes.

yes

Read-only nonclustered columnstore index on heap or btree.

yes yes yes* yes* yes*

Updateable nonclustered columnstore index on heap or btree

yes yes yes

Additional btree indexes allowed on a heap or btree that has a nonclustered columnstore index.

yes yes yes yes yes

Microsoft SQL Server 2016 Columnstore IndexesWhat happened from 2012 to 2016?

Columnstore Index Feature SQL Server 2012 SQL Server 2014 SQL Server 2016 SQL Database V12

Premium Edition SQL Data Warehouse

Updateable clustered columnstore index.

yes yes yes yes

Btree index on a clustered columnstore index.

yes yes yes

Columnstore index on a memory-optimized table.

yes yes yes

Nonclustered columnstore index definition supports using a filtered condition.

yes yes yes

Compression delay option for columnstore indexes in CREATE TABLE and ALTER TABLE.

yes yes yes

Microsoft SQL Server 2016 Columnstore Indexes

These are new in 2016 Updatable nonclustered columnstore index: A read-only nonclustered columnstore index is updateable after upgrade. A rebuild of the index is not required to make it updateable.

There are performance improvements for analytics queries on columnstore indexes, especially for aggregates and string predicates.

An in-memory table can have one columnstore index.

A clustered columnstore index can have one or more nonclustered rowstore indexes.

Support for primary keys and foreign keys by using a btree index to enforce these constraints on a clustered columnstore index.

Columnstore indexes have a compression delay option that minimizes the impact the transactional workload can have on real-time operational analytics.

?

https://msdn.microsoft.com/en-us/library/dn934994.aspx

Microsoft SQL Server 2016 In-Memory OLTP

Introduction to InMemory OLTP Where we are spending more execution time?

Here are the results from Microsoft analysis.

I/O, Thread Management Storage Engine Relational Engine Communication

Stack

10% 80% 10%Access Methods,

Transaction, Lock, Log, Managers

T-SQL Interpreter, Query Execution, Expressions


2000 2000 2001 2001 2002 2002 2003 2004 2006 2006 2007 2008 2009 2009 2010 2011 2012 2013 2013 20140

200

400

600

800

1000

1200

1400

1600

1800

$/1000 Mb

$/1000 Mb

SQL 2005SQL 2008 R2

SQL 2008 SQL 2012SQL 2014

SQL 2000


Hekaton Project - Greek word ἑκατόν (Houndred).

The objective is to improve 100x the performance. Traditional strategy relies in structures focused on data stored in disk.

By fully using memory capabilities we can have simpler structures.

Available from SQL Server 2014.

Highly improved on SQL Server 2016.


ArchitectureClient Application

Tabular Data Stream (TDS) Handler / Session Management

T-SQL Execution

Buffer Pool for Tables and Indexes

Parser, Catalog and Optimizer

InMemory Native Compiler

Storage Engine for Memory Optimized Tables and Indexes

Native Compiled Stored Procedures

and Schema

Sqlserv.exe

Memory Optimized Table Filegroup Transaction Log Data Filegroup

Generated DLL

InMemory Component

Existing SQL Component

Checkpoint Files / Recovery

Query interoperability


Why Hekaton is faster?

Compiled objects. All the steps to interpret code are avoided.


Improvements: Supports 2TB of durable tables (2014 -> 256GB)

LOBs with large row size for a memory-optimized table Transact-SQL Improvements for memory-optimized tables

UNIQUE indexes, FOREIGN KEY references, CHECK constraints, TRIGGERs A non-unique index can allow NULL values in its key Increased support for Altering Memory-Optimized Tables Natively compiled scalar UDFs, {LEFT|RIGHT} OUTER JOIN, Disjunction (OR, NOT)

OUTPUT clause in natively compiled stored procedures Reduced downtime during upgrade support for TDE

Microsoft SQL Server 2016 Query Store, find performance differences caused by changes in query plans

Query Store:

Microsoft SQL Server 2016 Query Store

Query Store:• support for natively compiled code from In-Memory OLTP workloads

• automatically captures a history of queries, plans, and runtime

statistics, and retains these for your review.

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

Microsoft SQL Server 2016 Query Store

Query Store:

Microsoft SQL Server 2016 Live Query Statistics

Live Query Statistics:

Microsoft SQL Server 2016 Live Query Statistics

Microsoft SQL Server 2016 Temporal Tables: correct information about stored facts at any point in time

Temporal Tables:• temporal table consists of two tables

actually, one for the current data and one for

the historical data.

• It’s new type of user table in SQL Server 2016

• Auditing all data changes

• reconstructing state of the data as of any

time in the past

• Calculating trends over time

• Maintaining a slowly changing dimension for

decision support applications

• Recovering from accidental data changes and

application errors

Microsoft SQL Server 2016 Temporal Tables

How does temporal work?

CREATE TABLE dbo.Employee ( [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED , [Name] nvarchar(100) NOT NULL , [Position] varchar(100) NOT NULL , [Department] varchar(100) NOT NULL , [Address] nvarchar(1024) NOT NULL , [AnnualSalary] decimal (10,2) NOT NULL , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Microsoft SQL Server 2016 Temporal Tables

How do I query temporal data?

SELECT * FROM Employee FOR SYSTEM_TIME BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000' WHERE EmployeeID = 1000 ORDER BY ValidFrom;

Microsoft SQL Server 2016 RLS, Security on your records, not only columns

Row Level Security:Row-Level Security (RLS) restricts which users can view what data in a table, based on a function.

Microsoft SQL Server 2016 Row Level Security

Row Level Security:

Microsoft SQL Server 2016 Always Encrypted, Better Security

Always Encrypted:• Data encrypted in flight and at rest

• Better than TDE, encrypted for all users,

even admins.

• Deterministic or Randomized Encryption

• .Net Framework version 4.6 or higher

all the encryption key resides with the application inside the customer’s trusted environment and not on the server.


Dynamic Data Masking:

Microsoft SQL Server 2016 JSON (JavaScript Object Notation)

{JSON}

4 Native functions:• ISJSON -Tests whether a string contains valid JSON.

• JSON_VALUE –Extracts the value from JSON.

• JSON_QUERY –Extracts subquery from JSON.

• OPEN_JSON -Table value function that parses JSON text and returns rowsetview of JSON.

Select IsJSON('{"name":“Amin",“family":“Mesbahi","age":X}')

Select * from OpenJSON('{"name":“Jack",“family":“JackZadeh","age":38}‘)

Microsoft SQL Server 2016 Stretch Database with Microsoft Azure

Stretch Database:


Real-time Operational Analytics:in-memory column store + in-memory OLTP = Real-time Operational Analytics

Microsoft SQL Server 2016 Better T-SQL

Transact-SQL Enhancements:• The maximum index key size for NONCLUSTERED indexes has been increased to 1700 bytes

• TRUNCATE TABLE statement now permits the truncation of specified partitions

• ALTER TABLE now allows many alter column actions to be performed while the table remains available

• The full-text index DMV sys.dm_fts_index_keywords_position_by_document returns the location of keywords in documents

• A new query hint NO_PERFORMANCE_SPOOL can prevent a spool operator from being added to query plans

• New DROP IF syntax is added for drop statements

• SESSION_CONTEXT can now be set EXEC sp_set_session_context 'user_id', 4; SELECT SESSION_CONTEXT(N'user_id');

• NEW: STRING_SPLIT and STRING_ESCAPE

• The COMPRESS and DECOMPRESS functions convert values into and out of the GZIP algorithm

• NEW: DATEDIFF_BIG and AT TIME ZONE functions and the sys.time_zone_info

• Eight new properties are added to SERVERPROPERTY

• The input length limit of 8,000 bytes for the HASHBYTES function is removed

Microsoft SQL Server 2016 PolyBase

PolyBasePolyBase is a technology that accesses and combines both non-relational and relational data, all from within SQL Server.

SELECT * FROM [dbo].[SensorData] WHERE Speed > 65 OPTION (FORCE EXTERNALPUSHDOWN);

Microsoft SQL Server 2016 Other Features

• Striped Backups to Microsoft Azure Blob Storage• File-Snapshot Backups to Microsoft Azure Blob Storage• Managed Backup• Replication is now supported to Azure SQL Database• Stretch Database

• Migrates your historical data transparently and securely to the Microsoft azure cloud• Trace flag 4199 behaviors are enabled

• do not need to use trace flag 4199 in SQL Server 2016 since most of the query optimizer behaviors

• TempDB Database• Foreign Key Relationship Limits

• The limit for the number of other table and columns that can reference columns in a single table, from 253 to 10,000.

• Support for UTF-8• bcp Utility, BULK INSERT, and OPENROWSET now support the UTF-8 code page.

• More CPU Friendly Transparent Data Encryption• AES Encryption for Endpoints• Database scoped credential• Replication of memory-optimized tables

Microsoft SQL Server 2016 HA

High Availability:• SQL Server 2016 Standard Edition now supports Always On Basic Availability Groups

• Load-balancing of read-intent connection requests is now supported across a set of read-only replicas

• The number of replicas that support automatic failover has been increased from two to three

• Group Managed Service Accounts are now supported for Always On Failover Clusters

• Always On Availability Groups supports distributed transactions and the DTC on Windows Server 2016

• You can now configure Always On Availability Groups to failover when a database goes offline

• Always On now supports encrypted databases

• Two availability groups in two separate Windows Server Failover Clusters (WSFC) can now be combined

into a Distributed Availability Group

• Direct seeding allows a secondary replica to be automatically seeded over the network

Microsoft SQL Server 2016 R, The Data Scientists Tool in SQL Server

RYou can continue to work with your favorite R or SQL tools, but scale analysis to billions of records without additional hardware, boost performance, and avoid unnecessary data movements

Microsoft SQL Server 2016What's New in Reporting Services?

SSRS new features

• Reporting Services web portal

• Custom branding for the web portal

• Key performance indicators (KPI) in the web portal

• Mobile Reports

• Mobile Report Publisher

• PDF Replaces ActiveX for Remote Printing

• PowerPoint Rendering and Export

• Pin Report Items to a Power BI Dashboard

• HTML 5 Rendering Engine


Thank You

Q&A

sql server 2016 new features

Software