1 / 24

SQLintersection Session: Tuesday, 8:30am –9:30am Hitting the Turbo Button with In-Memory OLTP

SQLintersection Session: Tuesday, 8:30am –9:30am Hitting the Turbo Button with In-Memory OLTP. Justin Randall jrandall@sentryone.com. Speaker : Justin Randall. Senior Professional Services Consultant 30+ years experience as a data professional DB2, Sybase, SQL Server

joannal
Télécharger la présentation

SQLintersection Session: Tuesday, 8:30am –9:30am Hitting the Turbo Button with In-Memory OLTP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQLintersectionSession: Tuesday, 8:30am–9:30amHitting the Turbo Button with In-Memory OLTP Justin Randall jrandall@sentryone.com

  2. Speaker: Justin Randall • Senior Professional Services Consultant • 30+ years experience as a data professional • DB2, Sybase, SQL Server • Blog: http://blogs.sentryone.com/author/JustinRandall • Twitter: @jh_randall

  3. Reminder: Intersect with Speakers and Attendees • Tweet tips and tricks that you learn and follow tweets posted by your peers! • Follow: #SQLintersection and/or #DEVintersection • Doors open at 7:00 pm • Trivia game starts at 7:30 pm Winning team receives something fun! • Raffle at the end of the night Lots of great items to win including a seat in a SQLskills Immersion Event! • The first round of drinks is sponsored by SentryOneand SQLskills • Join us – TUESDAY Evening – for SQLafterDark &

  4. Overview • Introduction Memory-Optimized Tables, aka In-Memory OLTP is one of several SQL Server technologies designed to help make your applications run faster. In this session we will demonstrate the speed improvements possible using memory-optimized tables, present common use cases, and look at features you can leverage in your environment. • Agenda • The Technology • Requirements • Target Workloads • Memory Optimized Table Essentials • Accessing Memory Optimized Tables

  5. The Technology • Tables and Indexes fully in memory, always • Durable and Non-Durable (schema-only) • Multi-Version Concurrency Control (Optimistic Concurrency ) • Row versioning vs. locks & latches • Operations on memory-optimized tables are still logged, but less so • Natively compiled tables and table variables • New index structures • Natively compiled modules (optional) • Stored Procedures, triggers, and user-defined scalar functions

  6. Performance Gains Demo

  7. SQL Server Requirements • SQL Server 2016 SP1 (or later), any edition, or Azure SQL DB • Enough memory to hold the data in memory-optimized tables and indexes, as well as additional memory to support the online workload. • Free disk space 2X the size of your durable memory-optimized tables. • Processors need to support the instruction cmpxchg16b. All modern 64-bit processors do so

  8. Target Workloads * In-Memory OLTP Common Workload Patterns and Migration Considerations White Paper

  9. Memory-Optimized Tables • Entire table resides in memory all the time • Compiled into DLLs • Interoperability with on-disk tables • Durability Options: • Schema and Data (durable) - persisted on disk over restarts – default option(delayed durability is supported) • Schema Only (non-durable) – data will not survive SQL Server restart

  10. Memory-Optimized Table Limitations • Cross-database transactions not allowed • Cannot access linked servers • DML triggers must be created as natively compiled modules • Bulk logging and minimal logging not supported • Keyset & dynamic cursors degraded to static and read-only • MERGE INTO target, where target is a memory-optimized table • Unsupported data types* *https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/supported-data-types-for-in-memory-oltp

  11. Memory-Optimized Table Indexes • Every Memory-Optimized table must have at least one index • All indexes are Nonclustered. Three types are supported: • Hash index • Nonclustered (range) index • Columnstore index • Exist only in active memory • Rebuilt when the database is brought back online • SQL UPDATE statements that change indexes are not logged • Durable tables must have a declared primary key • Index supporting primary key is the only unique index Resource: In-Memory OLTP – Row Structure and Indexes - http://bit.ly/2A1sj8F

  12. Memory-Optimized Table Indexes • Hash index is preferred for queries checking exact valuesSELECTListPriceFROM ProductWHEREProductId= 702; • Non-clustered (range) index is preferred when: • Queries have an ORDER BY clause on the indexed column • Queries use only the leading column(s) of a multi-column index • Queries whose WHERE clause checks the index for: • An inequality: WHERE Color <>'Silver'; • A range value: WHEREListPrice> 1295;

  13. Memory-Optimized Table Variables • Stored in memory only – no on-disk component • No I/O activity • No tempdb utilization or contention • Can be passed into a stored procedure • Must have at least one index CREATETYPEdbo.ProductType_inmem--create table type ASTABLE(ProductIDINTNOTNULL, ProductNbrNVARCHAR(25)NOTNULL, INDEXIX_ProductIDNONCLUSTERED (ProductID) ); DECLARE @ProductTblValdbo.ProductType_inmem--create table variable

  14. Memory-Optimized Tables & Indexes Demo

  15. Accessing Memory-Optimized TablesInterpreted T-SQL Interpreted T-SQL • Access both memory-optimized tables and on-disk tables • Some T-SQL construct limitations when accessing in-memory tables:Changes with every version of SQL Server, see documentation for the version you are using • Best uses: • Join memory-optimized and on-disk tables • Queries suited for parallel operations • Ad-hoc queries • Queries accessing lots of rows, i.e. cause table scans/range scans

  16. Accessing Memory-Optimized TablesNatively Compiled Procs, Triggers, & User-Defined Scalar Functions • Compiled to machine code • Only access memory-optimized objects • Limitations on T-SQL constructs that can be used remain. Reviewhttps://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transact-sql-constructs-not-supported-by-in-memory-oltp • Best uses: • When OLTP performance needs to be maximized • Queries do not benefit from parallel operations • Queries access a limited number of rows • Procedures involving complex logic and many statements

  17. Natively Compiled User-Defined Scalar Functions • Work almost identically to interpreted scalar UDFs • Must be schema-bound (WITH SCHEMABINDING) and atomic ( BEGIN ATOMIC WITH) • Can be used in expressions, with EXECUTE, and in a natively compiled proc CREATEFUNCTION dbo.MyInMemFunction( @Value int) RETURNSvarchar(8) WITHNATIVE_COMPILATION,SCHEMABINDING AS BEGINATOMICWITH (TRANSACTIONISOLATIONLEVEL=SNAPSHOT,LANGUAGE=N'English’) DECLARE @ReturnValue varchar(8); --<function code goes here> RETURN (@ReturnValue); END

  18. Accessing Memory-Optimized Tables Demo

  19. Management Studio Can Help • Memory Usage by Memory-Optimized Objects Report • Memory Optimization Advisor • On-Disk tables • Native Compilation Advisor • Stored procedures • User defined scalar functions

  20. Summary For all but the simplest efforts, implementing In-Memory OLTP is a project • Understand In-Memory requirements and limitations • Evaluate candidate workloads and objects against recommended target workloads • Use SSMS wizards as a sanity check • Build core memory-optimized tables, table variables • Start with interpreted stored procedures and standard nonclustered indexes • Test, Test, Test

  21. SQL Server 2016 In-memory OLTP (Books Online)https://msdn.microsoft.com/en-us/library/dn133186.aspx Inside SQL Server In-Memory OLTP – Bob Ward videohttps://www.youtube.com/watch?v=P9DnjQqE0Gc SQL Server In-Memory OLTP Internals for SQL Server 2016 (ebook)http://bit.ly/2lSrh8E Quick Start 1: In-memory OLTP Technologies for faster T-SQL Performancehttps://msdn.microsoft.com/en-us/library/mt694156.aspx In-Memory OLTP – Common Workload Patterns and Migration Considerations https://msdn.microsoft.com/library/dn673538.aspx Resources

  22. Memory-Optimized Tableshttps://msdn.microsoft.com/en-us/library/dn511014.aspx Faster Temp Table and Table Variable Using Memory Optimizedhttp://bit.ly/2pcuKU3 Scalar User Defined Functions for In-Memory OLTPhttps://msdn.microsoft.com/en-us/library/dn935012.aspx SQL Server Support for In-Memory OLTPhttp://bit.ly/2EGGCW5 Migrating to In-Memory OLTPhttps://msdn.microsoft.com/en-us/library/dn247639.aspx Guide to Query Processing for Memory Optimized Tableshttps://msdn.microsoft.com/en-us/library/dn205319.aspx Resources

  23. Questions? Don’t forget to complete an online evaluation! Hitting the Turbo Button with In-Memory OLTP Your evaluation helps organizers build better conferences and helps speakers improve their sessions. Thank you!

  24. Save the Date www.SQLintersection.com Week of December 2, 2018 We’re back in Vegas baby!

More Related