Downloaded 46 times
SQL Server In-Memory OLTP: What Every SQL Professional Should Know
- 1. http://aka.ms/bobsql bobward@microsoft.com @bobwardms, #bobsql Latch freesince 2007http://aka.ms/bobwardms
- 2. How do I usethis? Based on SQL Server 2016
- 3.
- 4. OLTP Through theLooking Glass, and What We Found There
- 5. Project Verde • 2007 “Early” Hekaton •2009/2010 Hekaton becomes In- Memory OLTP • SQL Server 2014 In-Memory OLTP Unleashed • SQL Server 2016
- 6. XTP = eXtremeTransaction Processing HK = Hekaton
- 8. “Feels” like normaldisk-based tables but in memory Internally completely different Hash or non-clustered index choices (at least one required)
- 11. Create a Memory Optimized FILEGROUP Decideon an index strategy Create a Memory- Optimized Table Create a natively compiled stored procedure Start running transactions Tune and manage
- 12. • Estimating Table,Index, Versioning, and Growth. Read here Do I have enough Memory? • Hash = unique row lookup • Non-clustered = range lookups (also called range index) Hash or Non-clustered index? • SCHEMA_ONLY = Does not persist server restarts; No I/O (even log) • SCHEMA_AND_DATA = Persists server restartsDurability • Ultimate speed vs some limits • Limited diagnostics by default Use Natively Compiled Stored Procedures? • Transaction Log - Transactions only as fast as commit speed • FILEGROUP – One or multiple folders across drives for faster startupPhysical File Placement NVDIMM
- 13. Transaction Performance AnalysisReport Memory Optimization Advisor Native Compilation Advisor Analyze based on DMVs Recommend changes Find incompatibilities Migrate schema, data, and procs
- 16. • Locks thetable • Maintain indexes • Used to maintain bucketcount for hash indexes • Natively Compled Procedures are SCHEMABOUND ALTER memory optimized tables • Auto Update statistics supported (requires db compat level = 130) • Natively Compiled Stored Procedures not recompiled on auto status update Maintaining Statistics • Need to reduce space? Delete rows or drop tables • Bind to your own RG resource pool with sp_xtp_bind_db_resource_pool Managing Memory Usage
- 17. Always On AvailabilityGroups HTAP applications Azure SQL Database Cross container transactions Table variables here BACKUP/RESTORE
- 19. Immutable • Rows neverchange: UPDATE = DELETE + INSERT Versions • UPDATE and DELETE create versions • Timestamps in rows for visibility and transactions correctness Optimistic • Assume no conflicts • Snapshots + conflict detection • Guarantee correct transaction isolation at commit NOT in tempdb Pessimistic = locks Errors may occur
- 20. Release latches andlocks Update index pages ( more locks and latch ) Modify page Latch page Obtain locks INSERT LOG record In-Memory OLTP INSERT Maintain index in memory Insert ROW into memory COMMIT Transaction = Log Record and Flush COMMIT Transaction = Insert HK Log Record and Flush Page Split Sys Tran = Log Flush Spinlocks No index logging SCHEMA_ONLY no logging No latch No spinlock
- 21. Log truncation eligible atCHECKPOINT event No WAL protocol Typically 128Mb but can be 1Gb Typically 8Mb but can be 128Mb Read details from database Boot Page (DBINFO) Load ROOT file for system tables and file metadata Load ACTIVE DATA files filtered by DELTA streamed in parallel Redo COMMITED transactions greater than last CHECKPOINT LSN
- 23. • SQL ServerIn-Memory OLTP Internals for SQL Server 2016 • In-Memory OLTP Videos: What it is and When/How to use it • Explore In-Memory OLTP architectures and customer case studies • In-Memory OLTP in Azure SQL Database Blog • In-Memory OLTP (In-Memory Optimization) docs http://aka.ms/bobwardms