In SSWUG.ORG’s virtual class, you will learn about locking, blocking and row versions, which are some of the most important topics to understanding SQL Server’s internal behavior. By the end of the five sessions, you will know how these technologies are used to manage SQL Server concurrency – the ability of multiple users to access data at the same time.
Kalen Delaney, course instructor and celebrated author of SQL Server 2008 Internals, will start by discussing what consistency might mean to one’s applications, and describe the details of how each of the concurrency models works internally. She will examine the costs and trade-offs between the two concurrency models that SQL Server 2008 supports through different transaction isolation levels.
The target audience of this class is administrators, developers or IT professionals who have some experience with SQL Server Management Studio and TSQL queries, as well as an understanding of basic transactional work processing. Most importantly, students should want to learn more about the way SQL Server works internally and how to take advantage of the inner working to create the best performing system and applications possible.
Session 1 – Intro to Concurrency Models, SQL Server Transactions and Isolation Levels –
Kalen will discuss basic information regarding the two concurrency models – optimistic and pessimistic. She will also go over details regarding transactions in SQL Server, such as nesting transactions and preventable phenomena. Finally, she will delve into isolation levels, explaining the costs associated with true isolation and more.
Session 2 – Overview of Pessimistic Concurrency Control –
Kalen will discuss aspects of locking and blocking. She will also discuss ways to control locking and highlight some tools to use for troubleshooting.
Session 03 – Overriding SQL Server’s Default Locking Behavior –
Information about locking hints is provided by Kalen. She also explains what happens when a lock timeout occurs. Finally, she reviews available index options.
Session 04 – Overview of Optimistic Concurrency Control –
Users who use optimistic concurrency do not lock a row when reading it. Kalen will explain which problems optimistic concurrency is meant to address. She will also explore snapshot-based isolation levels and how SQL Server manages the data once these levels are requested.
Session 05 – Snapshot Isolation Details –
Kalen will explain how SQL Server keeps track of older data, as well as how to set up and enable snapshot isolation. She will also discuss issues around tempdb, which is where older data is stored.
Kalen Delaney has been working with SQL Server for over 27 years. She has provided performance consulting services and taught advanced courses on SQL Server to thousands of professionals and organizations. Delaney began her career as a teacher and specialist for Sybase, then in 1992, became an independent trainer and consultant. As such, she worked with both the Microsoft and Sybase companies to develop courses and to do internal training for their technical support staff. She became a Microsoft SQL Server MVP in 1993.
She is the author of SQL Server Internals: In-memory OLTP, primary author of SQL Server 2012 Internals, Inside SQL Server 2005 as well as previous books on SQL Server Internals. Visit her website to find out more.