Locking, Blocking, Versions: Concurrency for Maximum Performance

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.

SQL Server offers two methods for an application to 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 of the concurrency models works internally. We’ll examine the costs and trade-offs between the two concurrency models that SQL Server 2014 supports through different transaction isolation levels. Finally, we’ll examine the tools available for analyzing and troubleshooting blocking problems, including metadata views and Extended Events. You will learn:

• why concurrency management is a crucial part of database and application tuning
• how each of the transaction isolation levels impacts concurrency
• the details of how SQL Server implements pessimistic concurrency through locking, and when locking causes blocking
• how the snapshot-based isolation levels implement optimistic concurrency, and the problems to be aware of
• how In-Memory OLTP allows maximum concurrency with no locking
• how to use the metadata views and Extended Events to troubleshoot concurrency problems and monitor the resources used to manage concurrency.

Want to preview this Post Conference session before you purchase it? Click HERE to watch Kalen’s 24Hop Summit Preview Edition session!

Speaker:
Kalen Delaney