See Also: Main_Page - Performance Tuning - Query Processing

If the server computer on which SQL Server is running has multiple CPU's SQL Server can run a single query in parallel using multiple threads. In addition to running user queries on multiple processors SQL Server can also use multiple threads to build indexes. When examining textual or graphical execution plans you will notice exchange operators distribute streams, repartition streams and gather streams if the query is using more than one processor. Typically queries that make heavy use of CPU cycles are good candidates for parallel execution. For example a query joining several large tables and sorting the output before returning it to the user is likely to benefit from a parallel execution plan.

When the query is submitted for execution the query optimizer decides whether appropriate number of system resources is available for parallel query execution. If so, it attempts to determine the optimal number of threads on which to execute the query.

Whether the query will be run in parallel or serially depends on multiple factors:

Affinity Mask option

This value is set using sp_configure system procedure at the server level and determines the processors that are available for SQL Server's use. By default, SQL Server is configured to use all processors on the server computer. If you override the default value and enable only a single processor for SQL Server, then queries will always execute serially.

Max Degree of Parallelism setting

This value is set using sp_configure system procedure at the server level. The default value is 0 which means there is no upper limit and SQL Server can use all available processors. Unless you have a compelling reason to do otherwise, you should leave this setting at its default value. If you set Max Degree of Parallelism to 1 then all queries will execute serially. This setting is ignored on servers with a single processor. Occasionally you might find that parallelism actually hinders performance of some queries. In this case the cost of initializing and synchronizing parallel plans might exceed the benefit of running portions of the query on multiple threads. If you feel that serial execution of a particular query can provide better performance you can override this setting using MAXDOP option within an individual query. For example, the following query contains the MAXDOP hint to limit query optimizer to using a single processor:

SELECT BusinessType, AnnualRevenue, ResellerName FROM dimReseller OPTION (MAXDOP 1)

Note that you can also specify the MAXDOP option when creating or rebuilding indexes. Note also that parallel index operations are only available with SQL Server 2005 Enterprise Edition.

Cost Threshold for Parallelism setting

This value is also set using sp_configure at the server level and determines the minimum cost of a query to be considered for parallel execution. The cost in this case reflects the number of seconds it would take to run the query serially. If the query is trivial and cannot benefit from multiple processors, SQL Server will always use serial execution.

Max Worker Threads setting

Also set using sp_configure at the server level. This setting determines the total number of native Windows OS worker threads available to SQL Server. The default value is 255 and works well for most environments. If the server is busy it might not be possible to execute a query on multiple threads because not enough worker threads are available. In such case the server first attempts to reduce the number of threads on which the query is to be executed while still using the parallel plan. If the server is severely low on resources the parallel plans are abandoned completely and queries are executed serially.

The contents of the query

If the query contains a scalar operator or relational operators that cannot run in parallel, then it won't be considered for parallel execution. Furthermore, if the number of rows to be operated on is relatively low, query optimizer doesn't consider parallel execution plans.

The amount of memory available on the server

Although splitting the load from a single thread to multiple threads can provide some efficiency gains, it also means executing multiple queries concurrently, each of which will use additional resources. Query optimizer examines the amount of memory (and other resources) available on server before considering parallel execution plans.

Query Governor Cost Limit setting

Set using sp_configure at the server level. This option specifies the upper limit in number of seconds in which every query must complete. If query optimizer estimates that serial execution of the query will exceed this limit it will attempt using the parallel execution plan (and vice versa).

The availability of distribution statistics

In older versions of SQL Server the query optimizer abandoned the parallel execution plans if the highest degree of parallelism could not be achieved due to missing statistics. With SQL Server 2005 the degree of parallelism for a query plan is reduced prior to being abandoned.

Insert, Update, Delete Operations

Even with the parallel execution plan, typical INSERT, UPDATE and DELETE operations are executed serially. However, if you have SELECT statements with sub-queries or INSERT statements with the SELECT list these can be split up across multiple threads. Similarly the WHERE clauses of DELETE or UPDATE statements can be evaluated on multiple threads before the actual data changes take place serially.

Index Rebuilds

Indexes can be created or rebuilt using multiple threads when using SQL Server 2005 Enterprise Edition. During index rebuild the main thread (referred to as the coordinating thread) randomly scans the table to estimate the distribution of index keys. The coordinating thread initially tries to come up with the number of key ranges equal to the maximum degree of parallelism. If it cannot find enough key ranges, it reduces the number of threads it intends to use (thereby reducing the degree of parallelism for this operation). Then it assigns a subset of all index keys to each thread. Each subordinate thread builds an index structure for the set of keys defined by the filter that the coordinating thread provided. Once all parallel threads complete the coordinating thread combines all index units into a single index.

Parallel index operations may apply to the following statements:

• CREATE INDEX
• ALTER INDEX...REBUILD
• DROP INDEX (for clustered indexes only)
• ALTER TABLE...ADD CONSTRAINT
• ALTER TABLE...DROP CONSTRAINT (for clustered indexes only)

The ALTER INDEX...REORGANIZE statement is equivalent to DBCC INDEXDEFRAG and is a single-threaded operation.