sqlpassion Performance Tuning Training Plan
individual study translation, if there is falsehood, please do not hesitate to point out, thank you.
Week 1:sql Server How to execute a query
Before we go into the intricacies of SQL Server performance tuning, I'd like to start by enumerating how SQL Server executes a query structure, which is important because understanding these concepts will be more profound for our future performance tuning courses. The following figure shows a few of the main components of SQL Server's execution of a query: SQL Server can be separated into two parts:
Relational EngineAnd
Storage Engine. which inThe largest component in relational engine is
Query Optimizer. Its only task is to generate a physical execution plan for the queries we pass to SQL Server (physical execution Plan)
reading Data First, the queries we submit to SQL Server are
Protocol layer (protocol level) passed to
command Parser (commands parser), which checks whether the syntax is legitimate, whether the referenced table, column, and so on are correct. The final result of Command parser is the so-called
Query Tree, which describes our query structure, which is used for subsequent query optimizer, resulting in an execution plan. The compiled execution plan is then handed to
Query Executorexecution, but before execution, the execution plan is first
Plan CacheCached for reuse (Plan cache is a very useful and dangerous concept, which we'll talk about later in Week10). After the execution plan is cached, Query executor starts interacting with the storage engine to perform each operation. When our queries require access to data (most of them are select),
Access MethodsWill first to
Buffer ManagerAsk if there is a specified data page that we want to read (in the next section we'll look at data pages: pages, now you just know that pages is a 8kb storage area), and Buffer Manager manages
Buffer Pool, which holds a lot of data pages, which is the main memory consumer in SQL Server, and we can set its size in configuration. If the requested page is already stored in the buffer pool, it can be returned immediately. This is called logical reading (Logical read). If the requested page does not exist in buffer pool, buffer Manager asynchronously initiates an IO operation that reads the requested page from the storage system into buffer pool, which is called a physical read (physical read). The entire query must wait for the physical read of the asynchronous operation to complete before we week22, and then discuss some of the details of the wait. Once all the request pages are read into buffer Pool, they are returned to access Methods, and after the execution of the plan, the processed data is returned to the application via the protocal layer. The entire query is complete.
Modifying DataWhen we run some insert,update,delete and other commands that will modify the data, Storage engine will use extra
Transaction Manager. Its role is to write a log to the transaction log to describe what changes have been made to our operations. This log record is quickly hardened and submitted. This means that our instance of SQL Server processes the transaction log as quickly as possible. The page is first rewritten in memory and then written to disk, the middle process we call
Check Point, it runs all the time and constantly requests dirty pages (dirty pages) to Buffer manager. Dirty pages have changed in memory but have not been written to a page on disk in a timely manner. Once a dirty page is written to disk, it is marked as a clean page.
Summaryas you can see, when we execute a simple query, many things happen inside SQL Server, different roles take on different functions, and in tuning we have to deal with a series of performance problems that each role can produce.
Translation SQL Passion Week 1:sql Server How to execute a query
