RSS Feeds

Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

SQL Azure

Developer

General DBA

ASP.NET / ADO.NET

SQL Azure

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

Max Degree of Parallelism

By : Arshad Ali
Dec 27, 2008

When SQL Server runs on a computer with more than one microprocessor/CPU or on SMP (a computer-architecture where two or more identical processors can connect to a single shared main memory and I/O and can perform the same functions. In case of multi-core processors, the SMP architecture applies to the cores, treating them as separate processors), it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. In other words, the max degree of parallelism setting governs the maximum number of processors that a particular query statement or index operations (index data definition language) can utilize at run time.

The default value of 0 uses all available processors. Set max degree of parallelism to 1 to suppress parallel plan generation (serial plan only). Set the value to a number greater than 1 (up to a maximum of 64) to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.

Benefits of parallel execution plan
Complex/Long running queries – During query optimization, SQL Server looks for queries that might benefit from parallel execution. It distinguishes between queries that benefit from parallelism and those that do not benefit, by comparing the cost of an execution plan using a single processor versus the cost of an execution plan using more than one processor and uses the cost threshold for parallelism (By default 5, this value of cost threshold for parallelism determines which queries are considered short, and they should therefore be run using serial plans) value as a boundary point to determine short or long query. In a parallel query execution plan, the INSERT, UPDATE, and DELETE operators are executed serially. However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. The actual data changes are then serially applied to the database.

Index data definition language (DDL) – Index operations that create or rebuild an index (REBUILD only, not applicable to REORGANIZE), or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates.

Other Operations – Apart from the above, this option also controls the parallelism of DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP.

Next Page>>