an introduction to sql server in-memory oltp engine
DESCRIPTION
This is an introduction to Microsoft SQL Server In-memory Engine that was earlier code named Hekaton. It describes the basic concepts and technologies involved in the in-memory engine - This has presented in Kerala - Microsoft Users Group Meeting on May 31, 2014TRANSCRIPT
![Page 1: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/1.jpg)
An introduction to SQL Server In-Memory OLTP
Krishnakumar S
![Page 2: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/2.jpg)
Traditional OLTP Architecture
•Disk is cheap – Data lives in disk•Memory is fast – Data modified in memory (Buffer) •Disk to Memory – I/O utilization•Query processing – CPU & Memory utilization•ACID – I/O utilization
Write ahead log, Transaction Log CPU utilization (Locking, latching)
•Memory to Disk – I/O utilization (Dirty pages to disk – Checkpoint)
![Page 3: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/3.jpg)
High volume data modifications
Traditional OLTP Architecture
Contentions – “Competition for resources”
• Lock• Latch• Disk I/O• Allocation
AirlinesSensor dataApplication loggingConversations
etc…
![Page 4: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/4.jpg)
CPU vs. Memory vs. I/O
Changing scenario…..
CPUStill hold Moor’s lawBecoming fasterMulticore
MemoryGetting cheaperBecoming fasterHold more data
I/OLagging far behindCPU and Memory
“The Free lunch is over…” – Herb Sutter, Dr. Dobb’s Journal, March 2005
“Tape is dead Disk is Tape Flash is Disk…” – Jim Gray, Microsoft, Dec 2006
![Page 5: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/5.jpg)
SQL Server 2014 In-memory OLTP Engine
Goal was to achieve 100x speedup for certain TP operations
Hekaton a Greek word for 100
Microsoft spend nearly seven years on Hekaton
A major change in SQL Server engine from version 7.0
![Page 6: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/6.jpg)
Four architectural pillars
SQL Server 2014 In-memory OLTP Engine
1. Optimize for main memory data access2. Accelerate business logic processing3. Provide frictionless scale-up4. Built-in to SQL Server
![Page 7: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/7.jpg)
Optimize for main memory
SQL Server 2014 In-memory OLTP Engine
1. No 8KB pages for tables; only stream of bytes2. New lock and latch free in-memory indexes
1. Hash index (equality)2. Bw index (range) [“Buzz Word” index]
3. Using MVCC (Muti-Version Concurrency control); transactions see a snapshot of data
4. Log, data changes to transaction log5. No logging for index changes; recreates when
server restart
![Page 8: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/8.jpg)
Accelerate business logic processing
SQL Server 2014 In-memory OLTP Engine
1. Table and index metadata compiled to DLL1. Table row format preserved in DLL2. Traditional query engine can be leveraged to access in-
memory tables 2. Stored Procedure compiled to DLL
![Page 9: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/9.jpg)
Frictionless scale-up
SQL Server 2014 In-memory OLTP Engine
1. Multi-version optimistic concurrency control with ACID support
2. Lock free algorithms for in-memory engine3. No lock manager, latches or spin lock
![Page 10: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/10.jpg)
Built-in to SQL Server
SQL Server 2014 In-memory OLTP Engine
1. In-memory engine is integrated in to SQL Server2. Both memory optimized and traditional tables can be
created for a database3. No lock manager, latches or spin lock
![Page 11: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/11.jpg)
In-memory OLTP Engine – Behind the scenes
![Page 12: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/12.jpg)
Rows
In-memory OLTP Engine – Behind the scenes
• Row Header– BeginTS
• TX TS that created the row– EndTS
• TX TS that deleted the row• Infinity
– StmtID• Statement that created the row
– IdxLinkCount• Number of indexes that reference this row
• Payload = actual data
TxID global DB counterReset on restartIncremented on TX start
TxTimeStamp global DB counter
Not reset on restartIncremented on TX end
![Page 13: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/13.jpg)
Rows – Hash index
In-memory OLTP Engine – Behind the scenes
Hash index on Name
f(John)
Timestamps NameChain ptrs City
90, ∞ Susan Bogota
50, ∞ John Paris
Transaction 99:SELECT City WHERE Name = ‘John’Simple hash lookup returns direct pointer to ‘John’ row
100, ∞ John Prague
50, 100
Transaction 100: UPDATE City = ‘Prague’ where Name = ‘John’No locks of any kind, no interference with transaction 99
![Page 14: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/14.jpg)
Rows – Non clustered index
In-memory OLTP Engine – Behind the scenes
![Page 15: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/15.jpg)
Rows – Non clustered index
In-memory OLTP Engine – Behind the scenes
Addr.
0
1
2
3
4
…
Page Mapping Table
Page P
Δ Inserted Record 50
Δ Deleted record 48
Delta record
Delta record
PID
0
1
2
3
4
…
4.14.2
![Page 16: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/16.jpg)
In-memory OLTP Engine – Summary
• T-SQL Features– 2 ways to access in-memory tables
• T-SQL interop (usually slower)• Natively compiled stored procedures (fast)
• T-SQL non supported features– TRUNCATE Table– MERGE (when in-memory table is the target)– Linked servers– Locking hints: TABLOCK, XLOCK, PAGLOCK, etc.
• NOLOCK is supported, but is quietly ignored
![Page 17: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/17.jpg)
In-memory OLTP Engine – Summary
• Stored Procedures– TRY, CATCH, error functions, IF, WHILE– Memory-optimized table types and table variables– Math, Date, String functions – SCOPE_IDENTITY, ISNULL, NEWID,
NEWSEQUENTIALID
![Page 18: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/18.jpg)
In-memory OLTP Engine – Summary
• Common Design patterns– In-Memory OLTP– High-performance OLTP– High data input or ”Shock Absorber”– Relational cache– Read-scale– ETL target and staging/temp tables
![Page 19: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/19.jpg)
Demo
![Page 20: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/20.jpg)
Questions?
![Page 21: An introduction to SQL Server in-memory OLTP Engine](https://reader038.vdocuments.us/reader038/viewer/2022110309/55864ef4d8b42a6b3d8b4610/html5/thumbnails/21.jpg)
Thank You