Created by Kevin Feasel
Alternate title
Which Kalen Delaney totally stole
Not really; it was the obvious title
In-Memory OLTP was released as part of SQL Server 2014. It has had a few marketing names during its short life:
The SQL Server developers are aiming for a 100x improvement over standard disk-based OLTP methods. At present, they can get ~30x improvement.
SQL Server never reads directly from a disk. When you want to read a page of data, that page goes into memory (the buffer pool). If you run out of available memory, pages start getting swapped back to disk. As long as a page is in memory, SQL Server does not need to read it from disk. Historically, database sizes have been greater than the amount of RAM available to a server.
DBCC PINTABLE
was a command telling SQL Server to keep a particular table in memory as long as possible.
Hekaton is NOT the same thing!
* Nothing!
Optimistic concurrency is nothing new in SQL Server. The on-disk OLTP has it: Read Committed Snapshot Isolation (RCSI).
RCSI uses tempdb to keep "snapshots" of records as they change and automatically performs garbage collection when the snapshots expire.
Tempdb usage tends to be a bottleneck with RCSI: under heavy load, all that swapping to disk can cause performance problems.
In-Memory OLTP does not use the same mechanisms as RCSI; it does not use tempdb for this.
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v0 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t1 | S1 & S3 read, get v0 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v0 v1 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t1 | S1 & S3 read, get v0 | |
t2 | S2 updates: v1 created | v0 is GC'd ASAP |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v1 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t1 | S1 & S3 read, get v0 | |
t2 | S2 updates: v1 created | v0 is GC'd ASAP |
t3 | S3 reads, gets v1 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v1 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t1 | S1 & S3 read, get v0 | |
t2 | S2 updates: v1 created | v0 is GC'd ASAP |
t3 | S3 reads, gets v1 | |
t4 | S1 BEGIN TRAN, updates: v2 created |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v1 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t1 | S1 & S3 read, get v0 | |
t2 | S2 updates: v1 created | v0 is GC'd ASAP |
t3 | S3 reads, gets v1 | |
t4 | S1 BEGIN TRAN, updates: v2 created | |
t5 | S2 & S3 read, get v1 | v1 still alive! |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v1 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t3 | S3 reads, gets v1 | |
t4 | S1 BEGIN TRAN, updates: v2 created | |
t5 | S2 & S3 read, get v1 | v1 still alive! |
t6 | S3 tries to update | ERROR -- Open update! |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v1 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t4 | S1 BEGIN TRAN, updates: v2 created | |
t5 | S2 & S3 read, get v1 | v1 still alive! |
t6 | S3 tries to update | ERROR -- Open update! |
t7 | S1 COMMIT TRAN | v1 GC'd ASAP |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t4 | S1 BEGIN TRAN, updates: v2 created | |
t5 | S2 & S3 read, get v1 | v1 still alive! |
t6 | S3 tries to update | ERROR -- Open update! |
t7 | S1 COMMIT TRAN | v1 GC'd ASAP |
t8 | S2 reads, gets v2 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t4 | S1 BEGIN TRAN, updates: v2 created | |
t5 | S2 & S3 read, get v1 | v1 still alive! |
t6 | S3 tries to update | ERROR -- Open update! |
t7 | S1 COMMIT TRAN | v1 GC'd ASAP |
t8 | S2 reads, gets v2 | |
t9 | S3 reads, gets v2 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v0 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t1 | S1 & S3 read, get v0 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v1 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t1 | S1 & S3 read, get v0 | |
t2 | S2 updates: v1 created | v1 overwrites v0 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v1 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t1 | S1 & S3 read, get v0 | |
t2 | S2 updates: v1 created | v1 overwrites v0 |
t3 | S3 reads, gets v1 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t1 | S1 & S3 read, get v0 | |
t2 | S2 updates: v1 created | v1 overwrites v0 |
t3 | S3 reads, gets v1 | |
t4 | S1 BEGIN TRAN, updates: v2 created | v2 overwrites v1 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t2 | S2 updates: v1 created | v1 overwrites v0 |
t3 | S3 reads, gets v1 | |
t4 | S1 BEGIN TRAN, updates: v2 created | v2 overwrites v1 |
t5 | S2 & S3 try to read | BLOCKED! |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t3 | S3 reads, gets v1 | |
t4 | S1 BEGIN TRAN, updates: v2 created | v2 overwrites v1 |
t5 | S2 & S3 try to read | BLOCKED! |
t6 | S3 still blocked... | No update run |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t4 | S1 BEGIN TRAN, updates: v2 created | v2 overwrites v1 |
t5 | S2 & S3 try to read | BLOCKED! |
t6 | S3 still blocked... | No update run |
t7 | S1 COMMIT TRAN | S2 & S3 unblocked, read v2 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t4 | S1 BEGIN TRAN, updates: v2 created | v2 overwrites v1 |
t5 | S2 & S3 try to read | BLOCKED! |
t6 | S3 still blocked... | No update run |
t7 | S1 COMMIT TRAN | S2 & S3 unblocked, read v2 |
t8 | S2 reads, gets v2 |
|-----------|----------------|---------------|---------------| S1 |------------------|---------------|-----------|-------------| S2 |-----------|-----------|----------|----|----------|---------| S3 v2 t0 t1 t2 t3 t4 t5 t6 t7 t8 t9
Time | Action | Consequences |
t4 | S1 BEGIN TRAN, updates: v2 created | v2 overwrites v1 |
t5 | S2 & S3 try to read | BLOCKED! |
t6 | S3 still blocked... | No update run |
t7 | S1 COMMIT TRAN | S2 & S3 unblocked, read v2 |
t8 | S2 reads, gets v2 | |
t9 | S3 reads, gets v2 |
We have two ways of accessing In-Memory OLTP tables: regular interpreted queries and native compiled code.
Native stored procedures are compiled as C code in DLLs. There is one DLL per stored procedure, and the minimum size of a stored procedure is 72KB.
Each DLL is loaded at the first execution. This means the first execution will be slower. To improve performance, build warm-up routines at database startup to cache important compiled stored procedures.
DLLs are not part of regular database backups; they should be maintained separately.
All query engine work is done at compile time rather than runtime. When a stored procedure DLL is created, SQL Server generates an execution plan (using OPTIMIZE FOR UNKNOWN to limit parameter sniffing problems).
There are no automatic query plan updates/recompilation. Recompiliation happens under three circumstances:
We cannot view actual execution plans for compiled stored procedures, and plans do not get stored in sys.dm_exec_cached_plans
. Getting plans involves some work.
Compiled stored procedures cannot:
Clustered indexes are gone. Clustered indexes (sort of) tell us the physical layout on disk of the data, but we don't care about how it's stored on disk--we never read from disk (after startup)!
A new, in-memory friendly, non-clustered index type now exists: Hash indexes.
Based on other V1 features Microsoft has released recently (especially Columnstore indexes), it is fair to say that In-Memory OLTP will undergo some improvement in the next version. Some likely candidates for improvement are:
Current best practice is to limit the in-memory size of all durable tables to 250 GB or less.
You should also expect ~2x data size requirements, peaking at up to 4x. This means that if you have 250 GB of in-memory table data, you should have up to 1 TB of RAM you can dedicate to supporting that in-memory table.
This is called "In-Memory OLTP" for a reason!
Given size limitations and V1 limitations, In-Memory OLTP might be useful for small OLAP systems, but the real benefit is in multi-writer scenarios. Good OLAP design means having only one writer, and many warehouses blow past 2TB of data.
In-Memory OLTP is equally intriguing and maddening. I want to recommend it now, but you really need to understand your systems in order to benefit from it, and it doesn't always work for every scenario.
Slides and code are available at http://www.catallaxyservices.com/presentations/In-Memory-OLTP
E-Mail: feasel@catallaxyservices.com
Twitter: @feaselkl