For a SQL batch request the worker that picks up that task will execute the entire SQL batch (every statement).This should settle the often asked question whether statements in a SQL batch (= 1) and create sub-tasks, each sub-task goes through exactly the same cycle: it is created as PENDING and a worker must pick it up and execute it (a different worker from the SQL batch worker, that is by definition occupied! The lists and state of workers inside SQL Server can be seen by querying .
Other factors considered are the CPU consumption and the memory required for each plan alternative.
Using formulas tuned over many years of experience all these factors are synthesized into a single cost value for each alternative and then the alternative with the lowest cost is chosen as the query plan to be used.
Add to this that the JOIN can be done using various strategies (nested loop, hash, merge) and you’ll see why optimization is such an important concept in SQL.
SQL Server uses a cost based optimizer, meaning that it will consider all (or at least many) of the possible alternatives, try to make an educated guess about the cost of each alternative, and then choose the one with the lowest cost.
Certain individual statements inside the batch may execute with parallelism (often referred to as DOP, Degree Of Parallelism) and in their case the task will spawn new sub-tasks for executing in parallel.
If the request returns a result the batch is complete when the result is completely consumed by the client (eg. When a new request reaches the server and the task is created to handle that request, in PENDING state.
The request itself can take several forms: This request type contains just T-SQL text for a batch to be executed.
This type of requests do not have parameters, but obviously the T-SQL batch itself can contain local variables declarations.
I cannot close this side without urging you to read Dynamic Search Conditions in T-SQL if you want to learn more about this subject.