IN-MEMORY OLTP By Manohar Punna SQL Server Geeks – Regional Mentor, Hyderabad Blogger, Speaker

1. IN-MEMORY OLTP By Manohar PunnaSQL Server Geeks – Regional Mentor, Hyderabad Blogger, Speaker Sr. DBA at S&P Capital IQ (McGraw Hill Financials)

2. Speaker Details • ManoharPunna(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 McGraw Hill Financials as a Sr SQL DBA in Escalation team

3. 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

4. Why In-Memory • 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

5. Disk Based Tables SQL DB Engine Process Memory Buffer Pool Physical Reads Checkpoint Lazy Writer Consider a scenario with NCI scan followed by a key lookup.

6. Memory Optimized Tables SQL DB Engine Process Memory Hekaton DB Engine Buffer Pool Asynchronous data Sync Recovery

7. How Stuff Works – Create, Insert, Read Lets LEARN this by a Demo

8. How Stuff Works – Row Format 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)

9. How Stuff Works – Garbage Collection Tran 5 = 210 Oldest Active Hint = 175 Hash Index • 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

10. 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 4kb 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

11. How Stuff Works – Data Storage (2) 0 Data File Checkpoint File Pair Delta File 100

12. How Stuff Works – Transaction Logging • 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

13. Considerations • Space needed in Memory • Table data - 2X the data size • Index data - Bucket_Count x Pointer_Size (8 bytes) • Space needed on Disk • Data 2X-3X 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 • CTP1 includes Data and Delta files with t-log backups

14. Summary • Covered creation of In-Memory table • Advantages of In-Memory table over disk-based table • Storage structure of Hekaton • Logging with Memory Optimized tables • What next • Native Compiled Stored Procedures • Deep dive with more demos

15. Q & A

16. Spread the word… • www.FaceBook.com/SQLServerGeeks • Be a member – www.SQLServerGeeks.com • @SQLServerGeeks • Talk about your experience • Post photos • Blog, Tweet (#SQLServerGeeks) • Post your experience on Forums • Why do all this? • “We want to make the community bigger & largerand we need your support”

17. Thank you  for suggestions, please email at admin@SQLServerGeeks.com