Changes in In-Memory OLTP in SQL Server 2016

PROBLEM

SQL Server 2014 ile hayatımıza giren In-Memory OLTP özelliği ve bu özelliğin getirdiği kısıtlar doğrultusunda OLTP sistemlerde yaşanan problemler.

ÇÖZÜM

SQL Server 2016 sürümü ile In-Memory OLTP özelliğinin ciddi sıkıtntılar oluşturan kısıtlarının bir çoğu ortadan kaldırılmıştır.

Örneğin ; Tablolarda ALTER süreçlerinin gerçekleştirilebilmesinin sağlanması vb. gibi…

 

UYGULAMA

SQL Server 2014 ile SQL Server 2016 arasında ki In-Memory OLTP değişiklikleri aşağıda belirtilmiştir.

Features SQL Server 2014 SQL Server 2016
Maximum
memory for memoryoptimized tables
256 GB 2 TB
Collation Characters
columns must use bin2Collation which are part of index key.
This
restriction is removed andCharacter columns using anyCollation can be part of
index.
Schema
and data changes
Not
allowed any changes afterTable creation.
Alter
table statement can beused to add, drop or alter columns.
Parallel
plans
Not
supported in this version
Operations
that uses hash indexescan be performed in parallel.
Transparent
data encryption
Not
supported in this version
Supported
in sql 2016 andMemoryoptimized tables datacan be encrypted
LOB
datatypes
Not
supported
Supported
Left
and Right outer join
Not supported Supported
Select
Distinct
Not Supported Supported
Subqueries
in clause ofselect statement
Not supported Supported
Nested
stored procedure calls
Not supported Supported
UNION
and UNION ALL
Not supported Supported
Foreign
keys
Not supported Supported
Multiple
log reader threads
Used
only one log reader threadper database
Allow
multiple threads for bothrecovery and checkpoint
no
of sockets
limited
scalability withmultiple socket machines
efficient
scalability with a4-socket machine.
AlwaysOn Data
visibility of in-memory oltp onSecondary replica was delayed byfew transaction
this
limitation is removed in sql server2016 and data from both disk-based andmemory-based visible to user at same time.
DML
Triggers
Not
supported
Partially
supported (after,natively compiled)
Index
on null column
Not
supported
Supported

Leave a Reply

Your email address will not be published. Required fields are marked *