1th Week how SQL Server executes a query

Original: 1th week how SQL Server executes a query

Hello everyone, Welcome to the 1th week of SQL Server Performance Tuning training . Before we go into the tedious details of SQL Server performance tuning, I want to build the foundation by explaining how SQL Server executes a query. This part is very important, because we will use these concepts to deepen our understanding of SQL Server in the next training.

When we execute a query, what are the most important components in SQL Server, the picture below gives you an overview.

As you can see, SQL Server is internally divided into 2 parts: the relational engine and the storage engine . one of the largest components in the relational engine is the query optimizer . The only and only task for the query optimizer is to generate the actual execution plan for our incoming query statements.

reading Data

We submit a query to SQL Server and pass the protocol layer to the command parser . The command parser checks to see if we provide a syntactically correct statement, and the tables and columns we refer to exist in the database. The command parser generates a query tree that can reproduce our query. The query tree is used by the query optimizer to generate execution plans.

The compiled execution plan is then submitted to the query executor . The task of the query executor is to execute the execution plan. But for later reuse, first it will put the compiled execution plan in the execution plan cache area to cache. Planning for caching in SQL Server is a powerful and dangerous concept. We will learn more details when we discuss the plan cache in the 10th week.

After the execution plan is cached, the query executor interacts with the storage engine and performs each operation in the execution plan. When we access the data in the execution plan, the access method reads to the Buffer Manager The page we want to specify. We'll discuss more details about the pages in SQL Server next week. Now you just have to know that the page is a 8Kb cache, where our tables and index data are stored. The Buffer Manager manages the buffer pool (the buffer pool contains the execution plan buffers and data buffers), where our 8KB size pages are stored. The buffer pool is the primary memory consumption of SQL Server, and it can be configured by the minimum and maximum server memory (MB) of the server properties, memory, and so on.

When the requested page is already in the buffer pool, the page is read immediately. This is called logical reading in SQL Server. If the requested page does not have a buffer pool, the buffer Manager initiates an asynchronous I/O operation that reads the requested page from our storage subsystem to the buffer pool, which is called a physical read . During asynchronous I/O operations, our queries need to wait until the end of the operation to continue. In the 22nd week we will discuss the waiting and waiting statistics for more details.

once the page is read to the buffer pool, the page is taken away by the requested access method. When execution is completed, the resulting data is returned through the protocol layer to the program that submitted the query.   

Modifying Data

The storage engine also interacts with the transaction manager When we are dealing with a TSQL statement (Insert,update,delete,merge) that modifies the data. The transaction manager writes the changes that we made in the execution transaction to the transaction file through the transaction log. When these log records are written, the transaction is ready for submission. How fast the transaction executes and how quickly your data is modified.

Pages that are modified in memory are written back to the storage subsystem by the process called the checkpoint . By default, checkpoints request all dirty pages from the buffer manager every minute and execute once. Dirty pages are modified in memory, but are not yet written back to the store. Once the Dirty page is written back to the store, the page is marked as a clean page.

Summary

From the above description we can see that when you execute an SQL statement query, there are a lot of different things happening in SQL Server. If you want to learn more about the interoperability of the various components in SQL Server, I highly recommend looking at the SQL Server team member Remus Rusanu this post http://rusanu.com/2013/08/01/ understanding-how-sql-server-executes-a-query/. (Chinese version: Take you to understand how SQL Server executes a query)

The various components mentioned above will trigger the SQL Server performance issue if there is a problem, and these problems will end up being solved by you. Next week when we see it, we'll discuss the pages in SQL Server together.

1th Week how SQL Server executes a query