InMemory OLTP Concepts and Improvements in SQL Server

In-Memory OLTP: Concepts and Improvements in SQL Server 2016 Sunil Agarwal Principal Program Manager (SQL Tiger Team) Microsoft Inc. sunila@microsoft. com Twitter @s_u_n_e_e_l

Session Objectives and Takeaways • Session objective(s): – In-Memory OLTP overview – Identify the scenarios where In-Memory OLTP provides benefit – Assess whether and how In-Memory OLTP can benefit a customer scenario • Key takeaways: – SQL In-Memory OLTP offers incredible performance gains for transactional and data ingestion workloads – Many non-core transactional scenarios can benefit from In-Memory OLTP, e. g. , temp tables, caching, ETL

Principles Performance-critical data fits in memory Push decisions to compilation time Architectural Pillars In-Memory OLTP Architectural Pillars Main-Memory Optimized Direct pointers to rows Indexes exist only in memory No buffer pool No write-ahead logging Stream-based storage T-SQL Compiled to Native Machine Code • T-SQL compiled to machine code leveraging VC compiler • Procedure and its queries, becomes a C function • Aggressive compiletime optimizations In-memory cache speed with capabilities of a database Queries & business logic run at nativecode speed • • Results • Conflicts are Rare • • Non-Blocking Execution Multi-version optimistic concurrency control, full ACID support Lock-free data structures No locks, latches or spinlocks, No I/O in transaction Transactions execute to completion without blocking Built-In SQL Server Integration • Same manageability, administration & development experience • Integrated queries & transactions • Integrated HA and backup/restore Hybrid engine and integrated experience

Key lookup: Btree vs Memoty-Optimized Table Hash index on Name R 1 R 3 Matching index record Clustered Index R 2

In-Memory OLTP: Integration and Application Migration Application TDS Handler and Session Management Few improvements in comm layers 20 -40 x more efficient Real Apps see 2 -30 x Parser, Catalog, Optimizer Hekaton Compiler Native. Compiled SPs and Schema Execution Plan cache for ad-hoc T-SQL and SPs Existing SQL Component T-SQL Interpreter Hekaton Component Access Methods Non-durable Table option Reduced log contention; Low latency still critical for performance Key Query Interop Hekaton Engine: Memory_optimized Tables & Indexes Memory-optimized Table Filegroup SQL Server. exe T 1 T 2 T 3 Tables T 1 T 2 T 3 Indexes Buffer Pool T 1 T 2 T 3 Transaction Log Data Filegroup Generated. dll

SQL Server In-Memory OLTP – Overview In-Memory OLTP Performance numbers Optimized for OLTP workloads • Memory-optimized data structures for efficient data access • Lock/Latch free access for linear scaling • Native Compilation of T-SQL Modules for efficient execution Up to 30 X performance improvement compared with traditional tables and stored procs • Actual gain depends on app pattern; typical gains: 2 X-10 X Fully integrated into SQL Server • No new license or product • Familiar tools • Integrated Security and High Availability None or Limited Application Changes Available in SQL Server and Azure SQL DB Perf numbers in testing (4 S server): • 10 M rows ingested per second • 1 GB/s of log generation • 360 K transactions per second for order processing workload (most transactions are mixed read/write) BWIN. PARTY experiences with ASP. NET session state • Consolidate 18 servers -> 1 in production • 1 M requests/sec in testing

In-Memory OLTP features - Overview • Memory-Optimized Tables – Data storage in memory-optimized structures – Compiled into DLLs for efficient data access • Memory-Optimized Table Types – Memory-optimized table variables live in memory, in user DB (no tempdb, no IO) – Can be used for TVPs and table variables in all stored procs (not just native) • Natively Compiled Stored Procedures – Optimize performance of OLTP-style operations – Not suitable for reporting/analytics style queries • Natively Compiled Scalar User-Defined Functions – Do not require memory-optimized tables if there is no data access

Create Table DDL CREATE TABLE [Customer]( [Customer. ID] INT NOT NULL PRIMARY KEY NONCLUSTERED, [Name] NVARCHAR(250) NOT NULL, [Customer. Since] DATETIME 2 NULL, INDEX [ICustomer. Since] NONCLUSTERED (Customer. ID, Customer. Since) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); This table is Memory Optimized This table is durable (default). Non-durable tables: DURABILITY=SCHEMA_ONLY

Create Table Type DDL CREATE TYPE [Sales]. [Sales. Order. Detail. Type_inmem] AS TABLE( [Order. Qty] [smallint] NOT NULL, [Product. ID] [int] NOT NULL, [Special. Offer. ID] [int] NOT NULL, INDEX [IX_Product. ID] NONCLUSTERED HASH ([Product. ID]) WITH ( BUCKET_COUNT = 8) ) WITH ( MEMORY_OPTIMIZED = ON )

Create Procedure DDL CREATE PROCEDURE [dbo]. [Insert. Order] @id INT, @date DATETIME 2 WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N‘Dutch') -- insert T-SQL here END

Create Function DDL CREATE FUNCTION [dbo]. [ufn. Get. Accounting. End. Date_native]() RETURNS [datetime] WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'us_english') RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20160701', 112)); END

Durability options Durability Configuration level Pros/Cons Scenarios Full durability Default Pro: • Every committed change is guaranteed to survive failure Con: • Latency impact: every commit requires log IO Default: most scenarios need full durability Nondurable tables Table creation Pro: • No IO at all Con: • Lose data on failure • Transient data such as session state • Caching • ETL (staging tables) Delayed durability • Transaction commit time Pro: • Low latency due to no log IO in transaction execution path • Efficient log IO due to batching Con: • Limited data loss on failure (usually ~60 K or ~1 ms worth) Low latency requirements DURABILITY=SCHEMA_AND_DATA DURABILITY=SCHEMA_ONLY COMMIT WITH (DELAYED_DURABILITY=ON) • Atomic block of native procedure BEGIN ATOMIC WITH (DELAYED_DURABILITY=ON, …) • Database level ALTER DATABASE CURRENT SET DELAYED_DURABILITY=FORCED Can accept some data loss OR Copy of recent data exists elsewhere in case of failure Always. On auto-failover (sync replicas) with low latency

In-Memory OLTP Performance Demo Sunil Agarwal Principal Program Manager Microsoft SQL Server Tiger Team Sources: • In-Memory OLTP perf demo • Memory-optimized table variables and temp tables

When to use (high-level) In-Memory OLTP Recommended Don’t Use In-Memory OLTP • Increase transaction throughput • Improve perf of analytics/BI – use Columnstore instead • Improve data ingestion rate • Reduce latency • Remove latency spikes – need consistent low latency • Transient data scenarios • Perf bottleneck outside SQL • • App is chatty – many short requests – network communication dominates Bottleneck is in the app • Low transaction volume • Resource limitations – not enough available memory for perf-critical data

When to use (low-level) In-memory OLTP Recommended Don’t use In-Memory OLTP • Optimize DML (insert/update/delete) • Optimize large range/table scans – use Columnstore instead • High degree of concurrency • Latency spikes due to latch/lock waits • Transient and temporary data • Tempdb bottlenecks • • SQL Server: contention on PFS/SGAM pages Azure SQL DB: tempdb counts partly toward log IO cap • Reduce log throughput • • Logging more efficient due to no logging of indexes Azure SQL DB: more data fits in log IO cap • [native compilation]: “OLTP-like” operations • • DML (insert/update/delete) Queries touch limited number of rows Query plans favor nested loops over hash join The more statements and the more complex the logic in a single proc -> the better the perf benefits • Can use combination of memory-optimized and columnstore • Short transactions with slow log IO • • Log IO device becomes perf bottleneck, so no gain from optimizing data processing Can potentially work around using DELAYED_DURABILITY • Tables don’t fit in memory • Note: for data that is temporal in nature, can use temporal memoryoptimized tables to limit memory footprint • [native compilation]: “Analytics-style” queries • • • Queries touching large nrs of rows (e. g. , table scans) Parallel queries (native modules are single-threaded) Query plans favor hash join over nested-loops (native support only nested-loops)

In-Memory OLTP Scenarios • High throughput OLTP with low latency requirements – E. g. , financial trading, gaming • Shock-absorber for data load (high data ingestion rate) – Memory-optimized table as “buffer”, with offload to clustered columnstore – Remove mid-tier caching • Session state and caching – E. g. , ASP. NET session state – Replace No. SQL caching (e. g. , Redis) with better perf and reduced complexity • ETL – Use SCHEMA_ONLY staging tables • Tempdb replacement – Memory-optimized table-valued parameters (TVPs) – Memory-only (SCHEMA_ONLY) tables to replace temp tables

Temp. DB replacement Demo Sunil Agarwal Principal Program Manager Microsoft SQL Server Tiger Team Sources: • In-Memory OLTP perf demo • Memory-optimized table variables and temp tables

SQL Server 2016: Performance improvements • Throughput: – Sustained log generation rate 1 GB/s – Numerous improvements under the hood • Parallel plans and parallel scan of memory-optimized tables and indexes • Heap scan – no traversal of index structures is required

SQL Server 2016: Programmability improvements Native Compilation Tables Full collations support in native modules Collations: Query surface area {LEFT|RIGHT} OUTER JOIN Disjunction (OR, NOT) UNION [ALL] SELECT DISTINCT Subqueries (EXISTS, IN, scalar) Nested Stored procedures (EXECUTE) Natively Compiled Scalar UDFs Access from both native and interop Improves perf of scalar UDFs in traditional workload if no table access is required Full collations support in index key columns All code pages supported for (var)char columns Constraints FOREIGN KEY CHECK UNIQUE constraints and indexes LOB types: [n]varchar(max) and varbinary(max) Row size >8060 bytes NULLable index key columns DML triggers on memory-optimized tables (AFTER) Natively Compiled Inline TVFs Row-Level Security EXECUTE AS CALLER Always Encrypted Query Store Temporal Tables (history lives on-disk)

SQL Server 2016: Manageability improvements • Improved SSMS experience – Lightweight Performance Analysis in SSMS – Generate migration checklists to evaluate all tables/procedures for migration • ALTER support for procedures and tables – – Full ALTER TABLE/PROCEDURE support Index management through ALTER TABLE … ADD/ALTER/DROP INDEX Limitation: ALTER TABLE offline for now; ALTER of 10 GB table takes 1. 5 minutes No ALTER between disk-based and memory-optimized • TDE (Transparent Data Encryption) – All on-disk data files are now encrypted once TDE is enabled • Stats improvements: auto-update and sampled stats

In-Memory OLTP for Azure SQL DB • Now in Public Preview • Supported in all new Premium databases – No need to manage filegroup – No need to bind database to a resource pool for memory management • Data size: – – – P 1 – 1 GB user data size P 2 – 2 GB user data size P 4 – 3 GB* user data size P 6 – 8 GB user data size P 11 – 10 GB* user data size – * Will change as follows: P 4 – 4 GB; P 11 – 14 GB • Full In-Memory OLTP Surface Area – Memory-optimized tables and table types – Natively compiled stored procedures and scalar UDFs – New features are released to Azure SQL DB as soon as they are completed

In review: session objectives and takeaways Identify the scenarios where In-Memory OLTP provides benefit Assess whether and how In-Memory OLTP can benefit a customer scenario SQL In-Memory OLTP offers incredible performance gains for transactional and data ingestion workloads Many non-core transactional scenarios can benefit from In-Memory OLTP, e. g. , temp tables, caching, ETL

A Big Thanks to Our Sponsors

Thank you for your time! Connect us with us: @SQLServer. Geeks facebook. com/groups/the. SQLGeeks www. SQLServer. Geeks. com admin@SQLServer. Geeks. com