2016 SQL Server In memory OLTP

DDR memory Q2 2014 – Faster,lower voltage,memory cheaper

Insert –

Take Page_EX latch

Query datapage header

add row

update page header

Release latch

80% of time in Storage Engine and Relational Engine

2014 Limitations

– Enterprise only

– 256GB per database

– Removal of in-memory FG not allowed

– No table/index changes allowed

– Row size limit is 8060


– 2TB of durable tables per database

2014 Garbage Collection process is not efficient enough

SQL Server 2016

– Storage management is decoupled from FileStream

– Unused files can be reused immediately after they are derference from the transaction log

2016 now allows TDE with In-memory OLTP

SQL Server 2014 scales to 2 sockets/64 cores

More cores, more log records,Single “Offline Checkpoint” thread

SQL Server 2016

Multiple “Offline Checkpoint” threads – one per container

SQL Server 2016

– Alter is supported

– Alter proc and sp_recompile is support

– Alter Table – add/alter/drop column/constraint

– Alter index ??

Alter table is offline operation, 2x space usage

sp_rename still not supported

SQL Server 2014 join MOT to Colmnstore get Query DOP 1

SQL Server 2016

– Parallel plans are supported

– Join columnstore with MOT

– Realtime analytics

SQL Server 2016

– DML triggers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s