Know How SQL Server Executes a Query in Short

The present blog explains the entire process as to how MS SQL server, the client-server platform executes a query request, issued by user. Sending requests, which is the only means of communication with the server database can be achieved by the use of some specific in-built commands that the database in SQL server understands. The reason for acquiring the way SQL server executes a query is that it shall assist developers to write better coding for the database. Though understanding requires a complete know how and is a lengthy task, an attempt has been made to make users acquaint with the process in short by means of the image displayed below.

Summary of ‘Query Execution’ Procedure:  The complete procedure of query execution can be summarized as below:

                                                                                          

Step 1: At the very beginning a Request is sent, the Request in turn creates a new Task. In fact this step involves Creation of Task.

Step 2: If workers are not free to take up a Task for execution, a queue of Task gets formed and remains in pending state for execution.

Step 3: When any Worker in the threadpool becomes free after the execution of any Task, the idle Worker then picks up a pending Task. This step is all about an idle worker picking up a Pending Task.

Step 4: This is the Task Execution step of the query execution process. Here, firstly an execution plan is compiled. This in turn involves parsing, compilation and optimization. Thereafter, Query plan is executed where the operators access data through the buffer pool.

Step 5: Then, Result set is returned during execution.

Step 6: Finally, at the end the Task gets executed and complete. The involved Worker returns to idle state and the process gets over.

The process of SQL Server execution of queries can be understood easily by the below mentioned steps.

1.    Creation of ‘Request’

At the very beginning when a new request is completely sent over the Tabular Data Stream (TDS), a protocol that is used for communication between the sender’s application and the server database; task is created by server database engine for handling the placed request. Once the request gets formed it can take any one of the three below mentioned forms.

1.    Batch Request

2.    Remote Procedure Call Request

3.    Bulk Road Request

Note - The list of requests in the server can be queried from sys.dm_exec_requests.

2.    Creation of ‘Task’

The task that is created to handle the request represents the request right from the start to end. When a new request reaches the server and then the task that is created to handle the particular request remains in ‘pending’ state. The reason being, that at the current stage the server has no clue as to what exactly the request signifies. Thus, a pile of tasks gets formed and remains in queue. In such a case the task has to be executed first, for which the server database engine should assign a worker to it.

Note - The list of tasks in the server can be attained by sys.dm_os_tasks query. 

3.    Assigning ‘Worker’ a Task

Workers are one of the main components and actually the threadpool of SQL Server. Multiple of them are created in the beginning i.e. at the very start of the server. Although more of them can be formed on-demand up to a limit of configured maximum worker threads. All the workers remain in waiting state for the ‘pending’ tasks to become available, from requests coming into the server. Thereafter, each idle worker picks up exactly one task and executes it. In the mean time all the engaged worker remains unavailable until the execution of task gets over. Therefore, Tasks that remain in ‘pending’ state due to lack of available workers, shall have to stay pending until execution of any task gets complete and the worker that executed the particular task becomes available.

Note - The lists and state of workers inside SQL Server can be checked by querying sys.dm_os_workers.

4.    Parsing and Compilation

Thereafter, an execution plan is complied. In this step, before a Task starts executing a Request the first thing it needs to do is to gain a proper understanding about the content of the specific Request. The T-SQL text that exists inside the request gets parsed and an abstract syntax tree gets formed for representation of the particular Request. Actually, the conclusion is that all the existing Requests are parsed and thereby compiled in this step. However, in case any error occurs at this stride the Requests gets terminated with a compilation error.

5.    Optimization

Optimization is the next important phase in the complete life cycle of Task Execution, so as to select an optimum data path access. In case of SQL server, the best way of optimization is chosen by first observing the costs of each possible alternative. Thereby, the alternative with the lowest cost is chosen as the query plan to be utilized, to make the process economical. It is quite obvious that exploring all the possible alternatives consumes much time. Hence, once a query plan is created it is also cached, for use in future. So, similar Requests that might be requested in future can skip over the optimization phase; provided an already compiled and optimized query plan is found in the internal cache of SQL Server. 

6.    Execution & Result

Finally, after compilation the Requests gets executed, the Task gets completed and the Worker becomes available and free to pick up another Task in pending condition. Once a query plan is selected by the Optimizer, the ‘Request’ or say the ‘Query plan’ can be executed. Actually, this is the last important step as to how SQL server executes a query. Finally, the Result set is given back which is the end of the process.