Faster Queries Without NOLOCK!

2019

TL; DR

Look at the ways SQL Server manages multiple users, and avoid blocking!

Session Details

SQL Server offers two methods for applications to provide multi-user concurrency and provide data consistency: Pessimistic and Optimistic Concurrency Control. In this seminar we’ll discuss what consistency might mean to your applications, and describe the details of how each concurrency model works internally. We’ll examine the costs and trade-offs between the two models. Finally, we’ll examine the metadata and tools available for analyzing and troubleshooting problems.

Why is concurrency management a crucial part of database tuning?
How does SQL Server implement pessimistic concurrency with locking?
How do we get true optimistic concurrency?

Pre-requisites
Basic knowledge of TSQL Querying
Laptop Required: Optional

Software: Delegates can follow along with some of the demos if they have a laptop with SQL Server 16 or later installed, with the latest Management Studio,.
Subscriptions: They can connect to an Azure SQL Server or SQL DB if they do not have a local one, but it is not required.

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.