Transcript SQL Server 2014 - Center

| Lausanne
SQL Server 2014:
les nouveautés
Dung T. Thoi
TSP Data Platform
Microsoft Suisse
SQL Server 2014 and the data platform
Microsoft’s In-Memory Technologies
•
•
•
•
•
•
2014
2014
2012
2012
2014
In-Memory OLTP –
Hekaton
Check out the dedicated session
Berlin, 16:40
Hardware Trends
Moore’s Law means more transistors
and therefore cores, but…
CPU clock rate stalled…
because processors would melt…
Meanwhile RAM cost
continues to drop
$ per GB of PC Class Memory
US$/GB
1000000
10000
100
1
11
21
31
41
51
61
71
81
91
101
111
121
131
141
151
161
171
181
191
201
1
http://www.gotw.ca/publications/concurrency-ddj.htm
Drivers
In-Memory OLTP Tech
Pillars
Benefits
In-Memory OLTP Architecture
High Performance
Data Operations
Frictionless Scale-Up
Efficient, BusinessLogic Processing
Hybrid Engine and
Integrated Experience
Main-Memory
Optimized
High Concurrency
T-SQL Compiled to
Machine Code
SQL Server Integration
• Optimized for in-memory
data
• Indexes (hash and range)
exist only in memory
• No buffer pool
• Stream-based storage for
durability
• Multi-version optimistic
concurrency control with
full ACID support
• Core engine uses lock-free
algorithms
• No lock manager, latches
or spinlocks
• T-SQL compiled to machine
code via C code generator
and VC
• Invoking a procedure is just
a DLL entry-point
• Aggressive optimizations @
compile-time
• Same manageability,
administration &
development experience
• Integrated queries &
transactions
• Integrated HA and
backup/restore
Business
Hardware Trends
Steadily declining memory
price, NVRAM
Many-core processors
Stalling CPU clock rate
TCO
Demo
In-Memory OLTP – Project “Hekaton”
In-Memory Data Warehouse:
ColumnStore
In-Memory In the Data Warehouse
In-Memory ColumnStore
Both memory and disk
Built-in to core RDBMS engine
“By using SQL Server 2012 In-Memory
ColumnStore, we were able to extract about
100 million records in 2 or 3 seconds versus
the 30 minutes required previously. “
- Atsuo Nakajima Asst Director, Bank of Nagoya
Customer Benefits:
‐ 10-100x faster
‐ Reduced design effort
‐ Work on customers’ existing hardware
‐ Easy upgrade; Easy deployment
Columnstore
Index
Representation
C C C C C C
1 2 3 4 5 6
10
Demo
In-Memory ColumnStore – Project “Apollo”
ColumnStore
Jacques
Dupont
M
Jeanne
Dubois
F
Paul
Dupuis
M
Jean
Dutoit
M
Jacques
Durand
M
Jeanne
Dupont
F
Isabelle
Dupuis
F
Alex
Dumont
M
In-Memory DW Storage Model
Data Stored Column-wise
Each page stores data from a single column
C1
C2
C3
C4
C5
C6
Highly compressed
‐ More data fits in memory
Each column can be accessed independently
‐ Fetch only columns needed
‐ Can dramatically decrease I/O
13
Batch Mode
Improving CPU Utilization
Biggest advancement in query processing in years!
Data moves in batch through query plan operators
‐ Minimizes instructions per row
‐ Takes advantage of cache structures
Highly efficient algorithms
Better parallelism
14
Batch Mode Processing
QP Vector Operators
Batch object
Process ~1000 rows at a time
Column vectors
Optimized to fit in cache
Vector operators implemented
Filter, hash join, hash aggregation, …
Greatly reduced CPU time (7 to 40X)
bitmap of qualifying rows
Batch stored in vector form
15
Demo
(CONT’ED)
In-Memory ColumnStore – Project “Apollo”
In-Memory DW 2014:
Clustered & Updatable
Fast execution for data warehouse queries
‐ Speedups of 10x and more
No need for separate base table
‐ Save space
Data can be inserted, updated or deleted
‐ Simpler management
Eliminate need for other indexes
‐ Save space and simpler management
More data types supported
17
Comparing Space Savings
101 Million Row Table + Index Space
19.7GB
10.9GB
6.9GB
5.0GB
4.0GB
1.8GB
1
2
3
4
5
6
18
Structure of In-Memory DW
How It Works
CREATE CLUSTERED COLUMNSTORE
‐ Organizes and compresses data into columnstore
Partition
ColumnStore
Deleted
Bitmap
BULK INSERT
‐ Creates new columnstore row groups
INSERT
‐ Rows are placed in the row store (heap)
‐ When row store is big enough, a new columnstore row
group is created
Row Store
19
Structure of In-Memory DW
How It Works (cont'd)
DELETE
‐ Rows are marked in the deleted bitmap
Partition
ColumnStore
UPDATE
‐ Delete plus insert
Most data is in columnstore format
Row Store
Deleted
Bitmap
Batch Mode Processing
What’s New?
SQL Server 2014
‐ Support for all flavors of JOINs
 OUTER JOIN
 Semi-join: IN, NOT IN
‐ UNION ALL
‐ Scalar aggregates
‐ Mixed mode plans
‐ Improvements in bitmaps, spill support, …
21
AlwaysOn 2014
AlwaysOn
Replace Database Mirroring and Log Shipping with Availability Group
Windows Server Failover Cluster
Primary Data Center
Disaster Recovery Data Center
Availability
Group
Fileshare Witness
Primary
Secondary
Synchronous
Synchronous / Asynchronous
Secondary
Increase number of Availability Group secondaries
Description
• Increase number of secondaries (4–8)
• Max number of sync secondaries is still two
Reason
• Customers want to use readable secondaries
One technology to configure and manage
Many times faster than replication
• Customers are asking for more database replicas (4–8)
To reduce query latency (large-scale
environments)
To scale out read workloads
Support for Windows Server Cluster Shared
Volumes
Description
Allow FCI customers to configure CSV paths for system and user databases
Reason
Avoid drive letter limitation on SAN (max 24 drives)
Improves SAN storage utilization and management
Increased resiliency of storage failover (abstraction of temporary disklevel failures)
Migration of SQL Server customers using PolyServe (to be discontinued in
2013)
Demo
AlwaysOn
Windows Server 2012/R2: High Performance Storage
SMB3 Protocol
Infiniband
40Gb/s
Infiniband
40Gb/s
SQL Servers
FCI or standalone
Windows File
Server 2012
Redundant
Connection
SQL transactions/s
•
•
•
•
Direct Attached
File share
Oh, yes, Fiber Channel too 
TemDB on local disk
6000
4000
2000
4270
(98%)
4315
(100%)
2
3
1175
(27%)
0
1
Other Engine
Enhancements
SSD Buffer Pool Extension and scale up
• What’s being delivered
• Use of non-volatile drives (SSD) to extend buffer pool
• NUMA-Aware large page and BUF array allocation
• Main benefits
• BP extension for SSDs
• Improve OLTP query performance with no application changes
• No risk of data loss (using clean pages only)
• Easy configuration optimized for OLTP workloads on commodity servers (32 GB RAM)
Example:
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'F:\SSDCACHE\EXAMPLE.BPE‘,
SIZE = 50 GB)
• Scalability improvements for systems with more than eight sockets
Query processing enhancements
• What’s being delivered
• New cardinality estimator
• Incremental statistics for partition
• Parallel SELECT INTO
• Main benefits
• Better query performance:
• Better choice of query plans
• Faster and more frequent statistics refresh on partition level
• Consistent query performance
• Better supportability using two steps (decision making and execution) to enable better query plan
troubleshooting
• Loading speed into table improved significantly using parallel operation
Complete Resource Governance
• What’s being delivered
• Add max/min IOPS per volume to Resource Governor pools
• Add DMVs and perfcounters for IO statistics per pool per volume
• Update SSMS Intellisense for new T-SQL
• Update SMO and DOM for new T-SQL and objects
• Main benefits
• Better isolation (CPU, memory, and IO) for multitenant workloads
• Guarantee performance in private cloud and hosters scenario
Security
Separation of duties enhancement
• Four new permissions
•
•
•
•
CONNECT ANY DATABASE (server scope)
IMPERSONATE ANY LOGIN (server scope)
SELECT ALL USER SECURABLES (server scope)
ALTER ANY DATABASE EVEN SESSION (database scope)
• Main benefit
• Greater role separation to restrict multiple DBA roles
• Ability to create new roles for database administrators who are not sysadmin (super user)
• Ability to create new roles for users or apps with specific purposes
Backup Encryption
Increase security of backups stored separate from the instance
(another environment such as the Cloud)
Encryption keys can be stored on-prem while backup files in the
cloud
Support non-encrypted databases (don’t need to turn on Transparent
Data Encryption anymore)
Different policies for databases and their backups
SQL Server 2014 and the Data Platform
Microsoft Power BI for Office 365
1 Billion Office Users
Discover
Analyze
1 in 4 enterprise customers on Office 365
Visualize
Share
Find
Q&A
Scalable | Manageable | Trusted
Mobile
Powerful Self-Service BI with Excel 2013
Powerful Self-Service BI with Excel 2013
Powerful Self-Service BI with Excel 2013
Powerful Self-Service BI with Excel 2013
SQL Server 2014 and the Data Platform
Check out the BI with SharePoint on Azure session, Rome, 16:40
Hybrid Cloud Solutions
Geo Replication
Restore to VM
Cloud Backup (SQL Server 2012 SP1 CU2)
Windows Azure
VM
Windows Azure
Storage
Onsite/Offsite storage costs
Device management costs
•
•
•
XDrives limited to 1 TB
Max 16 drives
Manage drives and policy
•
•
•
•
•
•
Near “bottomless” storage
Off-site, geo-redundant
No provisioning
No device management
Media safety (decay free)
Remote accessibility
Windows Azure
Blobs
•
•
WA Box
On-premises box
Demo
Cloud Backup & Backup Encryption
Deploy dB to Windows Azure VM
• What’s being delivered
• New wizard to deploy dB to SQL Server
in Azure VM
• Main benefits
• Easy to use
• Perfect for DBAs new to Azure and for ad hoc
scenarios
• Complexity hidden
• Detailed Azure knowledge is not needed
• Almost no overhead: the defining factor for
time to transfer is dB size
AlwaysOn Availability Group Spanning
On-premises and Azure
redmond.corp.microsoft.com
Availability Group
uswest.internal.cloudapp.net
Virtual
Network
VPN Device
Windows Cluster
Demo
AlwaysOn on Azure
Complete & Consistent Data
Platform
Download SQL Server 2014 CTP1
Call to action
Stay tuned for availability
www.microsoft.com/sqlserver
Backup Slides
In-Memory OLTP
New High performance, memory-optimized OLTP engine
integrated into SQL Server and architected for modern
hardware trends.
Integrated into SQL Server relational database
Full ACID support
Memory-optimized indexes (no BTrees, buffer pools)
Non blocking multi-version optimistic concurrency control (no
locks/latches)
T-SQL compiled to native code
The Application
SIP (Stock Information Processing) – takes high volume
real-time trade data and publishes market stock price
quotes after consolidating bids/asks, requiring
predictable <0.5msec low latency
In-Memory OLTP Solution
The application achieves a predictable 0.4msec end to
end – due to the lack of contention on the table, and
shorter path length from natively compiled code
Chart Title
Customer Benefits:
•
•
•
•
Low latency
Up to 10x improvement in performance
2-5x improvement in scalability
Leverage investments in SQL Server
3
2
Series1
1
0
0.5
1
1.5
53
In-Memory DW Index Structure
Row Groups & Segments
Segments
A segment contains values for one column for a set of rows
C1
C
2
C
3
C4
C5
C6
Segments for the same set of rows comprise a row group
Segments are compressed
Each segment stored in a separate LOB
Segment is unit of transfer between disk and memory
Row
grou
p
54
C1
C2
C3
C4
C
5
Table consists of column store and row store
DML (update, delete, insert) operations leverage delta store
C
6
INSERT Values
C1
C2
C3
C4
C5
Always lands into delta store
C6
DELETE
tuple mover
Column
Store
Delta (row)
store
Updatable Columnstore Index
Logical operation
Data physically remove after REBUILD operation is performed.
UPDATE
DELETE followed by INSERT.
BULK INSERT
if batch < 100k, inserts go into delta store, otherwise columnstore
SELECT
Unifies data from Column and Row stores - internal UNION operation.
“Tuple mover” converts data into columnar format once segment is
full (1M of rows)
REORGANIZE statement forces tuple mover to start.
Resource Governor goals
Ability to differentiate workloads
Ability to monitor resource usage per group
Limit controls to enable throttled execution or prevent/minimize
probability of “run-aways”
Prioritize workloads
Provide predictable execution of workloads
Specify resource boundaries between workloads
Resource Governor components
HDInsight and Hadoop Ecosystem
Distributed Processing
(MapReduce)
Distributed Storage
(HDFS)
ODBC
Query
(Hive)
Legend
Red = Core
Hadoop
Blue = Data
processing
Gray= Microsoft
integration
points and
value adds
Orange = Data
Movement
Green =
Packages
| Lausanne
Chapter 1/1
Tips for using this deck.
Tips for using this deck
Put main items in paragraph color.
For subitems, just increase the list/indent level.
 Bullets appear on the third level of indentation.
Turn on the visual Guides to help place objects.
View tab  Guides.
Tips for using this deck
Use the layouts provided in the Slide Master.
Easily add Chapter and Section slides to group your content.
Choose a Title Image.
Apply the Title Slide Layout of your choice.