// Week 1: How SQL Server Executes a Query

Hello, and welcome to the first issue of the SQL Server Performance Tuning series. Before we go into the nasty details of performance tuning in SQL Server, I want to lay out today the foundation about how SQL Server executes a query. This is a very important part, because in the upcoming issues of the series we will further enhance our knowledge based these concepts.

The following pictures gives you an overview about the most important components within SQL Server, that are used when we are executing a query.

As you can see, SQL Server is internally splitted up into the Relational Engine and the Storage Engine. The biggest component within the relational engine is the Query Optimizer. The one and only task of the query optimizer is to generate an physical execution plan for a query that we are passing in into SQL Server.

Reading Data

The query - that we are submitting to SQL Server - goes through the Protocol Layer to the Command Parser. The command parser just checks if we are providing a valid TSQL statement, if we are referencing tables and columns that are existing in our database. The result of the command parser is a so-called Query Tree, a tree structure that represents our query. The tree structure is used by the query optimizer to generate an execution plan. The compiled execution plan is afterwards handed over to the Query Executor. The task of the query executor is to execute the execution plan. But in the first step the compiled plan is cached in the Plan Cache for further reuse. Plan Caching is a powerful and at the same time also a very dangerous concept in SQL Server. We will see that in more detail in week 10 when we talk about plan caching in SQL Server.

After our execution plan is cached, the query executor communicates with the storage engine, and is executing every operator in our execution plan. When we are accessing data in our execution plan (we are always doing that!), the Access Methods are asking the Buffer Manager for specific pages that we want to read. Next week we are talking a little bit more in detail about pages in SQL Server. By now you just have to know that a page is a buffer of 8kb, where our table and index data is stored. The buffer manager manages the Buffer Pool, where our pages of 8kb are stored. The buffer pool itself is the main memory consumer of SQL Server and its size can be configured through the Min/Max Server Memory Setting.

When a requested page is already stored in the buffer pool, the page is immediately returned. This is a so-called Logical Read in SQL Server. If the page is not stored in the buffer pool, the buffer manager issues an asynchronous I/O operation to read the requested page pyhsically from our storage subsystem into the buffer pool. This is a so-called Physical Read. During the asynchronous I/O our query has to wait until the operation is completed. We will talk more about Waits and Wait Statistics in week 22.

As soon as the page is read into the buffer pool, the page is returned back to the access method that requested it. When the execution plan is finished, the produced data is returned through the protocol layer back to the application that submitted the query.

Changing Data

When we are dealing with TSQL statements, that are changing data (INSERT, UPDATE, DELETE, MERGE), the storage engine also interacts with the Transaction Manager. The transaction manager is writing transaction log records into the transaction log that are describing the changes we have performed for the transaction. As soon as these records are written out, the transaction can commit. This also means that your SQL Server instance can be only as fast as your transaction log.

Pages that were changed in memory are written to the storage subsystem through the so-called CHECKPOINT process. By default the CHECKPOINT process runs about every minute, and requests all dirty pages from the buffer manager. A dirty page is page that was changed in memory, but hasn’t yet written to the storage. As soon as a dirty page is written out to the storage, the page is marked as a clean page. When we are dealing with TSQL statements, that are changing data (INSERT, UPDATE, DELETE, MERGE), the storage engine also interacts with the Transaction Manager. The transaction manager is writing transaction log records into the transaction log that are describing the changes we have performed for the transaction. As soon as these records are written out, the transaction can commit. This also means that your SQL Server instance can be only as fast as your transaction log.

Pages that were changed in memory are written to the storage subsystem through the so-called CHECKPOINT process. By default the CHECKPOINT process runs about every minute, and requests all dirty pages from the buffer manager. A dirty page is page that was changed in memory, but hasn’t yet written to the storage. As soon as a dirty page is written out to the storage, the page is marked as a clean page.

Summary

As you can see from this first issue of the SQL Server Performance Tuning series, a lot of different things are happening within SQL Server, when you are executing a query. If you want to have a more detailed look on how the various components within SQL Server are interacting with each other, I’m also highly recommending reading the blog posting http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/ from Remus Rusanu, who is a developer on the SQL Server team.

And the nasty thing is that you can have in every subsystem serious performance problems, that have to be resolved by you. See you next week, where we are talking about Data Pages in SQL Server!