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.
