Download as PDF, PPTX
Uploaded byRico Chen
Sql2017 in memory oltp for developers
This document provides an overview of SQL Server 2017 In-Memory OLTP for developers. It discusses table types that can be used, including schema-only tables that are faster than schema-and-data tables. It also covers range and hash indexes that provide better performance than disk-based indexes when used in-memory. Natively compiled stored procedures can more efficiently access memory-optimized tables and avoid recompilation during restart. The document recommends setting the transaction isolation level to read committed and including retry logic to handle conflicts when using snapshot isolation.
Sql2017 in memory oltp for developers
- 1. SQL2017 IN-MEMORY OLTP FORDEVELOPERS RiCo Chen
- 2. AGEND • Table • TableTypes • Rang Indexes • Hash Indexes • Natively-Compiled Store Procedure • Transaction isolation level
- 3. TABLE • Schema_only alwaysfaster than schema_and_data • Schema_and_data needs a PK • Recommend avoid LOBs、FK、trigger • Rows in In-memory tables are versioned • The DLLs are recompiled after a server or database restart • Can scan the table in parallel(starting in SQL2016) • Improves concurrency such as latchs,locks,spinlocks and blocking problems • Improves resource contention and less usage • Improvers throughput • CLR access no support
- 4. TABLE TYPES • SchemaOnly and must have at least one index • Memory release after take variable out of scope • Parallel plan support • Ignore transaction • Willn’t caused recompiled • Instead temptable • Avoid create in runtime
- 5. RANG INDEXES • Forrang or ordered scans, have better performance than disk indexes • Better than hash index when querying a table with inequality • Index’s operations are not logged, they exits only in memory • No latching,locking for page update • No fillfactor,fragmentation • Only rebuilt when database is back online • Compression no support
- 6. HASH INDEXES • Forpoint lookups, have better performance than disk/memory nonclustered indexes • Index’s operations are not logged, they exits only in memory • No page,fragmentation,fillfactor • Each bucket is 8 bytes • Maximum number of bucket is 1,073,741,824 • Short link lists perform faster than long link lists • Hash function is balanced • Hash collisions can have a performance impact on read operations • Compression no support
- 7. NATIVELY-COMPILED STORE PROCEDURE •Support execute as Caller, has a small performance(10%) hit caused by permission checks • Memory-optimized tables Can be accessed most efficiently • Compiled at the time of first execution,not recompiled during a server restart • DLLs are not included in database backups • No parameter sniffering issue • For complex expressions,nested-loops joins,frequently executed,aggregation,procedural logic such as loop… • Avoid convert types and do not named parameters
- 8. TRANSACTION ISOLATION LEVEL •Set memory_optimized_elevate_to_snapshot=on • Using the read committed isolation level • Snapshot’s resource usage lesser than serializable or repeatable • Recommend retry logic implementation to deal with these conflicts • Transaction maybe waiting other depend transaction • Cross-database transactions are not supported(except tempdb) • Avoid long transaction
- 9.
- 10. REFERENCE • In-Memory OLTP(In-Memory Optimization)