sql server 2014 adds in-memory technology to boost performance of oltp workloads
TRANSCRIPT
![Page 1: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/1.jpg)
![Page 2: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/2.jpg)
SQL Server In-Memory OLTP: Developer Deep DiveJos de Bruijn
DBI-B307
![Page 3: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/3.jpg)
Memory optimized table and index structures
Native compilation of business logic in stored procedures
Fully integrated into SQL Server
Latch- and lock-free data structures
In-Memory OLTP RecapSQL Server 2014 adds in-memory technology to boost performance of OLTP workloads
![Page 4: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/4.jpg)
In-Memory OLTP for DB Developers
Memory optimized tables and indexes
Accessing memory-optimized tables
Transactions on memory optimized tables
![Page 5: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/5.jpg)
Memory optimized tables and indexes
![Page 6: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/6.jpg)
Create Table DDLCREATE TABLE [Customer]( [CustomerID] INT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), [Name] NVARCHAR(250) NOT NULL
INDEX [IName] HASH WITH (BUCKET_COUNT = 1000000), [CustomerSince] DATETIME NULL)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
This table is memory
optimized
This table is durable
Secondary Indexes are
specified inline
Hash Index
![Page 7: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/7.jpg)
Memory Optimized Table CreationCREATE TABLE DDL
Code generation and compilation
Table DLL produced
Table DLL loaded
![Page 8: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/8.jpg)
Hash Indexes
4567
0123
Array of8-byteMemorypointers
Hash index with (bucket_count=8):
Hash function f:• Maps values to buckets• Built into the system
Hash mapping:
f(Jane)
f(John)f(Susan)
f(Prague)
f(Bogota), f(Beijing)
Hash Collisions
![Page 9: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/9.jpg)
Memory Optimized Tables and Indexes
50, ∞ Jane Prague
Timestamps NameChain ptrs City
Hash index on City
Hash index on Name
90, ∞ Susan Bogota
f(Jane)
f(Susan)
f(Prague)
f(Bogota)
![Page 10: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/10.jpg)
Memory Optimized Tables and Indexes
50, ∞ Jane Prague
Timestamps NameChain ptrs City
Hash index on City
Hash index on Name
T100: INSERT (John, Prague)
100, ∞ John Prague
90, ∞ Susan Bogota
f(John) f(Prague)
![Page 11: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/11.jpg)
90, ∞ Susan Bogota
Memory Optimized Tables and Indexes
50, ∞ Jane Prague
Timestamps NameChain ptrs City
Hash index on City
Hash index on Name
T150: DELETE (Susan, Bogota)
100, ∞ John Prague
90, 150
![Page 12: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/12.jpg)
Memory Optimized Tables and Indexes
90, 150 Susan Bogota
50, ∞ Jane Prague
Timestamps NameChain ptrs City
Hash index on City
Hash index on Name
T200: UPDATE (John, Prague) to (John, Beijing)
100, ∞ John Prague
200, ∞ John Beijing
100, 200
f(Beijing)
f(John)
![Page 13: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/13.jpg)
Memory Optimized Tables and Indexes
90, 150 Susan Bogota
50, ∞ Jane Prague
Timestamps NameChain ptrs City
Hash index on City
Hash index on Name
T250: Garbage collection
100, 200
John Prague
200, ∞ John Beijing
f(John)
f(Jane)
f(Beijing)
f(Prague)
![Page 14: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/14.jpg)
Limitations on Tables in SQL 2014Optimized for high-throughput OLTPNo DML triggersNo XML and no CLR data types
Optimized for in-memoryRows are at most 8060 bytes – no off row dataNo Large Object (LOB) types like varchar(max)
Scoping limitationsNo FOREIGN KEY and no CHECK constraintsNo IDENTITYNo schema changes (ALTER TABLE) – need to drop/recreate tableNo add/remove index – need to drop/recreate table
![Page 15: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/15.jpg)
Accessing memory optimized tables
![Page 16: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/16.jpg)
Create Procedure DDLCREATE PROCEDURE [dbo].[InsertOrder] @id INT, @date DATETIME WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERAS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
-- insert T-SQL hereEND
This proc is natively compiled
Native procs must be schema-bound
Atomic blocks • Create a
transaction if there is none
• Otherwise, create a savepoint
Execution context is required
Session settings are fixed at create time
![Page 17: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/17.jpg)
Procedure CreationCREATE PROC DDL
Query optimization
Code generation and compilation
Procedure DLL produced
Procedure DLL loaded
![Page 18: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/18.jpg)
Accessing Memory Optimized Tables• Natively Compiled Procs
– Access only memory optimized tables
– Maximum performance– Limited T-SQL surface area
• When to use– OLTP-style operations– Optimize performance critical
business logic
• Interpreted T-SQL Access– Access both memory- and
disk-based tables – Less performant– Virtually full T-SQL surface
• When to use– Ad hoc queries– Reporting-style queries– Speeding up app migration
![Page 19: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/19.jpg)
Demo
Accessing Memory Optimized Tables
![Page 20: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/20.jpg)
Transactions on memory optimized tables
![Page 21: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/21.jpg)
In-Memory OLTP Concurrency Control
Multi-version data store
Snapshot-based transaction isolation
No TempDB
Conflict detection to ensure isolation
No deadlocks
No locks, no latches, minimal context switches
No blocking
Multi-version
Optimistic
![Page 22: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/22.jpg)
Supported Isolation LevelsSNAPSHOTReads are consistent as of start of the transactionWrites are always consistent
REPEATABLE READRead operations yield same row versions if repeated at commit time
SERIALIZABLETransaction is executed as if there are no concurrent transactions – all actions happen at a single serialization point (commit time)
![Page 23: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/23.jpg)
Example: Write conflict
Time Transaction T1 (SNAPSHOT) Transaction T2 (SNAPSHOT)
1 BEGIN
2 BEGIN
3 UPDATE t SET c1=‘value2’ WHERE c2=123
4 UPDATE t SET c1=‘value1’ WHERE c2=123 (write conflict)
First writer wins
![Page 24: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/24.jpg)
Guidelines for usage
1. Declare isolation level – no locking hints
2. Use retry logic to handle conflicts and validation failures
3. Avoid using long-running transactions
![Page 25: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/25.jpg)
Cross-container transactions
Disk-based tables Memory-optimized tables
Tab1 Tabn Tab1 Tabm
Regular Tx context Mem-opt Tx context
![Page 26: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/26.jpg)
Disk-based Memory optimized
Usage recommendations
READCOMMITTED SNAPSHOT • Baseline combination – most cases that use READCOMMITTED today
READCOMMITTED REPEATABLEREAD/SERIALIZABLE
• Data migration • Hekaton-only Interop
REPEATABLEREAD/SERIALIZABLE
SNAPSHOT • Memory-optimized table access is INSERT-only• Useful for data migration and if no concurrent
writes on memory-optimized tables (e.g., ETL)
Disk-based Memory optimized
SNAPSHOT Any isolation level
REPEATABLEREAD/SERIALIZABLE
REPEATABLEREAD/SERIALIZABLE
Supported isolation level combinations (V1)
Unsupported isolation level combinations (V1)
![Page 27: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/27.jpg)
Demo
Transaction Isolation
![Page 28: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/28.jpg)
RecapMemory optimized tables and indexesAre natively compiled on createNew hash indexes – ordered indexes in pipeline for CTP2Multi-versioned data store
Accessing memory optimized tablesNatively compiled stored procedures – best performance, but T-SQL limitationsInterpreted T-SQL access – full T-SQL surface area, and joins with disk-based tables
Transaction semanticsMulti-versioned, snapshot-based isolationOptimistic: no locking – conflict detectionCross-container transactions – limitations on isolation levels
![Page 29: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/29.jpg)
Related contentBreakout Sessions
DBI-B204 Microsoft SQL Server 2014 In-Memory OLTP: OverviewDBI-B308 Microsoft SQL Server 2014 In-Memory OLTP: Management Deep Dive – Thu 15:15
Hands-On Lab: SQL Server Project “Hekaton”Find Me Later At…
Ask The Experts…..Thursday eveningMSE – Server Cloud Tools…. Friday
![Page 30: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/30.jpg)
msdn
Resources for Developers
http://microsoft.com/msdn
Learning
Microsoft Certification & Training Resources
www.microsoft.com/learning
TechNet
Resources
Sessions on Demand
http://channel9.msdn.com/Events/TechEd
Resources for IT Professionals
http://microsoft.com/technet
![Page 31: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/31.jpg)
Evaluate this session
Scan this QR code to evaluate this session.
![Page 32: SQL Server 2014 adds in-memory technology to boost performance of OLTP workloads](https://reader030.vdocuments.us/reader030/viewer/2022032414/56649efa5503460f94c0d1a2/html5/thumbnails/32.jpg)
© 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.