Dejan Sarka - SQL Database Internals, Query Optimization, and Transactions
Even the best logical model can’t help when the physical implementation is bad. Therefore, it is important to know how SQL Server, Azure SQL Database, and partially also Azure Synapse Analytics, store data in row and columnar format and how the engines access this data. You will learn about heaps, balanced trees, and columnar indexes. In addition, this course introduces query optimization for both, transactional and analytical queries.
Course will take place on June 2th 2023 at Hotel International, Miramarska 24, 10000 Zagreb, Croatia.
Session abstract
Summary
Even the best logical model can’t help when the physical implementation is bad. Therefore, it is important to know how SQL Server, Azure SQL Database, and partially also Azure Synapse Analytics, store data in row and columnar format and how the engines access this data. You will learn about heaps, balanced trees, and columnar indexes. In addition, this course introduces query optimization for both, transactional and analytical queries.
In order to achieve a good compromise between data integrity and performance suitable for the business needs, it is essential to select the appropriate transaction isolation level. A developer needs to thoroughly understand the possible problems by selecting an inappropriate isolation level. Fortunately, SQL Server offers all possible isolation levels with both, optimistic and pessimistic locking. Memory-optimized tables do not use locking, but introduce their own set of possible issues. With locking hints, it is possible to control the locking on a lower granularity than with the session-level options. The seminar explains the possible problems and shows how the problems are solved with different transaction isolation levels.
Basic familiarity with SQL Server is recommended for this course. If you are a complete beginner in this area, then we recommend that you watch the Starting with SQL Server and Azure SQL Database course first.
Abstract
You will learn about physical structures in a SQL Server database, including pages, extents, heaps, and balanced trees. By knowing the internals, you will be able to use the appropriate structure and design optimal indices. The seminar will guide you how to write efficient queries and support them with indices for a transactional system. Optimizing storage and queries for analytical applications is many times quite different than for transactional applications. In this module, you will learn about the columnar storage inside SQL Server and how to write efficient analytical queries.
In the pessimistic concurrency approach, locks are used to prevent users from modifying data in a way that affects other users. Once a lock has been applied, other users cannot perform actions that would conflict with the lock until the owner releases it. Locks are used for reading and writing. Only one copy of data elements at any time. Update conflicts can’t occur.
In the optimistic concurrency approach, multiple copies (with multiple versions) of data elements are created. The copies are from the state before updates for on-disk tables and show the new data for in-memory tables. Locks might be used for writing only for on-disk tables. Readers read stored old versions of data elements. Update conflicts can occur. Memory-optimized tables always use the optimistic approach. There is no locking at all. The module starts by introducing the memory-optimized tables.
The worst issue with the locks is deadlocks. The module explains how a deadlock can occur, and shows how to solve it. Then the module introduces table-level locking hints.
Modules
- Table and index structures
- Optimizing queries for a transactional system
- Columnar storage and optimizing analytical queries
- Pessimistic isolation levels
- Optimistic isolation levels
- Memory-optimized tables and isolation levels
- Deadlocks and locking hints
Dejan Sarka
Dejan Sarka, MCT and SQL Server MVP, is an independent trainer and consultant that focuses on development of database and business intelligence applications.
Besides projects, he spends about half of the time on training and mentoring.
He is the founder of the Slovenian SQL Server and .NET Users Group.
Dejan Sarka is the main author or co-author of nineteen books about databases and SQL Server.
Dejan Sarka has also developed many courses and seminars for Radacad, Microsoft, SolidQ, and Pluralsight.