Transcript [Demo] - InMemoryOLTP_MartinCatherall

In-Memory OLTP (IMOLTP)
What Can It Do For Me?
Martin Catherall
Regional Mentor (APAC)
MVP – Data Platform
Martin Catherall
Senior Consultant
RockSolid SQL
UXC Eclipse
MVP – Microsoft Data Platform
PASS Regional Mentor APAC
In-Memory OLTP – What Can It Do For Me.
INTRODUCTION
SQL Server Change






Compression (Row or Page)
Extended Events (2008)
Transparent Data Encryption (2008)
AlwaysOn (2012)
Columnstore (2012)
In-Memory (2014)
IMOLTP - History
 Brought out SQL Server 2014
 Reasonable use cases (many limitations)
 Enhanced SQL Server 2016
 Less limitations
 Increased functionality
 Available in Azure SQL Database
 Not much changed needed (in a lot of cases)
Some Thoughts
 SQL Server Thrives with Memory
 As much as possible.
 But my database already fits into memory.
 Yes – but that’s still ‘disk based’
 Pay attention to your data growth.
 This will dictate your (growing) memory
requirements.
 We’ll still need memory for
 Normal SQL Server
 The Operating System.
Some (More) Thoughts
 Low Latency (nanoseconds v milliseconds)
 High Concurrency (Pessimistic / Optimistic)
 New concurrency model.
 Multi Version Concurrency Control (MVCC)
 Low Contention.
 Durability (The “D” in ACID)
 Can use to solve existing problems.
 Although not all (Test! Test! Test!)
In-Memory OLTP – What Can It Do For Me.
ENABLING IN-MEMORY
How to enable In-Memory
 Enabled at the database level.
 Simply Add a new filegroup
 Use familiar syntax. (ALTER DATABASE)
 Add an In-Memory Container.
 This is a folder on the file system.
 Use familiar syntax (ALTER DATABASE)
The IMOLTP filegroup
 Basically a directory on the file system.
 Which might get used!
 Only one IMOLTP filegroup allowed.
 Need for durability.
 So can data can be ‘recreated’ at startup.
 High Availability.
 Logging is slightly different
 The Checkpoint Worker thread.
Demonstration
Enabling In-Memory for the database.
In-Memory OLTP – What Can It Do For Me.
MULTI-VERSION
CONCURRENCY
CONTROL
MVCC
MVCC – A New Concurrency Model
 Data is immutable.
 Insert only.
 Isolation Modes
 SNAPSHOT – the default
 Read Committed
 Serializable.
MVCC – Records
 Append ONLY
In flight Record
Begin TimeStamp
Transaction ID
Committed Record
Begin TimeStamp
End TimeStamp
In-Memory OLTP – What Can It Do For Me.
CREATING TABLES
Creating Tables
 Much the same as “Disk Based Tables”
 There are limitations.
 These are reducing
 Durable or Non-Durable
 Migration. (memory optimization Advisor)
Demonstration
Memory Optimization Advisor
Natively Compiled Objects
 Slight Different Syntax
 But very similar.
In-Memory OLTP – What Can It Do For Me.
ACCESSING DATA
TSQL or Natively Compiled Stored Procedures?
Converting Code
Converting Code can be Challenging!
 Subquery support in SELECT statements in SQL2016
 Unfortunately, not yet in update/delete.






CASE – would be great to see
For example workarounds see.
https://msdn.microsoft.com/en-us/library/dn629453.aspx
https://msdn.microsoft.com/en-us/library/mt757375.aspx
https://msdn.microsoft.com/en-us/library/dn579376.aspx
https://msdn.microsoft.com/en-us/library/dn579375.aspx
 Feel free to share any of yours 
 See also
In-Memory OLTP – What Can It Do For Me.
MONITORING IN-MEMORY
In-Memory OLTP – What Can It Do For Me.
TEMP TABLE REPLACEMENT
In-Memory OLTP – What Can It Do For Me.
BACKUP AND RESTORE
Resources.
 http://bit.ly/2eqBrYE (Cheap Ram Changes)







http://bit.ly/1bSsY7o (Bob Beauchemin)
http://bit.ly/2efItmt (SQL Team - 2014)
http://bit.ly/1lspXr5 (unsupported constructs - MSDN)
http://bit.ly/2dMZukL (Migration - MSDN)
http://bit.ly/2cSWRNP (Temp Object Replacement)
http://bit.ly/2e6l8WE (2016 White paper – download)
http://bit.ly/2dmfjmb (free ebook (2014) - download)
Don’t Forget
• Online Evaluations
• www.sqlsaturday.com/572/sessions/sessionevaluation.aspx
• www.sqlsaturday.com/572/eventeval.aspx
• Submit for raffles by 3:30PM
QUESTIONS
Please Support Our Sponsors
SQL Saturday is made possible with the generous support of these sponsors.
You can support them by opting-in and visiting them in the sponsor area.