The Lifecycles of Queries: How SQL Server Really Executes Your Code

SQL Server performance problems are often diagnosed by looking at one part of the system in isolation. Execution plans, waits, and metrics all provide useful signals, but rarely explain how a problem started or why it persists.

In this full day training session, we will follow multiple queries through their lifecycles, starting with the client request and security context, moving through parsing and optimisation, crossing into the storage engine, and continuing through execution and resource usage.

We will examine how SQL Server builds and transforms query trees, including parsing, binding, constant folding, logical and physical operators, statistics, and cardinality estimation. From there, we will follow execution into the storage engine, covering access methods, locking and the transaction manager, write-ahead logging, and how data is read from and written to memory and disk through the buffer pool and extended buffer pool.

The session continues beyond query execution to look at what happens over time. We will cover TempDB usage, memory pressure, checkpoints, page eviction, and how SQL Server monitors and reacts to workload behaviour using features such as Query Store, automatic tuning, missing index feedback, and trace flags. We will also look at instance-level controls such as security and Resource Governor, and how they influence workload behaviour in consolidated environments.

Throughout the day, theory will be demonstrated wherever possible, with practical examples used to connect internal behaviour to real world performance, monitoring, and diagnostic scenarios. The goal is to give attendees a clear mental model of how SQL Server executes work end to end, so performance problems can be understood and solved rather than guessed at.

Follow queries through SQL Server from client submission to physical execution and storage. Diagnose performance issues by linking execution plans to real runtime and instance-level behaviour. Make informed intervention decisions in consolidated SQL Server environments using built-in monitoring and control features.