0:0018:25

Life Cycle of a Query in SQL Server

Deepthi Goguri 18:25 2021

Your changed data sits in memory, not on disk, until a checkpoint runs

When a query changes data, SQL Server logs it, then leaves the modified pages in memory as dirty pages until a checkpoint, every minute, hardens them to disk. Deepthi Goguri, a SQL Server DBA and Microsoft Certified Trainer with seven years of experience, says knowing these internals is how DBAs and developers track down performance issues. What does each stage check before that, and why are some plans never cached?

Follow one query from its TDS packet to the disk, stage by stage

Watch on YouTube

In short

Deepthi Goguri, a SQL Server database administrator, traces a query's journey through SQL Server, from the secure network connection and TDS packet through the parser, binder, simplification, trivial plan and full optimization stages. She then explains how the storage engine handles read and write transactions, including shared and exclusive locks, the buffer pool and the checkpoint process that hardens dirty pages to disk, arguing that knowing these internals helps DBAs and developers troubleshoot performance with confidence.

“So knowing this information will really help you in understanding how queries are processed at each stage inside the SQL engine. It will also help you in tracking down the performance issues efficiently.”
“But did you observe something here? The pages are already written into the buffer pool, but they are not yet hardened to the database file system.”

What you will learn

  • Trace a query's path from the client connection (SNI, TDS packet and SPID) into the SQL engine.
  • Distinguish what the parser, binder, simplification and trivial plan components each check or change.
  • Explain why trivial plans are not cached and how statistics, join heuristics and search phases shape a full plan.
  • Compare how read and write transactions use locks, the transaction log and the buffer pool.
  • Describe how the checkpoint process hardens dirty pages from memory to disk.

Key takeaways from the speaker

  • Please provide

Details

Level
Beginner
Format
Tech talk
Technical depth
Practitioner
For
Early career, Mid-career
Length
18:25
Published
June 2021

Questions this talk answers

Why should DBAs and developers learn what happens inside SQL Server when a query runs?

Knowing how queries are processed at each stage helps track down performance issues efficiently and builds confidence in managing the engine. It also helps convince coworkers and managers to take your ideas seriously.

How does a query get from the client application into the SQL engine?

The SQL Server Network Interface (SNI) establishes a secure connection over protocols such as TCP/IP, named pipes or shared memory and wraps the query in a TDS packet. The SQL operating system receives it and assigns a SPID before passing it to the parser.

What is the difference between the parser and the binder?

The parser checks syntax and variables, expands views, hashes the query and looks for an existing plan in the plan cache. The binder checks that tables and columns are valid, that the user has permissions, and handles data type resolution and aggregate binding.

Why are trivial plans not stored in the plan cache?

Trivial plans are so cheap for SQL Server to build on the fly that it does not want to waste limited memory storing them. By default they are not cached.

How does a write transaction differ from a read transaction in SQL Server?

A write first records the change in the transaction log and takes an exclusive lock instead of a shared lock. The modified pages sit in the buffer pool as dirty pages until the checkpoint process hardens them to disk.

About this talk

Many DBAs and Developers work with SQL Server but don’t fully understand how it really works behind the scenes. Join Deepthi on her dive into the internals of SQL Server to explain why knowing what goes on under the covers will help you make better decisions quickly when solving performance problems in SQL Server. This session will cover ACID properties, key components of the Relational Engine, Storage engine, Cache and the Transaction manager. We will also cover what happens under the covers during read and write operations within SQL Server. Deepthi will show you how the SQL engine thinks – enabling you to think like it!

Topics

Related talks

Browse the library