Sql updating a query result set
These plans describe the way to open the tables and indexes, search and locate the rows of interest, and do any data manipulation as requested in the SQL batch.For instance a query plan will describe an access path like .
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.At this stage the server has no idea yet what the request actually is.The task has to start executing first, and for this the engine must assign a worker to it. A number of workers is created initially at server start up and more can be created on-demand up to the configured . Workers are waiting for PENDING tasks to become available (from requests coming into the server) and then each worker takes exactly one task and executes it.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.This is the type of request Sql Client sends if you invoke any of the managed class.
Bulk Load is different from the other requests because is the only request that starts execution before the request is complete on the TDS protocol.
For example if the request is a SQL Batch type request the task will represent the entire batch, not individual statements.
Individual statements inside the SQL Batch will not create new tasks.
The worker is busy (occupied) until the task finishes completely.
Tasks that are PENDING when there are no more available workers will have to wait until one of the executing (running) task completes and the worker that executed that task becomes available to execute another pending task.
this allows it to start executing and then start consuming the stream of data to insert..