SQL

David Peter Hansen

First look at the In-Memory OLTP database engine component in SQL Server 2014

Senior Manager, Hitachi Consulting@dphansen

new database engine component fully integrated into SQL Server 2014optimized for OLTP workloads accessing memory resident dataachive improvements in performance and reduction in processing

Client App

Natively Compiled SPs and Schema

TDS Handler and Session Management

In-Memory OLTP Compiler

Tables

Indexes

Memory Optimized Tables & Indexes

Tables

Indexes

Buffer Pool for Tables & Indexes

Query Interop

Parser, Catalog, Optimizer

Interpreted T-SQL Query Execution

T3 T1 T2

checkpoint filesall data is stored in memory, all the timecheckpoint files for recovery purpose – append only max # of check point files = 4000, max size of a check point file = 128 MBtheoretical maximum = 512 GB, supported maximum = 256 GB

transaction loguse same transaction log as disk-based tablesuse less log bandwidth; do not log index operations or UNDO records

recovery timeduring database recovery all data needs to be read from disk into memorycheckpoint files uses streaming IOmain factors: speed of sequential IO and bandwidth of HBA; no need for SSDrecovery time objective (RTO) on restart determine IO speed requirement spread checkpoint files over multiple LUNs; support for parallel load of checkpoint files

create tablehas similar syntax for creatind disk-based tablesLOB data not allowedrow limit of 8060 bytes with no off-row dataDURABILITY = SCHEMA_AND_DATA or SCHEMA_ONLYlimitations: no DML triggers, no foreign keys, no IDENTITY

indexes not stored as B-treeshash indexes # buckets >= expected cardinality of the index key columnrange indexes – not available in CTP1are not stored on disk

democreate memory-optimized OLTP database, tables, and indexes

t-sql dmlselect, insert, update, deletealmost transparent to users that they are working on memory-optimized tablesclient applications connect through the TDS handlerinterop capabilities – interpreted T-SQL can access memory-optimized tables

demoselect, insert, update

locksoptimistic multi-version concurrency controlno locks acquired; no waiting because of blockingno waits for reading data from disks or locks on data rowsif a transaction insert a row with same key as another open transaction errorif a transaction updates same row as another open transaction error

demolocking: memory-optimized tables vs disk-based tables

natively compiled stored procaccess memory-optimized tables: interop (interpreted T-SQL) or natively compiled stored procsad-hoc queries interopaccess both memory-optimized and disk-based tables interopbest execution performance natively compiled stored proceduresnatively compiled stored procedures can only access memory-optimized tables

demonatively compiled stored procedures and interop

summarydatabase, checkpoint files & transaction logrecovery timetables, indexes & dmllocksnatively compiled stored procedures

referencesblogs.msdn.com/b/sqlcat/archive/2013/06/25/sql-server-in-memory-oltp-internals-overview-for-ctp1.aspxblogs.technet.com/b/dataplatforminsider/archive/tags/in_2d00_memory/research.microsoft.com/apps/pubs/default.aspx?id=193594blogs.msdn.com/b/arvindsh/archive/2013/07/03/sql-2014-in-memory-oltp-hekaton-training-videos-and-white-papers.aspxwww.sqlskills.com/blogs/bobb/category/hekaton/sqlblog.com/blogs/davide_mauri/archive/2013/08/16/testing-hekaton-some-results.aspxthomaslarock.com/2013/08/sql-server-2014-in-memory-oltp-hekaton-useful-linksblogs.msdn.com/b/carlnol/archive/2013/09/16/implementing-lob-storage-in-memory-optimized-tables.aspxmsdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspxmattsql.wordpress.com/2013/07/08/in-memory-oltp-with-sql-server-2014/www.simple-talk.com/sql/database-administration/exploring-in-memory-oltp-engine-(hekaton)-in-sql-server-2014-ctp1/

EvaluationCreate a Text message on your phone and send it to 1919 with the content:

DB302 5 5 5 I liked it a lotSession Code

David Performance

(1 to 5)

Match of technical Level

(1 to 5)

Relevance(1 to 5) Comments

(optional)

Evaluation Scale: 1 = Very bad 2 = Bad 3 = Relevant 4 = Good 5 = Very Good!

Questions:• Speaker Performance• Relevance according to your work • Match of technical level according to

published level• Comments

davidpeterhansen.com/talks