Course Description
Locking is a feature in SQL Server 2016 that helps ensure the physical integrity of each transaction. This course covers how to implement and troubleshoot locking, as well as how to minimize, handle, and remediate deadlocks. This course also covers memory-optimized tables, which are used to improve the performance of transaction processing, as well as native stored procedures - a feature used to enable efficient execution of the queries. This course is one of a series of courses that cover the objectives for 70-762: Developing SQL Databases exam.
Learning Objectives
Optimize Locking Behavior
- start the course
- provide an overview of locking
- discuss the types of concurrency and their effects
- describe deadlock behavior
- minimize deadlocks
- demonstrate how to handle deadlocks
- demonstrate how to remediate, detect, and end deadlocks
- demonstrate how to troubleshoot locking issues
- describe lock escalation behaviors
Memory-optimized Tables
- provide an overview of memory-optimized tables and discuss use cases for memory-optimized tables vs. traditional disk-based tables
- create memory-optimized tables
- optimize performance by adjusting durability settings
- describe considerations for statistics on memory-optimized tables
- describe the methods used to query memory-optimized tables
Native Stored Procedures
- describe the characteristics of natively compiled stored procedures
- recognize best-case usage scenarios for natively compiled stored procedures
- describe atomic blocks in natively compiled stored procedures
- demonstrate how to create a natively compiled stored procedure
- demonstrate how to monitor performance for natively compiled stored procedures
- describe the best practices for calling natively compiled stored procedures
Practice: Troubleshooting Deadlocks
- demonstrate how to troubleshoot deadlocks