Transcript Architectures and Case Studies with In

CREATE TABLE [Customer](
[CustomerID] INT NOT NULL
PRIMARY KEY NONCLUSTERED,
[Name] NVARCHAR(250) NOT NULL,
[CustomerSince] DATETIME2 NULL,
INDEX [ICustomerSince] NONCLUSTERED
(CustomerID, CustomerSince)
)
WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA);
This table is durable (default).
Non-durable tables:
DURABILITY=SCHEMA_ONLY
CREATE PROCEDURE [dbo].[InsertOrder] @id INT, @date DATETIME2
WITH
NATIVE_COMPILATION,
SCHEMABINDING
AS
BEGIN ATOMIC
WITH
(TRANSACTION
ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N‘Dutch')
-- insert T-SQL here
END
Durabilit
y level
Configuration
Pros/Cons
Scenarios
Full
durability
Default
Pro:
• Every committed change is
guaranteed to survive failure
Con:
• Latency impact: every commit
requires log IO
Default: most scenarios need
full durability
Delayed
durability
• Transaction commit time
DURABILITY=SCHEMA_AND_DATA
Pro:
COMMIT WITH
• Low latency due to no log IO in
(DELAYED_DURABILITY=ON)
transaction execution path
• Atomic block of native procedure
• Efficient log IO due to batching
BEGIN ATOMIC WITH
Con:
(DELAYED_DURABILITY=ON, …)
• Limited data loss on failure
• Database level
ALTER DATABASE CURRENT SET
(usually ~60K or ~1ms worth)
Low latency requirements
Table creation
• Transient data such as
session state
• Caching
• ETL (staging tables)
DELAYED_DURABILITY=FORCED
Nondurable
tables
DURABILITY=SCHEMA_ONLY
Pro:
• No IO at all
Con:
• Lose data on failure
Can accept some data loss
OR
Copy of recent data exists
elsewhere in case of failure
AlwaysOn auto-failover (sync
replicas) with low latency
In-Memory OLTP perf demo
Memory-optimized table variables and temp tables
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Domain controllers
Domain controllers
Sensors
Temporal memoryoptimized table
https://github.com/Microsoft/sql-server-samples/tree/master/samples/applications/iot-smart-grid
Applications / Web servers
Memory-optimized table variables and temp tables
Data Warehouse – SQL Server 2016
www.microsoft.com/itprocareercenter
www.microsoft.com/itprocloudessentials
www.microsoft.com/mechanics
https://techcommunity.microsoft.com
http://myignite.microsoft.com
https://aka.ms/ignite.mobileapp