SQL Server 2014 In-Memory
Overview
Nilabja Ball
Premier Field Engineer
Microsoft India
Nilabja Ball
 Premier Field Engineer with Microsoft India
 10 years of SQL Server Experience
 Speaker in SQL UG group, Microsoft Tech-Ed
 Closely works with Mission Critical Tier 1
Microsoft Customers
Session Objectives And Takeaways
Understand SQL Server’s in-memory strategy
Be able to identify which technology will best help customers be
successful
SQL Server 2014 Investments
In-Memory
Technologies
In-Memory OLTP
• 5-25x performance
gain for OLTP
integrated into SQL
Server
In-Memory DW
• 5-25x performance
gain and high data
compression
• Updatable and
clustered
SSD Bufferpool
Extension
• 4-10X of RAM and
up to 3X
performance gain
transparently for
apps
Enhanced
High Availability
Always On
Enhancements
• Increased
availability and
improved
manageability of
active
secondaries
Online Database
Operations
• Increased
availability for
index/partition
maintenance
New Hybrid
Scenarios
Backup to Azure
• Easy to implement
and cost effective
Disaster Recovery
solution to Azure
Storage
HA to Azure VM
• Easy to implement
and cost effective
high availability
solution with
Windows Azure
VM
Deploy to Azure
• Deployment
wizard to migrate
database
Other
investments
Better together
with Windows
Server
• WS2012 ReFS
support
• Online resizing
VHDx
• Hyper-V replica
Extending Power
View
• Enable Power
View on existing
analytic models
and support new
multi-dimensional
models.
SQL Server 2014 In-Memory Technology
In-Memory
Technologies
Applicable to
In-Memory OLTP
• 5-20X performance gain
for OLTP integrated into
SQL Server
In-Memory DW
• 5-25X performance gain
and high data
compression
• Updatable and clustered
SSD Bufferpool
Extension
• 4-10X of RAM and up
to 3X performance gain
transparently for apps
Transactional workloads:
Concurrent data entry, processing and retrieval
Applicable to
Decision support workloads:
Large scans and aggregates
Applicable to
Disk-based transactional workloads:
Large working (data)set
Business Trends
Moore’s Law means more
transistors and therefore cores,
but…
CPU clock rate stalled…
Meanwhile RAM
cost continues to
drop
$ per GB of PC Class Memory
US$/GB
1000000
10000
100
1990
1991
1992
1993
1994
1994
1995
1996
1997
1998
1999
2000
2000
2001
2002
2004
2005
2007
2008
2009
2011
1
Chart reference http://www.gotw.ca/publications/concurrency-ddj.htm
Microsoft Confidential
7
Results
Architectural Pillars
Principles
In-Memory OLTP Architectural Pillars
Performance-critical
data fits in memory
Push decisions to
compilation time
Conflicts are Rare
Built-In
Main-Memory
Optimized
T-SQL Compiled to
Native Machine Code
Non-Blocking
Execution
SQL Server
Integration
• Direct pointers to
rows
• Indexes exist only in
memory
• T-SQL compiled to
machine code
• No buffer pool
• Procedure and its
queries, becomes a C
function
• No write-ahead
logging
• Aggressive compiletime optimizations
In-memory cache
speed with capabilities
of a database
Queries & business
logic run at nativecode speed
• Multi-version
optimistic
• Lock-free data
structures
• No locks, latches or
spinlocks
Transactions execute to
completion without
blocking
• Simple Management
• Integrated queries &
transactions
• Integrated HA and
backup/restore
Hybrid engine and
integrated experience
Memory-Optimized Tables
Timestamps Chain ptrs
Name
City
Row format
BW-tree index on
Name
200, ∞
John
J
100, 200
90,150
Susan
S
Susan
John
Paris
Jane
Prague
Bogota
50, ∞
70, 90
Hash index on
City
B
P
Beijing
Brussels
Rows are multi-versioned, each has a valid time range indicated by two
timestamps
A version is visible if transaction read time falls within the version’s valid
time
Row can be part of multiple indexes, but there is only a single copy of the
row
Create Database
10 |
10/3/2015
Footer Goes Here
|
Create Memory Optimized Table
Hash Index
Collation
BIN2
Range Index
This table is durable
This table is
memory optimized
Microsoft Confidential
11
Natively Compiled Procedures
This proc is natively compiled
Native procs must be schemabound
Execution context is required
Atomic blocks
• Create a transaction if
there is none
• Otherwise, create a
savepoint
Session settings are fixed at
create time
Microsoft Confidential
12
Demo on In-Memory OLTP Table
SQL Engine With In-Memory
Types of candidate workloads
Application has OLTP characteristics
Relatively short transactions
High degree of concurrency, transactions from many sessions
Suited for in-memory processing
Critical path data can fit in memory
Currently encounter locking or latching bottlenecks
Code Characteristics
Stored procedures used
Code can be modified and isolated to subset of tables or
stored procedures
Microsoft Confidential
15
Memory Optimized Table Limitations
.
•
•
•
•
•
•
•
16 |
No FOREIGN KEY and no CHECK constraints
No schema changes (ALTER TABLE) – need to drop/recreate table
No add/remove index – need to drop/recreate table
No DML triggers
No XML and no CLR data types
Rows are at most 8060 bytes – no off row data
No Large Object (LOB) types like varchar(max)
10/3/2015
SQL Server 2014: In-Memory Overview
|
We want your feedback
Each complete feedback form gives
you a chance at the raffle
Our Gold Sponsors
Microsoft Sri Lanka
Brought to you by:
Pluralsight
Microsoft Corporation
Descargar

Document