INMEMORY OLTP By Manohar Punna SQL Server Geeks

IN-MEMORY OLTP By Manohar Punna SQL Server Geeks – Regional Mentor, Hyderabad Blogger, Speaker Sr. DBA at S&P Capital IQ (Mc. Graw Hill Financials) Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Speaker Details Manohar Punna (Regional Mentor, Hyderabad) 5+ Years of experience as Core SQL DBA Blogger and Speaker at SQL Server Geeks Worked in various business models supporting databases including Microsoft GTSC MCITP Certified Currently working at Mc. Graw Hill Financials as a Sr SQL DBA in Escalation team Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Agenda Why In-Memory? Disk Based Tables vs. Memory Optimized Tables How Stuff Works Demo – Create, Insert, Read Row Format Garbage Collection Data Storage Transaction Logging Considerations Summary Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Why In-Memory is getting cheaper Efficient processing of instructions With Native compiled SP execution is just a DLL entry point Aggressively optimized at compile time Scale-Up Issues No Paging Lock free structure Optimistic concurrency with full ACID support Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Disk Based Tables SQL DB Engine Process Memory Buffer Pool Physica l Reads Checkpo int Lazy Writer Consider a scenario with NCI scan followed by a key lookup. Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Memory Optimized Tables SQL DB Engine Process Memory Buffer Pool Hekaton DB Engine R 1 R 3 R 2 Asynchron ous data Sync Recovery Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

How Stuff Works – Create, Insert, Read Lets LEARN this by a Demo Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

How Stuff Works – Row Format Row Header Begin Ts 8 bytes Payload (data in columns) End Ts 8 bytes Stmnt ID 4 bytes Ind Link Count 2 bytes 8 b*(Ind. Link. Count – 1) Begin Ts and End Ts determine the validity of the record Row size limited to 8060 bytes Row linking leads to collisions. Can be avoided by providing more hash buckets. Off-Row storage types are not allowed (XML, BLOB, Spatial) Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

How Stuff Works – Garbage Collection Row Versions are created for Update, Delete and Aborted transactions GC is non blocking, cooperative and efficient Dedicated GC background thread Active Transactions work cooperatively to perform GC Based on oldest Active Transaction Hint Tran 5 = 210 Hash Index Oldest Active Hint = 175 100 200 1 FN 1 MN 1 LN 1 300 ∞ 1 FN 1 MN 1 LN 1 50 100 1 FN 1 MN 1 LN 1 200 ∞ 1 FN 1 MN 1 LN 1 Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

How Stuff Works – Data Storage Data storage is two types – Durable and Non-Durable Storage is a dedicated filestream in a Memory Optimized filegroup The data access is sequential Two types of files as pairs – Data and Delta Data Files 128 MB file size with 256 kb pages Stores only Inserted rows Delta Files Grows by 4 kb pages over time Contains only IDs of deleted rows Data files are merged overtime to reuse the space. Manually - sys. sp_merge_xtp_checkpoint_files Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

How Stuff Works – Data Storage (2) 0 Data File TS(In s) Table. I D Row. I D TS(In s) TS(De Row. ID l) Row Payload Checkpoint File Pair Delta File 100 Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

How Stuff Works – Transaction Logging SQL Log Header Opaque Hekaton specific log content The log records are logical Physical structure modifications are not logged Index specific or Index maintenance operations not logged Undo information is not logged Transaction are logged only when the commit occurs Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Considerations Space needed in Memory Table data - 2 X the data size Index data - Bucket_Count x Pointer_Size (8 bytes) Space needed on Disk Data 2 X-3 X the table size Log - Less than disk based tables. If latency is important use SSD Backups Differential is not supported Piecemeal Restore should include Hekaton File Group restored with Primary CTP 1 includes Data and Delta files with t-log backups Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Summary Covered creation of In-Memory table Advantages of In-Memory table over diskbased table Storage structure of Hekaton Logging with Memory Optimized tables What next Native Compiled Stored Procedures Deep dive with more demos Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Q&A Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Spread the word… www. Face. Book. com/SQLServer. Geeks Be a member – www. SQLServer. Geeks. com @SQLServer. Geeks Talk about your experience Post photos Blog, Tweet (#SQLServer. Geeks) Post your experience on Forums Why do all this? “We want to make the community bigger & larger and we need your support” Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks

Thank you for suggestions, please email at admin@SQLServer. Geeks. com Twitter: @SQLServer. Geeks www. Face. Book. com/SQLServer. Geeks