Locking and Blocking

2016

TL; DR

Locking and blocking is SQL Server’s default method of managing concurrency in a multi-user environment. In this session we’ll look at the three main aspects of locking, including when locks cause blocking and also examine ways to minimize blocking.

Session Details

Failing to design an application with concurrency in mind, and failure to test an application with the maximum number of expected simultaneous users is one of the main causes of poor application performance. Locking and blocking is SQL Server’s default method of managing concurrency in a multi-user environment. In this session we’ll look at the three main aspects of locking: type of lock, duration of lock and unit of locking. We’ll also look at when locks cause blocking and examine various ways to minimize blocking. In addition to looking at the aspects of locking, in this session, you will learn:

What metadata is available to show you:

The locks that have been acquired. The processes that are blocked and who is blocking them. The tables have had the most problems due to locking and blocking.

What other tools are available to track down other locking and blocking issues.

3 things you'll get out of this session

Kalen Delaney's previous sessions

Behind the Query Store…

Look inside Query Store to see what it does and how it works

Exploring Statistics: Where estimated rowcount comes from

A look inside SQL Server distribution statistics

What Happened? Exploring the Plan Cache

In this session, we'll explore SQL Server's plan cache, including techniques for discovering what plans are in cache, how often they've been run, and whether they contain any sub-optimal operators. Knowing what's happened is the first step in tuning.

Seeking SQL Server Secrets

In this session, I’ll tell you about some of my favorite undocumented features, and also tell you some of my tricks for discovering more undocumented secrets.