Active seminars

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

  1. Table and index structures
  2. Optimizing queries for a transactional system
  3. Columnar storage and optimizing analytical queries
  4. Pessimistic isolation levels
  5. Optimistic isolation levels
  6. Memory-optimized tables and isolation levels
  7. 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.

Pricing

One-day seminar price Price
Early bird price (before 01.05.2023.) 189,90€
Regular price 220,00€
*All prices are in Euro (€)

Register

Registrations for the seminar are opened until 19.05.2023. The minimum attendees required for the seminar to be hold is 10. Because of limited seats and great interest please register on time if you like to attend this event.

If you are interested in attending the Data Saturday Croatia 2023 preconference seminar “Dejan Sarka - SQL Database Internals, Query Optimization, and Transactions” please reserve your seat and register here.
*Registration is just a seat reservation; you will get payment details for the event when minimum number of attendees is meet.