in-memory optimization in sql server: will my workload execute faster?
TRANSCRIPT
![Page 1: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/1.jpg)
In-Memory Optimizationin SQL ServerWILL MY WORK LOAD EXEC UTE FASTER?
Artemakis ArtemiouSenior SQL Server ArchitectMicrosoft SQL Server MVP, Technical AuthorFounder of “Artemiou Data Tools” InitiativeCDNUG Lead, INETA-EU Country Lead (CY)
https://www.aartemiou.comhttps://www.inmemoltpsim.com
August 2015
![Page 2: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/2.jpg)
What is In-Memory Optimization? (I)
• Integrated into the SQL Server Engine
• Optimized for OLTP
• Eliminates both Locks and Latches
• Introduces memory-optimized tables and natively-compiled stored procedures
![Page 3: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/3.jpg)
What is In-Memory Optimization? (II)
• Memory-Optimized Tables
• Rows in the table are read from and written to memory
• The entire table resides in memory
• Non-blocking multi-version optimistic concurrency control
• Durable & non-durable
• DURABILITY = SCHEMA_ONLY / SCHEMA_AND_DATA
• A second copy maintained on disk for durability (if enabled)
• Data in memory-optimized tables is only read from disk during database recovery
• Interoperable with disk-based tables
![Page 4: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/4.jpg)
What is In-Memory Optimization? (III)
• Natively-Compiled Stored Procedures
• Interpreted stored procedure: Compiled at first execution
• Natively compiled stored procedure: Compiled when it is created
• Compiled to native code: DLL (linked to the SQL Server process)
• Aggressive optimizations at compile time
• Can only interact with memory-optimized tables
• Limited support for T-SQL constructs
• Very fast execution
![Page 5: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/5.jpg)
What is In-Memory Optimization? (IV)
In-Memory
OLTP
compiler
Memory-optimized table
filegroupData filegroup
SQL Server.exe
In-Memory OLTP engine for
memory-optimized tables and
indexes
TDS handler and session management
Natively compiled
SPs and schema
Buffer pool for tables and
indexes
Proc/plan cache for ad-hoc T-
SQL and SPs
Client app
Transaction log
Interpreter for T-SQL, query
plans, expressions
Access methods
Parser,
catalog,
algebrizer,
optimizer
Query
Interop
In-Memory OLTP
component
Key
Existing SQL
component
Generated .dll
![Page 6: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/6.jpg)
Best-Suited Types of Workloads• High Data Insert Rate
• Examples: Smart Metering, System Telemetry
• Read Performance and Scale
• Example: Social Network Browsing
• Compute Heavy Data Processing
• Examples: Manufacturing supply chains or retailers
• Low latency Execution
• Examples: Online Gaming Platforms, Capital Markets
• Session State Management
• Example: Managing sessions (i.e. user requests, etc.) for a heavily-visited websites
![Page 7: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/7.jpg)
The “Question” from Business:
Will we benefit in terms of significant performance and time gain if we invest in this technology?
Answer from IT: It Depends
Why? Because of the workload types the Organization’s DBMS systems usually process.
![Page 8: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/8.jpg)
How Can an Organization Answer the Question?
• Reading Case Studies
• Generic Testing
• Memory Optimization Advisor Tool in SQL server
• Simulation of Actual Production Workload
• In-Memory OTP Simulator
• Easily test In-Memory OLTP against any workload and analyze the execution times and speedups.
![Page 9: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/9.jpg)
What is In-Memory OLTP Simulator? (I)• Created by Senior SQL Server Architect and MVP Artemakis Artemiou
• Part of the “Artemiou Data Tools” Initiative
• Enables the user to easily test the powerful In-Memory OLTP Engine of SQL Server 2014 via a simple and intelligent GUI
• Features three simulation modes:
• Disk-Based
• Memory-Optimized
• Memory-Optimized with Natively-Compiled Stored Procedure
• Available Editions
• Community Edition (free)
• Ultimate Edition
![Page 10: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/10.jpg)
What is In-Memory OLTP Simulator? (II)
Simulation Page of In-Memory OLTP Simulator (www.inmemoltpsim.com)
![Page 11: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/11.jpg)
Main Features of In-Memory OLTP Simulator
Feature Ultimate Edition Community Edition
Execute Standard Scenarios Yes Yes
Change No. of Records for Standard Scenarios Yes Yes
View Current Resource Usage Statistics Yes Yes
Simulation Statistics Yes Yes
Real-Time Resource Monitoring Yes Yes
Emergency Thresholds Yes Yes
Emergency Actions Yes Yes
Export to Image and Text Yes Yes
Multiple Simulation Databases Yes No
Create, Execute, Import & Export Custom Scenarios Yes No
Executive Report Yes No
For the full list of available features in all editions of In-Memory OLTP Simulator please visit:https://www.inmemoltpsim.com/features/
![Page 12: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/12.jpg)
Screenshots of In-Memory OLTP Simulator (I)• First Screen
![Page 13: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/13.jpg)
Screenshots of In-Memory OLTP Simulator (II)• Simulation Page (before running anything)
![Page 14: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/14.jpg)
Screenshots of In-Memory OLTP Simulator (III)• Simulation Page (after running multiple runs of the same simulation)
![Page 15: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/15.jpg)
Screenshots of In-Memory OLTP Simulator (IV)• Scenario Manager
![Page 16: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/16.jpg)
Screenshots of In-Memory OLTP Simulator (V)• Executive Report (only in Ultimate Edition)
![Page 17: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/17.jpg)
Screenshots of In-Memory OLTP Simulator (VI)• Custom Scenario in Scenario Manager (only in Ultimate Edition)
![Page 18: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/18.jpg)
Screenshots of In-Memory OLTP Simulator (VII)• Generate Simulation Database & Connect to SQL Instance
![Page 19: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/19.jpg)
Screenshots of In-Memory OLTP Simulator (VIII)• View Active Scenario Definition
![Page 20: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/20.jpg)
Screenshots of In-Memory OLTP Simulator (IX)• Emergency Thresholds
![Page 21: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/21.jpg)
Screenshots of In-Memory OLTP Simulator (X)• Current Resource Usage Statistics
You can view selected screenshots and videos at: https://www.inmemoltpsim.com/screenshots_videos
![Page 22: In-Memory Optimization in SQL Server: Will My Workload Execute Faster?](https://reader030.vdocuments.us/reader030/viewer/2022032513/55d18a2cbb61eb846f8b45a6/html5/thumbnails/22.jpg)
Resources• In-Memory OLTP Simulator Official Website
• https://www.inmemoltpsim.com/
• In-Memory Optimization in SQL Server
• https://msdn.microsoft.com/en-us/library/dn133186.aspx
• In-Memory OLTP Simulator Datasheet
• https://www.inmemoltpsim.com/files/InMemOLTPSim_DataSheet.pdf
• In-Memory OLTP Simulator Videos
• https://goo.gl/dhOZKw
• Artemiou Data Tools
• https://www.aartemiou.com/datatools
• In-Memory OLTP – Common Workload Patterns and Migration Considerations
• https://msdn.microsoft.com/en-us/library/dn673538.aspx