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
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!
Why every DBA should know SQL Server internals (55 s)
Why SQL Server doesn't cache trivial plans (71 s)
Dirty pages and the checkpoint process (63 s)
Hello, everyone. Thank you so much for taking your valid time for attending Women in Tech conference today. It's an honor. Anna, thank you so much for giving me this wonderful opportunity. So what are we going to discuss in this session today? We are going to talk about the lifecycle of a query in SQL Server. My name is Deepthi Goguri and every little bit about me. I'm a database administrator.
Having seven years of experience in managing SQL Server and databases. I'm a Microsoft certified trainer and Microsoft certified solution expert. I did my masters in computer technology at Eastern Illinois University here in Chicago. And I do blog for dbanuggets.com. It's a very active website. We talk all about SQL Server. And I'm on Twitter as well, very active on Twitter. Like if you have any questions later down the road, please do not hesitate to contact me by using Twitter.
@dbanuggets is my user ID and down below that's my email address. And I have not mentioned everything here, but I am a co-organizer for a couple of user groups. I'm a co-organizer for Microsoft data and AI South Florida user group. And also I'm a co-organizer for Microsoft data equity, diversity and diversity, equity and inclusion user group. And also I'm a volunteer for Microsoft data. Women in technology virtual group as well.
And I'm a co-organizer for Microsoft data and AI South Florida user group as well. And I'm one of the board member, committee member for the FAS Summit 2021. It's a professional association of SQL Server. We have that as a conference. So I'm one of the committee member in that as well. So what are we going to discuss in this session today? We are going to talk about the lifecycle of a query. And we are also going to see what are the components we have inside the SQL engine.
So why should we care to know about what happens behind the scenes, right? Many developers and TBAs, they work with SQL Server on a daily basis, but they might not exactly understand what happens behind the scenes when we submit any query to the SQL Server. 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.
And also build your confidence when managing database engine as well as well as the databases. It will also help you in convincing your coworkers or managers of why they have to take your ideas seriously. Because by the end of this session, you will know what goes on behind the scenes so that you can, you know, you can just let them know like, hey, that's not right. This is right. And let's go ahead and take this decision. And they have to listen to you because that's right. Right.
And then last but not least, we work with SQL Server every day, most of them like developers who work with SQL Server, the more you know, the better you can understand the engine and databases. So let's dive deep into the lifecycle of a query. I know this slide, you know, there are many components in it, you might get confused, but let me make it simple as possible.
So if the lifecycle of a query is divided into three different steps, then the first step contains the components parser, binder, simplification and the trivial plan. These components from the first step are very simple. They are very lightweight to the SQL Server. SQL Server can process them very quickly with less amount of resources. All they need is like, you know, the query text and that's all it needs, but not much.
But when you come to the second step, these components load, metadata, join heuristics and search phases. These components request most of the metadata to process any query. And when you come to the third step, the third step contains the components, access methods, transaction manager and buffer manager. Now these three components, they are the drive locations, right? These three components have to interact with this subsystem, which is like most, like most of the storages are like, sometimes they are slow.
So this is like most, you know, resource consuming. I can say that. Let's talk about a select query first and then let's talk about the insert update query later. So let's say there is a user here. You wanted to do a select statement onto the SQL Server who wanted to run. No matter if he's using the SQL Server Management Studio, Azure Data Studio, .NET Visual Studio or even a third party application like booking a ticket, right?
Booking a flight ticket. He wanted to book a ticket, but if he wanted to run that query onto the SQL Server, he should get a secure network connection. But how does that happen, right? There is something known as SNI, which is known as SQL Server Network Interface, meaning it will establish a secure network connection between the SQL operating system and the user.
The user is our client here, right? So how does SNI will establish the connection? By using the internet protocols like TCP/IP, named pipe, shared memory. Those are the internet protocols. No matter what kind of internet protocol we use, the process remains the same. So once the secure network connection has been established, this particular SNI, what it will do is it will take the query that we are submitting to the SQL Server and it will wrap that query into a tabular data stream packet, which is also known as a TDS packet.
And that TDS packet will be sent to the SQL operating system. Once that query is in the SQL operating system, the first thing that happens is the SQL operating system will identify that coming connection and it will assign a SPID to it. That means the SQL Server process ID, it will give an ID to the particular query.
And that particular query will be sent to the first step of the SQL engine, which is the parser. The parser, the name itself says it will parse the query, meaning it will go ahead and check tactical errors, right? It will go ahead and check if there are any syntactical errors. If for example, I wanted to run select star from a table, but by mistake, I have given select star form, then the parser component would first identify that as a syntactical error and throw back me an error saying it is syntactically incorrect.
And variables are declared here, like if you are declaring any variables, declare a variable, those variables are identified here, whether they are valid or not. So, if you want to see if you want to read your SQL query, the query text behind that view is brought in line into the code you are sending. Basically, the query, the view query will get expanded into the query that we are sending.
Those things will happen and the query that we are sending, it will be passed and then a hash is put on the query. A kind of hash number is put on that particular query and it will go ahead and check if there is already an existing plan for that particular query inside the plan cache. If there is the same match with the hash value, it will go ahead and use the same execution plan for the query.
But what if this particular query is running for the first time, right? Then it will go ahead and build the initial logical tree for the first time. And that particular query will be sent to the next component, which is binder. Binder is completely different from the parser. Binder will actually go ahead and check if the object inside of a query, they are valid or not.
Like if you are having tables, columns, whether they are valid or not. And binder is also known as algebraizer or normalizer. And user permissions are also checked. Like whether the user that is running the query, whether they have enough permissions or not. Those kinds of things are checked here. And data type resolution happens here. Like if you are using union all clause in between two tables, if you are running any query and running union all clause, the ordinal columns of those two queries should be of the same data type.
If it doesn't match in their data type, we will see the error. And aggregate binding also happens here. Like some average, those kinds of things are bound here. And with this information, the parser will update the parse tree again inside the plan cache. And the query will be sent to the simplification component. Simplification itself says it will simplify the query.
Now, how? How do we simplify the query? By converting. If you have any subqueries in your query, that subquery will be converted into a very simple join. So no matter how many subqueries you have in your query behind the scenes, those will just convert it into outer join, inner join, you know, depending upon the type of subqueries you have.
And contradiction detection happens here. Like if you have any foreign key relationship issues, simplification component will identify that here. And with this information, again, the parse tree inside the plan cache is updated and the query will be now sent to the trivial plan component. If your query is so simple, like select star from a table or select star from a table with a simple where clause, those kinds of queries are treated as very simple queries by the SQL server.
It will think, hmm, this is such an easy query. Why should I even go ahead and update the parse tree that these previous component has been built inside the plan cache? Why should I even do that? Let me go ahead and discard that parse tree inside the plan cache. And let me create a trivial plan on the fly just like that, because it doesn't cost it much.
It doesn't cost SQL server much. It can just go ahead and create the trivial plan. Like no matter how many times you send those type of very simple queries, like thousand times, for example, trivial plan component will build the. And so those queries, those many times, those are the trivial plans. And by default, these trivial plans are not stored inside the cache.
Now, why they do not get stored? Because it's so easy for SQL server to go ahead and create them instead of storing the valuable, you know, the valuable cache inside the memory. It doesn't want to waste that memory, right? Memory is limited inside SQL server. So now, but what if the query that we are running is not so simple?
It's so complicated. We have like 10 tables, complicated logic in it. Then SQL server should go ahead and, you know, build the build the full plan for that. So in that case, it will go to the load metadata component, the second step. Most of the metadata to process any query are gathered here. Like for example, index statistics and histograms.
The next coming session that I'm going to present will be especially on the statistics. So go look at that as well. So index statistics, these are basically roadmap. Like how, how many, how many columns does SQL server thing coming out of a table? It will just estimate based upon those estimate. It will go ahead and build the plan, you know, query plan for that particular query.
So those kinds of histograms and then index statistics are gathered for that particular query here. And auto create statistics happens here. Like if your auto create statistics is turned on, on your database, which is by default, it's turned on. In that particular scenario, it will go ahead and create the statistics on necessary columns automatically. Cardinality estimates are gathered here.
Basically these estimates are nothing but the statistics information. Those are gathered here and SQL server and windows collation are checked here. Set options are also checked like set XML on, set XML off, set ANSI padding on or off. Depending upon on or off, your plan might be dependent. Like what kind of plan gets generated might be dependent.
With this information, the parse tree inside the plan cache is once again updated. And the next component is join heuristics. So join heuristics, what it does is like it will intelligently think like, let me go ahead and reorder the join in such a way that like, for example, let's say select star between three tables. The table between A to B, let's say it is filtering out around 60% of the records from the result set.
And, you know, and the connection between B and C table is actually filtering out 90% of the records from the result set. In that case, the join heuristic component, it will go ahead and reorder the join in such a way that it will push back the join between B to C front of the statement and the join between A to B back of it.
Now, why do we do that? It's because it wanted to grab the results as quickly as possible. This kind of information is again updated inside the plan cache and the query will be sent to the search phases. Search phases are nothing but these are, you know, the query plans that get generated at different kind of phases. And SQL server have to go ahead and choose the good plan from these three phases and update that information inside the plan cache.
And, you know, send the query to the storage engine, which is the third step. So now our query is at the storage engine component. So let's talk about the read transaction like any query, whether it is a select, insert, no matter what query it is, the difference happens at the store. So inside the transaction manager, we have two components in it, the log manager and the log manager.
So since we are doing the read transaction, we do not write. We do not have to write anything to the log because we are not writing anything, right? We are just reading. We are doing select start from. So in that case, only the log manager will get invoked. You know, just log manager will get invoked and it will request the buffer manager to put a shared log on the resources that it needs to process the query.
Once the query goes to the buffer manager component, all it does is it will go ahead and log the resources. It will put a shared log on the resources it needs to process the query. And then it will go ahead and check whether all the, you know, all the resources that it needs to do this particular select, whether these resources are already inside the buffer pool.
If it finds those pages inside the buffer pool, well and good, it will go ahead and do the select from the memory of a pool itself. But if it didn't find those data pages, it will have to go ahead and grab those data pages by interacting the disk subsystem, by interacting the data files and grab those data pages that it requires to process the query.
Do the select, do the select, send the results to the user. Do the select, send the results to the user. That's the re-transaction. Very simple. But what happens with the right transaction, right? It will go ahead and check access methods. The same. It will just analyze the query, all of how it have to get the results from the disk subsystem.
But all the difference happens here with the right transaction. Previously, we just put the shared lock on the resources. But now as we are writing into the database, we have to first make those modifications that we are trying to make inside the transaction log manager. Like inside the transaction log, the previous and the next values are written here.
And then an exclusive log have to be placed on the resources. It will go ahead and request the buffer manager to put an exclusive log. It will put an exclusive log on the resources it needs. Go ahead and check if all the pages are already inside the buffer pool. If it didn't find some of them or any of them.
Like previously, for the re-transaction, it have to go ahead and grab those pages which it requires to process the query. Put them in the memory. Do the modification. Do the write statements into those data pages. And then send the success message to the user. 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.
That's when the checkpoint process will kick in. This particular checkpoint process, all it does is it will go ahead and check if there are any dirty pages inside the buffer pool. Now what are these dirty pages? Dirty pages are the data pages which are already written inside the memory, but not yet hardened to the disk. So checkpoint process is an automated process done by the SQL server.
Every minute it will go ahead and push back these dirty pages which we have just written and go ahead and harden them onto the database file system. So that's how the write transaction works. That's how the write transaction works. In the summary, we have discussed mainly about the life cycle of the... I did not update this particular summary slide, but then as this is a 20 minute session, we couldn't cover all of these.
But what we have seen is the life cycle of the query, what will happen for the read as well as the write transaction inside the SQL engine. So I followed these particular resources to, you know, prepare for my presentation. And thank you so much for attending my session today.
Transcribed automatically; names checked against the speaker record. Select a time to jump there.
Article based on this talk
What Happens Inside SQL Server When You Run a Query?
Deepthi Goguri follows a query from the network connection to the disk, showing how SQL Server parses, optimizes, reads and writes data.
In short
Every query sent to SQL Server travels the same path: lightweight checks on the query text, a metadata-heavy optimization stage when the query is complex, and a storage engine stage that works with memory and disk. Deepthi Goguri, a SQL Database Administrator, walks through each component on that path and argues that knowing what happens behind the scenes helps DBAs and developers track down performance problems efficiently and make their case with confidence.
Key takeaways
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.
Why look behind the scenes
Goguri speaks to developers and DBAs who work with SQL Server every day but may not know what happens after a query is submitted. Her case for learning the internals is practical: "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." (1:56)
She adds that this knowledge builds confidence in managing the engine and its databases, and gives engineers the grounding to explain to coworkers and managers why a decision is right.
Three stages and the road into the engine
Goguri divides the life cycle of a query into three steps. The first contains the parser, binder, simplification and trivial plan components. "These components from the first step are very simple. They are very lightweight to the SQL Server." (3:18) The second step (load metadata, join heuristics and search phases) requests most of the metadata needed to process a query. The third (access methods, the transaction manager and the buffer manager) has to interact with the disk subsystem, which she calls the most resource consuming part.
First, though, the query has to reach the server. Whether the user works in SQL Server Management Studio, Azure Data Studio, Visual Studio or a third-party application such as a flight booking site, the SQL Server Network Interface (SNI) establishes a secure connection using TCP/IP, named pipes or shared memory. "No matter what kind of internet protocol we use, the process remains the same." (5:12) SNI wraps the query in a tabular data stream (TDS) packet and sends it to the SQL operating system, which assigns the connection a SQL Server process ID, or SPID.
From parser to trivial plan
The parser checks for syntax errors (her example is typing "form" instead of "from"), validates declared variables and expands views into the submitted code. It then hashes the query and checks the plan cache for a matching plan. If one exists, SQL Server reuses it; if the query is new, the parser builds an initial logical tree.
The binder, also called the algebrizer or normalizer, checks that tables and columns are valid, that the user has permission, that data types resolve (for instance across a UNION ALL) and that aggregates are bound. Simplification then rewrites the query: "So no matter how many subqueries you have in your query behind the scenes, those will just convert it into outer join, inner join, you know, depending upon the type of subqueries you have." (8:56)
For very simple queries, such as a select from one table with a simple WHERE clause, SQL Server discards the parse tree and builds a trivial plan on the fly. "And by default, these trivial plans are not stored inside the cache." (10:12) The reason is memory: "It doesn't want to waste that memory, right? Memory is limited inside SQL server." (10:37)
When a query needs a full plan
Complex queries with many tables move to the second step. Load metadata gathers index statistics and histograms, which Goguri describes as a roadmap SQL Server uses to estimate how many rows come out of a table. If auto create statistics is on (the default, she notes), statistics are created on the necessary columns automatically. Cardinality estimates, collation and SET options such as ANSI padding are checked too, and these settings can change which plan is generated.
Join heuristics then reorder joins. In her example, if the join between tables B and C filters out 90% of the records while the join between A and B filters out 60%, the B to C join moves to the front. "Now, why do we do that? It's because it wanted to grab the results as quickly as possible." (13:27) Finally, the search phases generate plans at different phases, SQL Server chooses a good one, updates the plan cache and hands the query to the storage engine.
Reads, writes and the checkpoint
In the storage engine, query types diverge. A read writes nothing to the transaction log, so only locking is involved: the buffer manager places a shared lock on the resources the query needs. If the pages are already in the buffer pool, the select runs from memory; otherwise SQL Server reads them from the data files, then returns the results.
A write first records the change in the transaction log, with previous and new values, and takes an exclusive lock instead of a shared one. Pages are brought into memory if needed, modified, and a success message goes to the user. Goguri then highlights a detail many miss: "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." (16:42)
That is the job of the checkpoint process. "Dirty pages are the data pages which are already written inside the memory, but not yet hardened to the disk. So checkpoint process is an automated process done by the SQL server." (17:10) As she describes it, the process runs every minute and hardens those dirty pages to the database file system.
Watch the talk
"Life Cycle of a Query in SQL Server" by Deepthi Goguri, about 18 minutes: https://www.womentech.net/video/life-cycle-query-in-sql-server
About the speaker
Deepthi Goguri is a SQL Database Administrator at Intone networks Inc. with seven years of experience administering SQL Servers. She is a Microsoft Certified Trainer with Associate and Expert level Microsoft certifications in Data Management and Analytics, and she blogs at DBANuggets.com.
Questions this talk answers
Why should DBAs and developers learn what happens inside SQL Server when a query runs?
How does a query get from the client application into the SQL engine?
What is the difference between the parser and the binder?
Why are trivial plans not stored in the plan cache?
How does a write transaction differ from a read transaction in SQL Server?
Drafted with AI from the talk transcript. Editor review pending.
Resources mentioned
dbanuggets.com (speaker's SQL Server blog)
@dbanuggets on Twitter
Cite this talk
Deepthi Goguri. "Life Cycle of a Query in SQL Server." WomenTech Network, 2021. https://www.womentech.net/video/life-cycle-query-in-sql-server