Query execution is driven by the relational engine in SQL Server. This means executing the plan that resulted from the optimization process. In this section, we will focus on the highlighted sections of the following diagram, which handle query execution:
Before execution starts, the relational engine needs to initialize the estimated amount of memory necessary to run the query, known as a memory grant. Along with the actual execution, the relational engine schedules the worker threads (also known as threads, or workers) for the processes to run on and provides inter-thread communication. The number of worker threads spawned depends on the following two key aspects:
- Whether the plan was eligible for parallelism as determined by the Query Optimizer.
- The actual available Degree of Parallelism (DOP) in the system, based on current load. This may differ...