Topic:
SQL Server 2025 introduces major enhancements to the locking engine, including Optimized Locking and the new Lock After Qualification strategy. These improvements fundamentally change how the engine acquires and holds locks, reducing contention and improving throughput under concurrent workloads.
In this deep dive, we will examine the internals of SQL Server’s locking behavior and how the 2025 improvements minimize unnecessary locks during query execution. We will analyze common contention scenarios—such as large updates, high‑concurrency workloads, and lock escalation—and demonstrate how Lock After Qualification defers lock acquisition until rows are fully qualified, thereby reducing blocking and deadlocks.
Through detailed demos using Dynamic Management Views (DMVs) and Extended Events (XEvents), you will learn how to inspect lock footprints, compare behaviors across access patterns, and evaluate strategies such as partitioning, index design, and transaction scoping. We will also discuss how these changes interact with features like RCSI, READPAST/skip locked, and explicit lock hints (UPDLOCK, HOLDLOCK, ROWLOCK) in the context of SQL Server 2025.
By the end of this session, you will have a clear understanding of SQL Server 2025’s optimized locking engine, know how to monitor and troubleshoot blocking scenarios, and be equipped to design queries and indexes that fully leverage these engine enhancements for maximum concurrency and stability.
Speaker:
Torsten (MVP, MCSE, MCT) has been deeply involved with Microsoft SQL Server
since the early 2000s, specializing in optimizing performance for extensive SQL
Server environments. He has dedicated over two decades to working with bp as a
platform engineer and collaborates closely with Sarpedon Quality Lab in his role as
Principal Microsoft SQL Server Consultant.
Torsten has delivered more than 300 presentations on SQL Server performance
optimization and Azure DevOps at various national and international conferences
and user groups. Since 2018, he has been recognized as a Microsoft Most Valuable
Professional for Data Platform. Moreover, for three consecutive years, Sessionize
has acknowledged him as one of the most active speakers.
Links:
https://www.meetup.com/dba-fundamentals-group/events/312863781