In-memory OLTP is a new feature introduced on SQL Server
2014.
Why
In-Memory?
This feature is used to improve OLTP database application performance.
Memory-optimized-tables are fully transactional, durable, and are accessed
using Transact-SQL in the same way as disk-based tables.
- ·
A query can reference both memory-optimized tables and
disk-based tables.
- ·
A transaction can update data in memory-optimized tables and
disk-based tables.
- ·
Stored procedures that only reference memory-optimized tables
can be natively compiled into machine code for further performance
improvements.
Benefits of this
feature:
- ·
In-Memory OLTP will improve performance best in OLTP with
short-running transactions.
- ·
Optimistic concurrency control that eliminates logical locks.
- ·
Lock free objects that eliminate all physical locks and latches.
Threads that perform transactional work don’t use locks or latches for
concurrency control.
- ·
Natively compiled stored procedures, which have significantly
better performance than interpreted stored procedures, when accessing a
memory-optimized table.
Deep dive into SQL
Server in-memory OLTP
It
mainly features two new data structures which are Memory-Optimized Tables, and
Natively-Compiled Stored Procedures.
Memory-Optimized Tables
Memory-optimized tables are fully
durable by default, and, like transactions on (traditional) disk-based tables,
fully durable transactions on memory-optimized tables are fully atomic,
consistent, isolated, and durable (ACID). Memory-optimized tables and natively
compiled stored procedures support a subset of Transact-SQL.
Natively-compiled stored
procedures
A natively-compiled stored procedure is
a SQL Server object that can access only memory-optimized data structures such
as memory-optimized tables, table variables, etc.
Here comes SQL server 2016 In-Memory OLTP
SQL Server 2016 offers significant
improvements in In-Memory feature.
Some of the important features with
enhancements
In-Memory Performance
SQL Server 2016 in-memory technology provides improved query
performance. For example, the same
transactions that run against SQL Server 2014 memory-optimized tables will run
30 times faster when run against SQL Server 2016 memory-optimized tables.
Supported size for In-Memory Tables
In SQL Server 2014, size of memory optimized tables is maximum of 256 GB of memory.
In SQL Server 2016 the recommended
maximum is 2 TB of memory for memory-optimized tables.
Schema and data changes
SQL Server 2014 in-memory OLTP does not allow
you to modify memory-optimized tables once they are created. If you have to
make changes to a memory-optimized
table schema or rebuild its indexes,
you must drop and re-create the memory-optimized table, using DROP
TABLE and CREATE
TABLE statements.
From SQL Server 2016 in-memory OLTP, this is
no longer a constraint; you can make changes to memory-optimized tables after
creation, using the ALTER
TABLE statement.
Transparent Data Encryption (TDE)
In SQL Server 2014, the
TDE feature is not compatible with
memory-optimized tables. That means data stored for memory-optimized tables on
a MEMORY_OPTIMIZED_DATA file-group are not encrypted
on disk.
TDE function is fully compatible with SQL
Server 2016 memory-optimized tables
AlwaysON
In SQL Server 2014, the visibility of data in
memory-optimized tables on secondary replicas is delayed by few transactions.
SQL Server 2016 memory-optimized tables; like
disk-based tables, all data is visible immediately to the user.
Below are the complete feature enhancements
from 2014 to 2016
|
Feature/Limit
|
SQL Server 2014
|
SQL Server 2016
|
|
Maximum combined size of
durable tables
|
256 GB
|
2 TB
|
|
LOB (varbinary(max),
[n]varchar(max))
|
Not supported
|
Supported*
|
|
Transparent Data
Encryption (TDE)
|
Not supported
|
Supported
|
|
Offline Checkpoint
Threads
|
1
|
1 per container
|
|
ALTER PROCEDURE /
sp_recompile
|
Not supported
|
Supported (fully online)
|
|
Nested native procedure
calls
|
Not supported
|
Supported
|
|
Natively-compiled scalar
UDFs
|
Not supported
|
Supported
|
|
ALTER TABLE
|
Not supported
(DROP / re-CREATE)
|
Partially supported
(offline – details below)
|
|
DML triggers
|
Not supported
|
Partially supported
(AFTER, natively compiled)
|
|
Indexes on NULLable
columns
|
Not supported
|
Supported
|
|
Non-BIN2 collations in
index key columns
|
Not supported
|
Supported
|
|
Non-Latin codepages for
[var]char columns
|
Not supported
|
Supported
|
|
Non-BIN2 comparison /
sorting in native modules
|
Not supported
|
Supported
|
|
Foreign Keys
|
Not supported
|
Supported
|
|
Check/Unique Constraints
|
Not supported
|
Supported
|
|
Parallelism
|
Not supported
|
Supported
|
|
OUTER JOIN, OR, NOT,
UNION [ALL], DISTINCT, EXISTS, IN
|
Not supported
|
Supported
|
|
Multiple Active Result
Sets (MARS)
(Means better Entity Framework support.)
|
Not supported
|
Supported
|
|
SSMS Table Designer
|
Not supported
|
Supported
|
Creating Memory optimized table
CREATE TABLE [Employee](
[EmployeeID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT =
1000000),
[Name] NVARCHAR(250) NOT NULL INDEX [IName] HASH WITH (BUCKET_COUNT =
1000000),
[EmployeeSince] DATETIME NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
Below are keywords used in above query.
MEMORY_OPTIMIZED = ON
This clause triggers the database engine
to consider this table as memory optimized table
NONCLUSTERED HASH
Memory optimized tables can have up to
eight non-clustered indexes, they don’t support clustered indexes. These
indexes don't duplicate data, but rather just point to the rows in the chain.
In-memory OLTP maintains these indexes online and they get created each time
during database recovery.
Only two types of Indexes can be created
on In-Memory tables, i.e. Non Clustered Hash Index and Non
Clustered Index (aka Range Index).
Hash index is an array of N buckets or
slots, each one containing a pointer to a row.
Non Clustered Indexes are inherently
Covering, and all columns are automatically INCLUDED in the Index.
BUCKET_COUNT
With index specification, you also need
to specify the BUCKET COUNT. The recommended value for this should be two
times the expected number of unique values of that column.
The value of BUCKET_COUNT represents the number of buckets a Hash index
has. We can think of a bucket as an index position in which a row address is
stored.
DURABILITY = SCHEMA_AND_DATA
With memory optimized tables you can
either specify DURABILITY = SCHEMA_AND_DATA or DURABILITY = SCHEMA_ONLY.
DURABILITY
= SCHEMA_AND_DATA is a
default option, with this option changes to table data are logged in the SQL
Server transaction log and data gets stored in a file-stream based file-group. So
that data can be recovered after an unexpected crash or a server restart.
DURABILITY
= SCHEMA_ONLY with this
option changes to table data are not logged, only structure or schema of table
is saved. After a server restart the table will be available, but it will be an
empty table.
---------------------------------------------------------------------------------------------
In-Memory
OLTP in Azure
In-Memory OLTP can be used to improve the performance of OLTP workload
in Premium
Azure SQL Databases without increasing the performance level. With In-Memory
OLTP in SQL Server, customers achieve up to 30x transactional performance
gains.
A memory-optimized table has one
representation of itself in active memory, in addition to the standard
representation on a hard drive. Business transactions against the table run
faster because they directly interact with only the representation that is in
active memory.
Premium databases created in November
2015 or later do support the In-Memory feature.
Check the table compatibility for
In-Memory using Memory Optimization Advisor.
Bringing this technology to the cloud
means customers will be able to take advantage of in-memory OLTP in a fully
managed database-as-a-service with 99.99% SLA.
