Managing Database Concurrency


Overview/Description
Target Audience
Prerequisites
Expected Duration
Lesson Objectives
Course Number
Expertise Level


Overview/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.

Target Audience
Database professionals responsible for implementing and maintaining SQL Server 2016

Prerequisites
None

Expected Duration (hours)
1.8

Lesson Objectives

Managing Database Concurrency

  • 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
  • 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
  • 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
  • demonstrate how to troubleshoot deadlocks

    • Course Number:
    df_sqld_a07_it_enus

    Expertise Level
    Expert