OVERVIEW
MODULE 1: IN-MEMORY OLTP OVERVIEW
In-Memory OLTP is a key feature in the in-memory offerings of SQL Server. In this module, we discuss user scenarios and hardware trends, such as: decreasing memory cost; many-core processors; stalling CPU clock rate that prompted the key architectural pillars of memory optimization; and no lock/latch concurrency control and native compilation. We’ll also discuss how In-Memory OLTP is integrated into SQL Server.
MODULE 2: IN-MEMORY OLTP FOR DBAS – DEEP DIVE
SQL Server introduces in-memory technology for optimizing OLTP workloads. In this module, we take a deep dive into how in-memory tables and indexes are managed in memory and in storage. You will also learn how to provision and control memory usage, how durability and high availability is achieved, and how these constructs and operations are integrated together with disk-based tables to provide you a seamless experience.
Module 3: In-Memory OLTP for Developers – Deep Dive
SQL Server introduces in-memory technology for optimizing OLTP workloads. In this module, we will focus on the new concepts for database developers. We will discuss the new memory-optimized tables and the new memory-optimized hash and non-clustered indexes. You will learn how to access memory-optimized tables using interpreted Transact-SQL, and how to optimize data access using the new natively compiled stored procedures. Finally, we will discuss how to work with the new lock-free transaction semantics for In-Memory OLTP.
MODULE 4: IN-MEMORY OLTP MIGRATION – DEEP DIVE
SQL Server In-Memory OLTP is a key new feature included in SQL Server. It has been proven to dramatically increase OLTP performance without hardware changes. However, there are numerous nuances in In-Memory OLTP. These nuances mean that not all workloads can derive benefits from In- Memory OLTP. For the workloads that can benefit, care must be taken so that the migration process will enable the workload to take maximum advantage of In-Memory OLTP while keeping efforts down to a minimum. This module uses a sample workload to demonstrate how to plan to migrate to In- Memory OLTP.
MODULE 5: IN-MEMORY OLTP PERFORMANCE TROUBLESHOOTING
You’ve migrated parts of your application to In-Memory OLTP, but now you’re not seeing a serious performance improvement, or maybe you’re even seeing a performance degradation! What’s going on? In this module, we walk you through several performance problems we have seen with customers, and demonstrate how to diagnose and resolve the issues to ultimately obtain the performance improvement you’re looking for from In-Memory OLTP. We will address a variety of issues, including log IO bottlenecks, index tuning, and query plan problems.
MODULE 6: IN-MEMORY OLTP: MEMORY/STORAGE MONITORING AND TROUBLESHOOTING
Now that you have your application migrated to the In-Memory OLTP Engine and you’re getting good performance with your workload, your main focus is to keep it operational. This module walks through various scenarios, such as running out of memory, garbage collection of both memory and storage, and corrupt backups. We will also discuss how to manage it using familiar tools such as PerfMon, Profiler, DMVs, XEvents, and Management Pack to monitor your workload.
MODULE 6: BUFFER POOL EXTENSION
BPE takes advantage of lightning-fast storage (in the form of Solid State Storage) to effectively and economically expand the amount of usable memory for the system
MODULE 7: UPDATEABLE COLUMNSTORE INDEX
The addition of updateable columnstore in Microsoft SQL Server offers order of magnitude and more query performance. In this session, we will discuss the impact of this to you and your customers. We’ll also talk about internals, how columnstore works, the differences from row store, and about the metadata that surfaces this new information.
MODULE 8: OPTIMIZER & QUERY PLAN ENHANCEMENTS
There are two big changes in SQL Server that will impact your query plans, particularly for data warehousing applications. In this module, you will learn how to manage statistics on large tables with the new capability in SQL Server to define and update stats by partition. We will also discuss the
cardinality estimator drives on how plans are costed and were re-architected in SQL Server. We will then dive into details on the reason for these changes and how they might affect you.
• New Cardinality Estimator
• Incremental Stats
MODULE 9: RESOURCE GOVERNOR FOR I/O
Resource Governor Enhancement for Physical I/O control
How to Set CPU, MEMORY, IO through Resource Governor
MODULE 10: MISCELLANEOUS ENHANCEMENTS
• Delayed Durability
• Query Store and Query Statistics
• Batch Mode Support
• Temporal Tables
• Stretch Database
• Truncate table on partition
• Always Encrypted Data
• Dynamic Data Masking
• Row Level Security
• FOR JSON OUTPUT
• Backup & Restore Enhancements
• System View Enhancements
• Security Enhancements
• Lock Priority on Online Operations