SQL Server 2008 lets you run certain kinds of complex queries
simultaneously on two or more processors. The queries must lend themselves to
being executed in sections.
Here’s an example:
SELECT AVG(charge_amt), category FROM charge GROUP BY
category
If the charge table has 1,000,000 rows and there are 10
different values for category, SQL Server can split the rows into groups and have only a
subset of the groups processed on each processor. For example, with a four-CPU
machine, categories 1 through 3 can be averaged on the first processor,
categories 4 through 6 can be averaged on the second processor, categories 7
and 8 can be averaged on the third, and categories 9 and 10 can be averaged on
the fourth. Each processor can come up with averages for only its groups, and
the separate averages are brought together for the fi nal result.
During
optimization, the Query Optimizer always fi nds the cheapest possible serial
plan before considering parallelism. If this serial plan costs less than the
configured value for the Cost Threshold For Parallelism option, no parallel plan
is generated. Cost Threshold For Parallelism refers to the cost of the query in
seconds; the default value is 5. If the cheapest
serial plan costs more than this configured threshold, a
parallel plan is produced based on assumptions about how many processors and
how much memory will actually be available at runtime. This parallel plan cost
is compared with the serial plan cost, and the cheaper one is chosen. The other
plan is discarded.
A parallel query
execution plan can use more than one thread; a serial execution plan, which is
used by a nonparallel query, uses only a single thread. The actual number of
threads used by a parallel query is determined at query plan execution
initialization and is the DOP. The decision is based on many factors, including
the Affinity Mask setting, the Max Degree Of Parallelism setting, and the
available threads when the query starts executing.
You can observe
when SQL Server is executing a query in parallel by querying the DMV sys.dm_os_tasks. A
query that is running on multiple CPUs has one row for each thread, as
follows:
SELECT task_address, task_state,
context_switches_count, pending_io_count, pending_io_byte_count,
pending_io_byte_average, scheduler_id, session_id, exec_context_id, request_id,
worker_address, host_address
FROM sys.dm_os_tasks ORDER BY session_id, request_id;
Be careful when
you use the Max Degree Of Parallelism and Cost Threshold For Parallelism
options—they have server-wide impact.
