1 Query Store
a) Captures queries, query plans, runtime
statistics, etc.
b) Can enforce polices to direct SQL Server Query
processor to execute in specific manner
c) Contains query work load history
d) Helps system-wide or database level
performance analysis and troubleshooting
e) New system views are added to support Query
store
2 Live Query Statistics
a) Live Query plans with system resources
consumption such as CPU, memory for all running queries in the plan
b) You can drill down further into live running
queries from its query plan
c) Helps you watch change of statistics during
query execution
3 Native JSON Support
a) Language - Independent data-interchange format
b) Modern Web and Mobile Applications are using
it
c) Alternate XML
d) Parsing done via Standard JavaScript function
e) Easier and faster than XML parsing
f) Several new T-SQL constructs introduced to
facilitate JSON
4 Temporal Database support
a) Keep track data in the database (Current and
historical)
b) Provides ability to query the table snapshot
for any point in time
c) Data can be restored to any known state
without downtime
d) Help regulate compliance and audit
e) Help implementing slowly changing dimension
5 Always Encrypted
a) Encrypted data at rest and in motion
b) Encryption master key resides at Application
c) No application change required (Simple SSIS
Package convergence)
6 Row Level Security
a) Security can be defined on a complete row
b)
7 Column Store
a)
Column store index on
your in-memory table for higher throughput
b)
8 Polybase
a) Querying relational
and non-relational data
b) Example: SQL +
Hadoop (With the help of external table)
9 Advance Analytics
a) Data Scientists can get model or templates
from MS Azure where others are sharing and
hook it up with their
real time data for data examining
10 BI on Mobile Devices
a) All BI on Mobile: (New) – DataZen App
I. Windows
II. iOS
III. Android
IV. HTML5
11 Data Stretch to MS Azure
a) Stretch the data to the cloud (Turn the
feature on and it works with Always Encrypted)
a. Remote data archive (DBA)
b. Stretch tables into Azure
c. Power BI Scenarios with SSIS
b) Enhanced Backup/restore for stretched
databases
12 SSDT (All in One)
SQL Server 2016 Enhancements from SQL Server 2014
Here is the summary for enhanced features of SQL Server 2016
from SQL Server 2014
1- Performance
a) In-memory
OLTP
I. Altering in memory table is now
allowed (2014, you needed to recreate the table)
II. More size of durable tables
III. Migration of tables and stored
procedures to in-memory report is added in SSMS standard reports and doesn’t
require management data warehouse any more
b) Query
Processing
I. Better Query plan choices and
more accurate cardinality estimates
II. Faster and more frequent Stats
updates are possible
III. Parallel insertion is possible
with select into operation
c) Data
and backup compression
I. Compression level has increased
for database as well as backup
d) Data
collector
I. Improved DMVs, SQL Traces,
Server activity, disk usage, and query usage
II. Improved database tuning
advisor (Indexes, views and partitions can be created using database tuning
advisor)
III. Performance reports are added
in standard reports
2- Security
a) Transparent
Data Encryption (TDE)
I. TDE now supports storage of
memory optimized tables
II. Tables and column level masking
is improved
b) Backup
Encryption
I. Backup encryption is now
supported with compression
c) SQL
Server Audit Tools
I. Underlying operation can be
audited now besides just permission audit
3- Availability
a) Availability
of Mission Critical systems
I. Load balancing between Primary
and Secondary replica
II. Auto Failover from 2 to 3
III. DTC tied to database instead of
instance
IV. AG health enhanced to database
level instead of only instance level
b) Online
database Operations
I. No blocking for alter and
truncate operations (100% uptime for tables during these operations)
c) Data
backup enhancement
I. Enhanced GUI for backup/restore
operation
4- Scalability
a) Windows
and SQL Server enhancements
I. Supported on Windows server
Core (less patches, less maintenance and less downtime)
II. Support for windows server
ReFS(Resilient File System)
III. Faster live migration for SQL
VM
IV. Cluster-Aware Updating improved
V. Dynamic Quorum
b) Buffer
Pool Extension
I. Buffer pool can now use SSDs
(Solid-state Drives)
c) Partitioning
I. Scale upto 1500 table
partitions (helps to deal with Large data sets which applications such as SAP
uses daily or hourly)
d) Distributed
Replay
I. Multithreaded replay is
introduced as opposed to single threaded replay for production get ready
planning
e) Tempdb
Optimization
I. Multiple TempDB files per
instance for multi-core environments
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
