Hekaton Developer Deep Dive Sunil Agarwal DBIB307 Memory optimized table and index structures Native compilation of business logic in stored procedures Hekaton is fully integrated into SQL Server ID: 389862
Download Presentation The PPT/PDF document "SQL Server "" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1Slide2
SQL Server "Hekaton": Developer Deep Dive
Sunil Agarwal
DBI-B307Slide3
Memory optimized table and index structures
Native compilation of business logic in stored procedures
“Hekaton” is fully integrated into SQL Server
Latch- and lock-free data structures
Hekaton Recap
Project “Hekaton” adds in-memory technology to boost performance of OLTP workloads in SQL ServerSlide4
Memory optimized tables and indexesSlide5
Create Table DDL
CREATE
TABLE [Customer]( [CustomerID] INT NOT
NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 1000000), [Name]
NVARCHAR(250) NOT NULL INDEX
[IName
]
HASH WITH
(
BUCKET_COUNT = 1000000), [CustomerSince] DATETIME NULL)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
This table is memory optimized
This table is durable
Secondary Indexes are specified inline
Hash IndexSlide6
Memory Optimized Table CreationSlide7
Memory Optimized Tables and Indexes
90,150
Susan
Bogota
50,
∞Jane
Prague
100, 200
John
Paris
200,
∞
John
Beijing
Timestamps
Name
Chain
ptrsCity
Hash index on City
Hash index on Name
Garbage Collection
Removes Unused RowsSlide8
Limitations in Initial Release
Optimized for high-throughput OLTPNo DML triggers
No XML and no CLR data types
Optimized for in-memoryRows are at most 8060 bytes – no off row dataNo Large Object (LOB) types like varchar(max)Scoping limitationsNo FOREIGN KEY and no CHECK constraintsNo IDENTITYNo schema changes (ALTER TABLE) – need to drop/recreate tableNo add/remove index – need to drop/recreate tableSlide9
Accessing memory optimized tablesSlide10
Create Procedure DDL
CREATE
PROCEDURE [dbo].[InsertOrder] @id INT, @date
DATETIME WITH NATIVE_COMPILATION, SCHEMABINDING,
EXECUTE AS OWNERAS BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL =
SNAPSHOT,
LANGUAGE
=
'us_english') -- insert T-SQL hereENDThis proc is natively compiledNative procs must be schema-boundAtomic blocks Create a transaction if there is none
Otherwise, create a savepoint
Execution context is required
Session settings are fixed at create timeSlide11
Procedure CreationSlide12
Accessing Memory Optimized Tables
Natively Compiled
Procs
Access only memory optimized tables
Maximum performance
Limited T-SQL surface area
When to useOLTP-style operationsOptimize performance critical business logic
Interpreted T-SQL
Access (
InterOP
)
Access both memory- and disk-based tables Less performantVirtually full T-SQL surfaceWhen to useAd hoc queriesReporting-style queriesSpeeding up app migration
Eliminating Latch ContentionSlide13
Demo
Accessing Memory Optimized TablesSlide14
Transactions on memory optimized tablesSlide15
Hekaton Concurrency Control
Multi-version data store
Snapshot-based transaction isolation
No
TempDB
Conflict detection to ensure isolation
No deadlocks
No locks, no latches, minimal context switches
No blocking
Multi-version
OptimisticSlide16
Supported Isolation Levels
SNAPSHOTReads are consistent as of start of the transaction
Writes are always consistent
REPEATABLE READRead operations yield same row versions if repeated at commit timeSERIALIZABLETransaction is executed as if there are no concurrent transactions – all actions happen at a single serialization point (commit time)Slide17
Example: Write conflict
Time
Transaction
T1 (SNAPSHOT)Transaction T2 (SNAPSHOT)1BEGIN
2BEGIN3
UPDATE t SET c1=‘bla’ WHERE c2=1234
UPDATE t SET c1=‘bla’ WHERE c2=123 (write conflict)
First writer winsSlide18
Guidelines for usage
Declare isolation level – no locking hints
Use retry logic to handle conflicts and validation failures
Avoid using long-running transactionsSlide19
Cross-container transactions
Disk-based tables
Memory-optimized tables
Tab1
Tab
n
Tab1Tabm
Regular
Tx
context
Hekaton
Tx contextSlide20
Disk-based
Memory optimized
Usage recommendations
READCOMMITTED
SNAPSHOTBaseline combination – most cases that use READCOMMITTED today
READCOMMITTEDREPEATABLEREAD/SERIALIZABLEData migration
Hekaton-only InteropREPEATABLEREAD/SERIALIZABLE
SNAPSHOT
Memory-optimized table access is INSERT-only
Useful for data migration and if
no concurrent writes on memory-optimized tables (e.g., ETL)
Disk-basedMemory optimizedSNAPSHOTAny isolation level
REPEATABLEREAD/SERIALIZABLE
REPEATABLEREAD/SERIALIZABLE
Supported isolation level combinations (V1)
Unsupported isolation level combinations (V1)Slide21
Demo
Transaction IsolationSlide22
Migration ExamplesSlide23
Retry Logic for Transaction Failures
Failures causing transaction abort
Write conflicts, validation failures
Aborted transactions need to
be retried
Solution: implement retry logic
Server-side retry avoids changes to client appsSlide24
Retry Logic for Transaction FailuresSlide25
Retry Logic for Transaction Failures
CREATE
PROCEDURE usp_my_procedure @param1 type1, @param2 type2, ...ASBEGIN
ENDSlide26
Retry Logic for Transaction Failures
CREATE
PROCEDURE usp_my_procedure @param1 type1, @param2 type2, ...ASBEGIN DECLARE
@retry INT = 10 WHILE (@retry >
0) BEGIN
ENDENDSlide27
Retry Logic for Transaction Failures
CREATE
PROCEDURE usp_my_procedure @param1 type1, @param2 type2, ...ASBEGIN DECLARE
@retry INT = 10 WHILE (@retry > 0)
BEGIN BEGIN TRY
SET @retry = 0 END TRY
BEGIN
CATCH
SET
@retry -= 1 END CATCH ENDENDSlide28
Retry Logic for Transaction Failures
CREATE
PROCEDURE usp_my_procedure @param1 type1, @param2 type2, ...ASBEGIN DECLARE
@retry INT = 10 WHILE (@retry > 0)
BEGIN BEGIN TRY
SET @retry = 0 END TRY BEGIN
CATCH
SET
@retry
-=
1 IF (@retry > 0 AND error_number() in (41302, 41305, 41325, 41301, 1205)) IF (@@TRANCOUNT>0)
ROLLBACK TRANSACTION
ELSE THROW END CATCH
ENDEND
Deadlock
(for disk-based tables)
Hekaton-specific
error codesSlide29
Retry Logic for Transaction Failures
CREATE
PROCEDURE usp_my_procedure @param1 type1, @param2 type2, ...ASBEGIN DECLARE
@retry INT = 10 WHILE (@retry > 0)
BEGIN BEGIN TRY EXEC usp_my_native_proc
@param1, @param2, ... SET
@retry = 0
END
TRY
BEGIN CATCH SET @retry -= 1 IF (@retry > 0 AND error_number() in (41302, 41305, 41325, 41301, 1205)) IF
(@@TRANCOUNT>0) ROLLBACK
TRANSACTION ELSE
THROW END CATCH
ENDENDSlide30
Workaround Example: IDENTITY
IDENTITY and SEQUENCE are used for surrogate key generation
Not supported for memory-optimized tables and natively compiled stored procedures
Solution: wrapper stored procedure using SEQUENCESlide31
Workaround Example: IDENTITY
CREATE
TABLE
t1( c1 INT NOT NULL IDENTITY ..., c2 INT,
c3 DATE)
CREATE
TABLE
t1
(
c1
INT NOT NULL ..., c2 INT, c3 DATE)WITH (MEMORY_OPTIMIZED=ON)
Before migration:
After migration:
CREATE
PROC
usp_insert
@c2
INT
,
c3
DATE
AS
BEGIN
INSERT
INTO
t1
VALUES
(
@c2
,
@c3
)
END
CREATE
SEQUENCE usq_t1
AS
INT
START
WITH
1 INCREMENT
BY
1
CREATE
PROC
usp_insert
@c2
INT
,
c3
DATE
AS
BEGIN
DECLARE
@c1
INT
=
NEXT
VALUE
FOR
usq_t1
INSERT
INTO
t1
VALUES
(
@c1
,
@c2
,
@c3
)
ENDSlide32
Recap
Memory optimized tables and indexesAre natively compiled on create
New hash indexes – range indexes in pipeline for RTM
Multi-versioned data storeAccessing memory optimized tablesNatively compiled stored procedures – best performance, but T-SQL limitationInterpreted T-SQL access – full T-SQL surface area, and joins with disk-based tablesTransaction semanticsMulti-versioned, snapshot-based isolationOptimistic: no locking – conflict detectionCross-container transactions – limitations on isolation levelsSlide33
Related content
Breakout Sessions
DBI-308
-
Microsoft SQL Server In-Memory OLTP Project "Hekaton": Management Deep Dive 6/6/2013 @ 10:15 AMSlide34
Track Resources
@
sqlserver
mva
Microsoft Virtual Academy
SQL Server
Website
Get Certified!
Hands-On Labs
Download Data Explorer
Download Geoflow
Windows AzureSlide35
msdn
Resources for Developers
http://microsoft.com/msdn
Learning
Microsoft Certification & Training Resources
www.microsoft.com/learning
TechNet
Resources
Sessions on Demand
http://channel9.msdn.com/Events/TechEd
Resources for IT Professionals
http://microsoft.com/technet Slide36
Complete an evaluation on CommNet and enter to win!Slide37
Evaluate this session
Scan this QR code
to evaluate this session and be automatically entered in a drawing to
win a prize
Required Slide
*delete this box when your slide is finalized
Your MS Tag will be inserted here during the final scrub. Slide38
© 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.