Tag Archives: max degree parallelism

This SQLServer instance does not have the required “max degree of parallelism” setting of 1

Posted on by
Reply

I have got this error while trying to install SharePoint 2013

max degree of parallelism

What is Max Degree of Parallelism ?
When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, 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. SQL Server considers parallel execution plans for queries, index data definition language (DDL) operations, and static and keyset-driven cursor population.
Read this for more details
http://technet.microsoft.com/en-us/library/ms189094.aspx

How to fix it ?

  1. Open Microsoft SQL Server Management Studio
  2. Login with sysadmin user
  3. Right Click on instance name and select properties >> Advanced
  4. Change Max Degree of Parallelism to 1
  5. Restart the SQL Service

Parallel Query Processing

Posted on by
Reply
    • SQL Server provides parallel queries processing to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Because SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly and efficiently.
    • During query optimization, SQL Server looks for queries or index operations that might benefit from parallel execution.
    • For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution.
    • After exchange operators are inserted, the result is a parallel-query execution plan.
    • A parallel query processing execution plan can use more than one thread. A serial execution plan, used by a nonparallel query, uses only one thread for its execution. The actual number of threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism.
    • Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of threads that are being used. The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure.
    • You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.
    Advertisements

    The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

    • The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
    • A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
    • A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
    Advertisements

    Configure the max degree of parallelism option

    • In Object Explorer, right-click a server and select Properties.
    • Click the Advanced node.
    • In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.
    Advertisements