sql server in-memory oltp migration overview
DESCRIPTION
Are you considering migrating to SQL Server 2014? Are you aware of the advantages of the In-Memory OLTP features for your application workload? In this session we will make an overview of the In-Memory OLTP features and explore possible migration scenarios that can help you understand what the perfect fit is for you. We will demonstrate how you can analyze and implement this new technology to your existing applications and gain the wanted performance without event changing you application code.TRANSCRIPT
![Page 1: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/1.jpg)
www.facebook.com/prpass
SQL Server 2014 In-Memory OLTPMigration Overview
Jose L. RiveraPASS Global Spanish VC LeaderMVP | MCTS | MCITP | MCSA
![Page 2: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/2.jpg)
• Microsoft SQL Server MVP• Líder del Capítulo Global PASS en Español• VP - Puerto Rico PASS
• Profesional de Bases de Datos con mas de 10 años usando SQL Server y tecnologías relacionadas
• Especializado en ETL e Inteligencia de Negocios
• Blog: http://sqlconqueror.com• Email: [email protected]
/SQLConqueror
![Page 3: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/3.jpg)
Weekly Meetings Different Time Zones Spanish speakers from
all over the globe!
/SpanishPASSVC
/user/SpanishPASSVC
/SpanishPASSVC
http://globalspanish.sqlpass.org
![Page 4: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/4.jpg)
Agenda
In-Memory OLTP Overview In-Memory Implementation Migration Scenarios
Q&A
![Page 5: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/5.jpg)
What’s in SQL Server 2014
In-Memory Technologie
s
Hybrid Scenarios
Enhanced HA/DR
Other Investment
s
![Page 6: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/6.jpg)
In-Memory OLTP?
Memory-optimized OLTP engine
High performance
Full integration into SQL Server
Architected for modern hardware
![Page 7: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/7.jpg)
Hardware Trends
More complex CPUs CPU clock rate stalled Parallel processing
RAM is really cheap!
SSD: Minimize IO bottleneck
![Page 8: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/8.jpg)
In-Memory OLTP Benefits
Reduce Contentio
n
Reduce Logging
Minimize execution
time
Lower Latency
![Page 9: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/9.jpg)
Pre-Implementation Requirements
Enough memory and disk space cmpxchg16b instruction support (in
most 64bit processors) Database should not exceed 250 GB At least 2 or 4 sockets and fewer than
60 cores
![Page 10: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/10.jpg)
Limitations
memory-optimized filegroup cannot be deleted (Yes! DROP DATABASE)
No triggers, LOBs, XML, CLR No FOREIGN KEY, CHECK No schema changes, add/remove index
(drop/recreate only)
![Page 11: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/11.jpg)
Memory optimized Structures• Tables, Indexes, SPs
![Page 12: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/12.jpg)
Row Format
Row header Payload (table columns)
Begin Ts End Ts StmtIdIdxLinkCou
nt
8 bytes 8 bytes 4 bytes 2 + 2 (padding) bytes
8 bytes * (IdxLinkCount)
![Page 13: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/13.jpg)
Memory-Optimized Tables
90,150 Susan Bogota
50, ∞ Jane Prague
100, 200 John Paris
70, 90 Susan Brussels
200, ∞ John Beijing
Timestamps NameChain ptrs City
Hash index on
City BP
Row format
BW-tree index on Name
J
S
![Page 14: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/14.jpg)
Memory-Optimized Indexes
90, 150 Susan Bogota
50, ∞ Jane Prague
Timestamps NameChain ptrs City
Hash index on
City
Hash index on Name
100, 200 John Prague
200, ∞ John Beijing
f(John)
f(Jane)
f(Beijing) f(Bogota)
f(Prague)
![Page 15: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/15.jpg)
Table Creation Process
CREATE TABLE DDL
Code generation and compilation
Table DLL produced
Table DLL loaded
![Page 16: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/16.jpg)
Natively Compiled SPs
It is ALL in All tables must be memory optimized
CLR like Execution plan as a result of the TSQL is
compiled into native code (machine code)
![Page 17: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/17.jpg)
SP Creation Process
CREATE PROC DDL
Query optimization
Code generation and compilation
Procedure DLL produced
Procedure DLL loaded
![Page 18: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/18.jpg)
DEMOIn-Memory OLTP BasicsWorkload
![Page 19: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/19.jpg)
Migration Methodology
Analyze Application Know your app Define goals Identify bottlenecks
Test, Test, Test
Examine Gains
![Page 20: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/20.jpg)
In-Memory OLTP PatternsImplementation Scenario
Pattern Characteristics and Challenge
Main Benefits of In-Memory OLTP
High Data Insert Rate
Primarily append-only store Inability to ingest write
workload
Eliminate contention Minimize I/O logging
Read Performance and Scale
High performance read operations
Unable to meet scale-up requirements
Eliminate contention Efficient data retrieval Minimize code execution
timeo CPU efficiency for scale
Compute Heavy Data Processing
Insert/Update/Delete workload Heavy computation inside
database Read and write contention
Eliminate contention Minimize code execution
time Efficient data processing
![Page 21: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/21.jpg)
In-Memory OLTP PatternsImplementation Scenario
Pattern Characteristics and Challenge
Main Benefits of In-Memory OLTP
Low Latency Require low latency business transactions that typical database solutions cannot achieve
High concurrency exacerbates latency
Eliminate contention Minimize code execution
time Efficient data retrieval
Session State Management
Heavy insert, update, and point lookups
User scale under load from multiple stateless web servers
Eliminate contention Efficient data retrieval Optional I/O
reduction/removal
![Page 22: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/22.jpg)
Migration Approach
Identify bottlenecks in tables
Address unsupported
constructs and migrate data
Perform minimal changes for
interpreted access
Identify performance critical
transaction code
Address surface area limitations and
migrate code
![Page 23: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/23.jpg)
Migration Advisors
Memory Optimization Advisor Identifies which tables in your database will
benefit
Native Compilation Advisor Identifies interpreted stored procedures in
your database that will benefit
![Page 24: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/24.jpg)
DEMOARM tool from MDW (Screenshots)
![Page 25: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/25.jpg)
MDW Reports
![Page 26: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/26.jpg)
Transaction Performance Analysis
![Page 27: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/27.jpg)
Table Contention Analysis
![Page 28: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/28.jpg)
MDW reports
![Page 29: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/29.jpg)
DEMOMigration Advisors
![Page 30: SQL Server In-Memory OLTP Migration Overview](https://reader035.vdocuments.us/reader035/viewer/2022062707/55864e5dd8b42a7d3d8b45a4/html5/thumbnails/30.jpg)
/SQLConqueror