new database engine component fully integrated into SQL

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

Client App TDS Handler and Session Management Natively Compiled SPs and Schema T 3 In-Memory OLTP Compiler Tables Indexes Memory Optimized Tables & Indexes Parser, Catalog, Optimizer Query Interop Interpreted T-SQL Query Execution T 1 T 2 Tables Indexes Buffer Pool for Tables & Indexes

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

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

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

create table has similar syntax for creatind disk-based tables LOB data not allowed row limit of 8060 bytes with no off-row data DURABILITY = SCHEMA_AND_DATA or SCHEMA_ONLY limitations: no DML triggers, no foreign keys, no IDENTITY

indexes not stored as B-trees hash indexes # buckets >= expected cardinality of the index key column range indexes – not available in CTP 1 are not stored on disk

demo create memory-optimized OLTP database, tables, and indexes

t-sql dml select, insert, update, delete almost transparent to users that they are working on memory-optimized tables client applications connect through the TDS handler interop capabilities – interpreted T-SQL can access memory-optimized tables

demo select, insert, update

locks optimistic multi-version concurrency control no locks acquired; no waiting because of blocking no waits for reading data from disks or locks on data rows if a transaction insert a row with same key as another open transaction error if a transaction updates same row as another open transaction error

demo locking: memory-optimized tables vs disk-based tables

natively compiled stored proc access memory-optimized tables: interop (interpreted T-SQL) or natively compiled stored procs ad-hoc queries interop access both memory-optimized and disk-based tables interop best execution performance natively compiled stored procedures can only access memory-optimized tables

demo natively compiled stored procedures and interop

summary database, checkpoint files & transaction log recovery time tables, indexes & dml locks natively compiled stored procedures

references blogs. msdn. com/b/sqlcat/archive/2013/06/25/sql-server-in-memory-oltp-internals-overview-for-ctp 1. aspx blogs. technet. com/b/dataplatforminsider/archive/tags/in_2 d 00_memory/ research. microsoft. com/apps/pubs/default. aspx? id=193594 blogs. msdn. com/b/arvindsh/archive/2013/07/03/sql-2014 -in-memory-oltp-hekaton-training-videos-and-white-papers. aspx www. sqlskills. com/blogs/bobb/category/hekaton/ sqlblog. com/blogs/davide_mauri/archive/2013/08/16/testing-hekaton-some-results. aspx thomaslarock. com/2013/08/sql-server-2014 -in-memory-oltp-hekaton-useful-links blogs. msdn. com/b/carlnol/archive/2013/09/16/implementing-lob-storage-in-memory-optimized-tables. aspx msdn. microsoft. com/en-us/library/dn 133186(v=sql. 120). aspx mattsql. 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 -ctp 1/

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 Evaluation Create a Text message on your phone and send it to 1919 with the content: Session Code DB 302 5 5 5 I liked it a lot David Performance (1 to 5) Relevance (1 to 5) Match of technical Level (1 to 5) Comments (optional)