Transcript SQL Server Evolution SQL 2016 new innovations – Part 2 Lindsey Allen Principal Group Program Manager Borko Novakovic Program Manager.

SQL Server Evolution
SQL 2016 new innovations – Part 2
Lindsey Allen
Principal Group Program Manager
Borko Novakovic
Program Manager
What’s in this session
• SQL 2016 highlights
• Scaling up to new heights – 16 sockets
• In-memory Engine Advances
• Query flight recorder - Query Store
• Time travel and auditing with Temporal database
• Bring Advanced Analytics to your data
• Call to action
Mission critical platform
Performance
Security
Availability
Scalability
Operational analytics
Always Encrypted
Enhanced AlwaysOn
Row level security
• 3 synchronous replicas for
auto failover across domains
Support for Windows
Server 2016
•
Minimize performance impact
running real-time analytics on
transaction data
•
Avoid data sprawl
In-memory OLTP for
more applications
Query Store
Dynamic Data
Masking
• Round robin load balancing
of replicas
• DTC for transactional integrity
across database instances
with AlwaysOn
Enhanced online
operations
12TB 16 Sockets
Demo
SQL scalability on HP superdomeX
Lindsey Allen
In-memory engine
Faster Transactions
IN-MEMORY OLTP
Faster Queries
IN-MEMORY DW
Up to 30x faster transaction
processing with In-Memory OLTP
Over 100x query speed and
significant data compression with
In-Memory ColumnStore
6
Drivers
Architectural Pillars
Customer
Benefits
In-memory OLTP
High performance data
operations
Efficient business-logic
processing
Frictionless scale-up
Hybrid engine and
integrated experience
Main-Memory
Optimized
T-SQL Compiled to
Machine Code
High Concurrency
SQL Server Integration
• Optimized for in-memory
data
• Indexes (hash and range)
exist only in memory
• No buffer pool, B-trees
• Stream-based storage
• T-SQL compiled to machine
code via C code generator
• Invoking a procedure is just
a DLL entry-point
• Aggressive optimizations @
compile-time
• Multi-version optimistic
concurrency control with full
ACID support
• Core engine uses lock-free
algorithms
• No lock manager, latches or
spinlocks
Business
Hardware trends
Steadily declining memory
price, NVRAM
Stalling CPU clock rate
• Same manageability,
administration &
development experience
• Integrated queries &
transactions
• Integrated HA and
backup/restore
Many-core processors
TCO
Columnstore (index)
Data stored as rows
Data stored as columns
C1
C2
C3
C4
C5
Benefits:
•
Improved compression:
Data from same domain
compress better
…
•
Reduced I/O:
Fetch only columns needed
•
Improved Performance:
More data fits in memory
• Updatable NCCI
• In-Memory OLTP +
Column-store
• Faster batch mode
scans using CPU vector
instructions
• Dynamic Aggregate
pushdown
• PK/FK enforcement
• Offload Reporting to
AlwaysOn Secondary
Replica
Demo
Column store
performance
improvement
Lindsey Allen
LINEITEM: 600M rows
ORDERS: 150M rows
CUSTOMER: 15M rows
CUSTOMER_ZIP: 315M rows
Deeper insights across data & Hyperscale Cloud
Access any data
PolyBase
Native JSON
Temporal database
support
Scale and manage
Hybrid solutions
Enterprise-grade
Analysis Services
Stretch tables into Azure
New single SSDT in
Visual Studio 2015
Power Query for analytics
and reporting
Enhanced MDS
Built-in Advanced
Analytics
Enhanced Reporting
Services
Business insights through
rich visualizations on any
mobile device
Enhanced SSIS
Power BI with on-premises
data
Hybrid scenarios with SSIS
• Azure Data Factory integration with SSIS
• Package Lineage and impact analysis
• Connect SSIS to cloud data sources
Enhanced backup to Azure
• X faster restore and 50% reduction in
storage
Easy migration of on-premises
SQL Server
Query Store
Flight data recorder for your database
When performance is not good…
Web site
is down
Database is
not working
Temporary
perf. issues
Impossible
to predict /
root cause
DB
upgraded
Regression
caused by
new bits
With Query Store you CAN…
Find and fix plan
regressions
Identify top
resource
consumers
De-risk SQL
Server upgrade
Deeply analyze
workload
patterns
Demo
Fixing performance regression using
Query Store
Borko Novakovic
Temporal Database
Why temporal?
• Real data sources are dynamic
• Historical data may be critical to business
success
• Traditional databases fail to provide
required insights
• Workarounds are…
• Complex, expensive, limited, inflexible,
inefficient
• SQL Server 2016 makes life
easy
• No change in programming model
• New Insights
SELECT * FROM Department
FOR SYSTEM_TIME
AS OF '2010.01.01'
Azure SQL Database
Demo
Auditing with Temporal database
Borko Novakovic
Built-in advanced analytics
In-database analytics
Example Solutions
Extensibility
• Fraud detection
R Integration
• Sales forecasting
R
• Warehouse efficiency
?
New R scripts
• Predictive maintenance
Analytic Library
01001
0
10010
0
01010
1
Data Scientist
Interact directly with data
01001
0
10010
0
01010
1
T-SQL Interface
Relational Data
Built-in to SQL Server
Data Developer/DBA
Manage data and
analytics together
01001
0
10010
0
01010
1
01001
0
10010
0
01010
1
01001
0
10010
0
01010
1
Microsoft Azure
Machine Learning Marketplace
Demo
Build-in Advanced analytics
Lindsey Allen
Fisher’s Iris flower dataset
machine learning
AML
Gallery
ML
Studio
SSMS /
R
SSRS /
CR
Excel /
PV
Power
BI.com
CTA
•
[TAE8DD] Azure SQL Data Warehouse Overview
•
[T55A62] Microsoft Azure SQL Database: Overview
•
[TC530B] Stretching on-prem databases to cloud
•
[T4D1C9]In-Memory Technologies Overview
•
Polybase in SQL Server Futures - A sneak Peek
•
[TB63B1] In-Memory OLTP Futures
•
[T9F2FD] Overview of Microsoft SQL Server Security Futures
•
Temporal, Query Store and JSON Support in SQL Server Futures
•
[TCFAC2] ColumnStore Index: Microsoft SQL Server 2014 and Beyond
•
[TD4D79] Best Practices for Designing Your Cloud-Based, Data-Tier Strategy
•
[TBD345] APS and Data Warehousing in the cloud - Technical drilldown
•
[TB01EC] Elastic Scale for Microsoft Azure SQL Database
http://myignite.microsoft.com