Area of ​​special attention – Maximum Degree of Parallelism option. About interesting things from the world of IT, instructions and reviews Setting the maximum degree of parallelism parameter

It is no secret that when considering the problems of configuring a SQL server related to increasing productivity, most IT specialists opt for increasing hardware. But is this always justified? Have all server configuration methods already been used? It is known that working with configuration parameters and changing their default values ​​can improve the performance and other characteristics of a given system. Among these SQL configuration options, there is one option that has many questions associated with it, this option is Max degree of parallelism (DOP) - so we’ll talk about it.

The Maximum Degree of Parallelism (DOP) option determines the number of threads onto which SQL Server can parallelize a query and indicates the number of server processors used. This parameter has a default value of 0 – the maximum degree of parallelism. For example, if you have 24 cores, then the value of ‘max degree of parallelism’ will be equal to 24 and the optimizer, if it deems it necessary, can use all processors to execute one instruction, that is, the request will be parallelized into 24 threads. This is good for most cases, but not for everyone. Also, it is not always good to use the default value of this parameter. Configuring this parameter may be necessary, for example, in the following situation: let's say we have an application into which all employees enter information about daily transactions, and, at a certain period of time, each of the users runs a query that builds a report on all the user's transactions for a certain period of time. Naturally, if the time period is long, this request will take a long time to complete and, with DOP installed by default, will occupy all available processors, which will naturally affect the work of other users. Therefore, by changing the DOP value, we can increase the SQL server response time for other users without changing the query itself.
MS recommends setting the value as follows:

Setting the parameter to TSQL entirely for the server:

EXEC sp_configure "max degree of parallelism", 4; reconfigure

You can also set this value for a specific TSQL query:

USE AdventureWorks2008R2 ; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS TotalFROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (MAXDOP 2); GO

In this example, the maxdop hint changes the default value of the max degree of parallelism parameter to 2. You can view the current setting like this:

EXEC sp_configure "Show Advanced",1; RECONFIGURE; EXEC sp_configure "max degree of parallelism"

Now let's see how this value affects the speed of query execution. In order for the test request written above to be executed more long time, add another select to it. The request will take the following form:

< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty

On my test machine the ‘max degree of parallelism’ value is set to 0. MSSQL is running on the machine with 4 nuclear processor. I conducted a series of experiments with different MAXDOP values: equal to 1 – without query parallelization; equal to 2 - using only 2 cores; equal to 4 – using all and no hint to determine the option that uses the default sequel. In order to get execution statistics, you need to include the SET STATISTICS TIME ON option in the query, and also enable the display query plan button in Management studio. To average the results, I ran each query in a loop 3 times. The results can be seen below:

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 1); SQL Server Execution Times: CPU time = 45942 ms, elapsed time = 46118 ms. SQL Server Execution Times: CPU time = 45926 ms, elapsed time = 46006 ms. SQL Server Execution Times: CPU time = 45506 ms, elapsed time = 45653 ms.

The query plan shows that when the hint was installed (MAXDOP 1), the query was executed without parallelization. Average query execution time 45925.66 ms

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 2); SQL Server Execution Times: CPU time = 51684 ms, elapsed time = 28983 ms. SQL Server Execution Times: CPU time = 51060 ms, elapsed time = 26165 ms. SQL Server Execution Times: CPU time = 50903 ms, elapsed time = 26015 ms.

When installing the hint (MAXDOP 2), the request was executed in parallel on 2 cpu, this can be seen in the Number of Execution in the query execution plan. Average query execution time 27054.33 ms

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 4); SQL Server Execution Times: CPU time = 82275 ms, elapsed time = 23133 ms. SQL Server Execution Times: CPU time = 83788 ms, elapsed time = 23846 ms. SQL Server Execution Times: CPU time = 53571 ms, elapsed time = 27227 ms.

When installing the hint (MAXDOP 4), the request was executed in parallel on 4 cpu. Average query execution time 24735.33 ms

SELECT dt.ProductID, dt.OrderQty, SUM(dt.LineTotal) AS Total FROM Sales.SalesOrderDetail dt, (SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty SQL Server Execution Times: CPU time = 85816 ms, elapsed time = 23190 ms. SQL Server Execution Times: CPU time = 85800 ms, elapsed time = 23307 ms. SQL Server Execution Times: CPU time = 58515 ms, elapsed time = 26575 ms.

the request was executed in parallel, also 4 cpu. Average query execution time 24357.33ms

links: http://support.microsoft.com/kb/2023536

Max degree of parallelism (DOP) is an additional SQL Server configuration option that has been the subject of many questions and publications. In this blog post, the author hopes to provide some clarity on what this option does and how it should be used.

First, the author would like to clear up any doubt that the option listed sets how many processors SQL Server can use when serving multiple connections (or users) - it does not! If SQL Server has access to four idle processors, and it is configured to use all four processors, it will use all four processors, regardless of the maximum degree of parallelism.

So what does this option do? This option sets the maximum number of processors that SQL Server can use for a single query. If a query to SQL Server must return a large amount of data (many records), it sometimes makes sense to parallelize it, breaking it into several small queries, each of which will return its own subset of rows. Thus, SQL Server can use multiple processors, and therefore, on multiprocessor systems, a large number of records of an entire query can potentially be returned faster than on a single-processor system.

There are many criteria that must be considered before SQL Server invokes "Intra Query Parallelism" (splitting a query into multiple threads), and there is no point in detailing them here. You can find them in BOL by searching for "Degree of parallelism". It says that the decision to parallelize is based on the availability of memory to the processor and, especially, on the availability of the processors themselves.

So why should we consider using this option - because leaving it at its default value (SQL Server makes its own parallelization decisions) can sometimes have undesirable effects. These effects look something like this:

• Paralleled queries run slower.
• Query execution times can become non-deterministic, which can annoy users. Execution times may change because:
  • The query may sometimes parallelize and sometimes not.
  • A request can be blocked by a parallel request if the processors were previously overloaded with work.

Before we continue, the author would like to point out that there is no particular need to dive into the internal organization of parallelism. If you are interested in this, you can read the article "Parallel Query Processing" in Books on Line, which describes this information in more detail. The author believes that there are only two important things to know about the internal organization of concurrency:

1. Parallel queries may spawn more threads than specified in the "Max degree of parallelism" option. DOP 4 can spawn more than twelve threads, four for querying and additional threads used for sorts, streams, aggregates and assemblies, etc.
2. Paralleling requests can cause different SPIDs to wait with the wait type CXPACKET or 0X0200. This can be used to find those SPIDs that are in a waiting state during parallel operations and have a waittype in sysprocesses: CXPACKET. To make this task easier, the author suggests using the stored procedure available on his blog: track_waitstats.

And so “The query may be slower when parallelized” why?

• If the system is very weak throughput disk subsystems, then when analyzing a request, its decomposition can take longer than without parallelism.
• There may be data skew or blocking of data ranges for the processor caused by another process used in parallel and launched later, etc.
• If there is no index on the predicate, resulting in a table scan. Parallel operation within a query can hide the fact that the query would have completed much faster with a sequential execution plan and the correct index.

The effects of parallelism mentioned above should naturally lead you to believe that the internal mechanics of query parallelization are not suitable for use in OLTP applications. These are applications for which changing query execution times can be annoying to users and for which a server serving many concurrent users is unlikely to choose a parallel execution plan due to the inherent processor workload profile of these applications.

Therefore, if you are going to use parallelism, then most likely you will need it for data retrieval tasks (data warehouse), decision support or reporting systems, where there are not many queries, but they are quite heavy and are executed on a powerful server with a large amount of RAM. memory.

If you decide to use parallelism, what value should you set for DOP? A good practice for this mechanism is that if you have 8 processors, then set DOP = 4 and this will most likely be the optimal setting. However, there is no guarantee that it will work this way. The only way to be sure is to test different meanings for DOP. In addition to this, the author wanted to offer his empirical advice to never set this number to more than half the number of processors that are available. If the author had fewer than six processors, he would set DOP to 1, which simply disables parallelization. He might make an exception if he had a database that only supports a single user process (some data retrieval technologies or reporting tasks), in which case, as an exception, it would be possible to set DOP to 0 (the default value), which allows SQL Server itself decides whether to parallelize a query.

Before finishing the article, the author wanted to caution you that parallel index creation depends on the number you set for DOP. This means that you may want to change it while indexes are being created or recreated to improve the performance of this operation, and of course you can use the MAXDOP hint in the query, which allows you to override the value set in the configuration and can be used during off-peak hours .

Finally, your query may slow down when parallelized due to errors, so make sure your server has the latest service pack installed.

set no count on
if not exists (select 1 from sysobjects where name = "waitstats" )
create table waitstats ( varchar ( 80 ),
requests numeric ( 20 ,1 ),
numeric ( 20 ,1 ),
numeric ( 20 ,1 ),
now datetime default getdate())
else truncate table waitstats

dbcc sqlperf (waitstats,clear) -- clear out waitstats

declare @i int
,@delay varchar ( 8 )
,@dt varchar ( 3 )
,@now datetime
,@totalwait numeric ( 20 ,1 )
,@endtime datetime
,@begintime datetime
,@hrint
,@min int
,@sec int

select @i = 1
select @dt = case lower (@delaytype)
when "minutes" then "m"
when "minute" then "m"
when "min" then "m"
when "mm" then "m"
when "mi" then "m"
when "m" then "m"
when "seconds" then "s"
when "second" then "s"
when "sec" then "s"
when "ss" then "s"
when "s" then "s"
else @delaytype
end

if @dt not in ("s" ,"m" )
begin
print "please supply delay type e.g. seconds or minutes"
return
end

if @dt = "s"
begin
select @sec = @delaynum % 60
select @min = cast ((@delaynum / 60 ) as int )
select @hr = cast ((@min / 60 ) as int )
select @min = @min % 60
end

if @dt = "m"
begin
select @sec = 0
select @min = @delaynum % 60
select @hr = cast ((@delaynum / 60 ) as int )
end

select @delay = right("0" + convert(varchar( 2 ),@hr), 2 ) + ":" +
2 ),@min), 2 ) + ":" +
+ right("0" +convert(varchar( 2 ),@sec), 2 )

if @hr > 23 or @min > 59 or @sec > 59
begin
select "hh:mm:ss delay time cannot > 23:59:59"
select "delay interval and type: " + convert (varchar ( 10 )
,@delaynum) + "," + @delaytype + " converts to "
+ @delay
return
end

while (@i<= @num_samples)
begin
insert into waitstats (, requests,
,)
exec ("dbcc sqlperf(waitstats)" )
select @i = @i + 1
waitfor delay @delay
End

Create waitstats report
execute get_waitstats

--//--//--//--//--//--//--//--//--//-//--//--//--//--//--//--//--//--/

CREATE proc get_waitstats
AS
-- This stored procedure is provided =AS IS= with no warranties, and
-- Concerns no rights.
-- Use of included script samples are subject to the terms specified
-- at http://www.microsoft.com/info/cpyright.htm
--
-- this proc will create waitstats report listing wait types by
-- percentage
-- can be run when track_waitstats is executing

set no count on

declare @now datetime
,@totalwait numeric ( 20 ,1 )
,@endtime datetime
,@begintime datetime
,@hrint
,@min int
,@sec int

select @now=max (now),@begintime=min (now),@endtime=max (now)
from waitstats where = "Total"

Subtract waitfor, sleep, and resource_queue from Total

select @totalwait = sum() + 1 from waitstats
where not in ("WAITFOR" ,"SLEEP" ,"RESOURCE_QUEUE"
, "Total" , "***total***" ) and now = @now

Insert adjusted totals, rank by percentage descending

delete waitstats where = "***total***" and now = @now

insert into waitstats select "***total***"
,0
,@totalwait
,@totalwait
,@now

select
,
,percentage = cast ( 100 */@totalwait as numeric ( 20 ,1 ))
from waitstats
where not in ("WAITFOR" , "SLEEP" , "RESOURCE_QUEUE" , "Total" )
and now = @now
order by percentage desc

Max degree of parallelism (DOP) is an additional SQL Server configuration option that has been the subject of many questions and publications. In this blog post, the author hopes to provide some clarity on what this option does and how it should be used.
First, the author would like to clear up any doubt that the option listed sets how many processors SQL Server can use when serving multiple connections (or users) - it does not! If SQL Server has access to four idle processors, and it is configured to use all four processors, it will use all four processors, regardless of the maximum degree of parallelism.
So what does this option do? This option sets the maximum number of processors that SQL Server can use for a single query. If a query to SQL Server must return a large amount of data (many records), it sometimes makes sense to parallelize it, breaking it into several small queries, each of which will return its own subset of rows. Thus, SQL Server can use multiple processors, and therefore, on multiprocessor systems, a large number of records of an entire query can potentially be returned faster than on a single-processor system.
There are many criteria that must be considered before SQL Server invokes "Intra Query Parallelism" (splitting a query into multiple threads), and there is no point in detailing them here. You can find them in BOL by searching for "Degree of parallelism". It says that the decision to parallelize is based on the availability of memory to the processor and, especially, on the availability of the processors themselves.
So why should we consider using this option - because leaving it at its default value (SQL Server makes its own parallelization decisions) can sometimes have undesirable effects. These effects look something like this:

Paralleled queries run slower.

Query execution times can become non-deterministic, which can annoy users. Execution times may change because:

The query may sometimes parallelize and sometimes not.

A request can be blocked by a parallel request if the processors were previously overloaded with work.

Before we continue, the author would like to point out that there is no particular need to dive into the internal organization of parallelism. If you are interested in this, you can read the article "Parallel Query Processing" in Books on Line, which describes this information in more detail. The author believes that there are only two important things to know about the internal organization of concurrency:

Parallel queries may spawn more threads than specified in the "Max degree of parallelism" option. DOP 4 can spawn more than twelve threads, four for querying and additional threads used for sorts, streams, aggregates and assemblies, etc.

Paralleling requests can cause different SPIDs to wait with the wait type CXPACKET or 0X0200. This can be used to find those SPIDs that are in a waiting state during parallel operations and have a waittype in sysprocesses: CXPACKET. To make this task easier, the author suggests using the stored procedure available on his blog: track_waitstats.

And so “The query may be slower when parallelized” why?

If the system has very low throughput of disk subsystems, then when analyzing a request, its decomposition may take longer than without parallelism.

There may be data skew or blocking of data ranges for the processor caused by another process used in parallel and launched later, etc.

If there is no index on the predicate, resulting in a table scan. Parallel operation within a query can hide the fact that the query would have completed much faster with a sequential execution plan and the correct index.

From all this, it follows a recommendation to check the execution of the request without parallelism (DOP=1), this will help identify possible problems.
The effects of parallelism mentioned above should naturally lead you to believe that the internal mechanics of query parallelization are not suitable for use in OLTP applications. These are applications for which changing query execution times can be annoying to users and for which a server serving many concurrent users is unlikely to choose a parallel execution plan due to the inherent processor workload profile of these applications.
Therefore, if you are going to use parallelism, then most likely you will need it for data retrieval tasks (data warehouse), decision support or reporting systems, where there are not many queries, but they are quite heavy and are executed on a powerful server with a large amount of RAM. memory.
If you decide to use parallelism, what value should you set for DOP? A good practice for this mechanism is that if you have 8 processors, then set DOP = 4 and this will most likely be the optimal setting. However, there is no guarantee that it will work this way. The only way to be sure is to test different values ​​for DOP. In addition to this, the author wanted to offer his empirical advice to never set this number to more than half the number of processors that are available. If the author had fewer than six processors, he would set DOP to 1, which simply disables parallelization. He might make an exception if he had a database that only supports a single user process (some data retrieval technologies or reporting tasks), in which case, as an exception, it would be possible to set DOP to 0 (the default value), which allows SQL Server itself decides whether to parallelize a query.
Before finishing the article, the author wanted to caution you that parallel index creation depends on the number you set for DOP. This means that you may want to change it while indexes are being created or recreated to improve the performance of this operation, and of course you can use the MAXDOP hint in the query, which allows you to override the value set in the configuration and can be used during off-peak hours .
Finally, your query may slow down when parallelized due to errors, so make sure your server has the latest service pack installed.

• Tutorial

This instruction is intended for beginners looking for a simple guide in Russian for installing the English version of SQL Server 2012, which will then be used for SharePoint 2013.
This article is not for professionals.

All work is divided into 3 stages:

• Installing SQL Server 2012
• Setting the server configuration parameter max degree of parallelism
• Configure the rights of the account that is intended to install SharePoint 2013

Attention: there are a lot of pictures under the cut!

Installing SQL Server 2012

2. The installation wizard will start. He will perform the check. You can click on the “Show details” button and view a detailed report:

3. Detailed report. Click the “OK” button:

4. Enter the product key and click the “Next” button:

5. We agree to the terms of the license agreement.
To do this, check the box " I accept the license terms

6. At the “Setup Role” step, select the first item " SQL Server Feature Installation". Click the “Next” button:

7. At the “Feature Selection” step, mark " Database Engine Services", "Management Tools – Basic" And " Management Tools – Complete". Then click the “Next” button:

8. The installer will then perform another check. You can click on the “Show details” button and view a detailed report:

9. Detailed report. (At this stage, I had an error in the “Microsoft .NET Framework 3.5 is installed...” rule. More on that below). Click the “Next” button:

10. At the “Instance Configuration” step, you need to configure the SQL Server service instance.
I repeat that this article is intended for beginners. Therefore, we will make the assumption that SQL Server has not been installed on your server before, which means we will leave all the default settings. Click the “Next” button:

11. At this step, the installation wizard will display the disk space requirements. Click the “Next” button:

12. At the "Server Configuration" step, you must specify a domain account for the service " SQL Server Database Engine". After filling out the “Account Name” and “Password” fields, click the “Next” button:

13. At the “Database Engine Configuration” step, just add the current user to the SQL server administrators. To do this, click the “Add Current User” button, then click the “Next” button:

14. In the next step, click the “Next” button:

15. Next, the installation wizard will perform the test again and display its results. Click the “Next” button:

16. At the “Ready to Install” step, the wizard will display summary information. Here you need to click the “Install” button:

17. After installation is complete, information about the operations performed will be displayed:

18. I highly recommend restarting your computer at this stage. In some cases (for example, when installing Microsoft .NET Framework 3.5), the installation wizard itself will display a window asking you to restart the computer. Don't refuse.

Setting the server configuration parameter max degree of parallelism

1. Launch Microsoft SQL Server Management Studio(Start - All Programs - Microsoft SQL Server 2012 - SQL Server Management Studio).

2. On the server connection screen, click the “Connect” button.

3. Right-click on your server in the " Object Explorer" and select " Properties":

4. In the server properties window that opens, in the left menu, select the page " Advanced" and scroll the list of properties to the very bottom of the screen. Set the parameter value " Max Degree of Parallelism" V 1 and click OK:

5. Do not close SQL Server Management Studio, we will need it later.

Configure the rights of the account that is intended to install SharePoint 2013

• dbcreator
• securityadmin
• public

2. In the “Login name” field, enter the domain name of the account under which you plan to install and configure SharePoint 2013.

3. In the left menu, select the page " Server Roles" and check the roles "dbcreator" and "securityadmin", and also make sure that the role "public" is already checked. Then click the "OK" button:

Now the SQL server is ready to install SharePoint 2013.

Installing Microsoft .NET Framework 3.5 in MS Windows Server 2012 R2 Standart

1. You need to open the console" Server Manager".

2. In the left menu, select the “Dashboard” item.

3. In the center of the window, click on the “Add roles and features” item.

4. In the wizard that opens, skip the “Before You Begin” step.

5. At the “Installation Type” step, select the item " Role-based or feature-based installation". Click the “Next” button.

6. In the next step, leave everything as default and click the “Next” button.

7. Skip the “Server Roles” step by clicking the “Next” button.

8. At the “Features” step, check the “.NET Framework 3.5 Features” checkbox. Click the “Next” button.

9. Once the installation process is complete, you can close the Add Roles and Features Wizard.

10. Done!

Good luck to everyone and peaceful skies above your heads!

P.S. Happy upcoming Cosmonautics Day!

SCOPE OF THIS ARTICLE: SQL Server (since 2008)Azure SQL DatabaseAzure SQL Data WarehouseParallel Data Warehouse

This section describes how to configure the server configuration parameter max degree of parallelism (MAXDOP) in SQL Server 2016 using SQL Server Management Studio or Transact-SQL. When an instance of SQL Server runs on a multiprocessor computer, it determines the optimal degree of parallelism, that is, the number of processors used to execute a single statement, for each of its parallel execution plans. To limit the number of processors in a parallel execution plan, the parameter can be used max degree of parallelism. SQL Server considers parallel execution plans for queries, DDL operations on indexes, parallel inserts, online column modifications, parallel statistics collection, and population of static and keyset-driven cursors.

Restrictions

• If the affinity mask parameter is set to a value other than the default, it can limit the number of processors available to SQL Server on symmetric multiprocessor (SMP) systems.

This setting is optional and should only be changed by experienced database administrators or certified SQL Server technicians.

To allow the server to determine the maximum degree of parallelism, set this parameter to 0, which is the default value. Setting the maximum degree of parallelism to 0 allows SQL Server to use all available processors (up to 64 processors). To disable the creation of parallel plans, set the parameter max degree of parallelism value 1. Set the parameter to a value between 1 and 32,767 to specify the maximum number of processor cores that can be used to run a single query. 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, then the parameter value max degree of parallelism will not be taken into account.

The value of the max degree of parallelism parameter can be overridden by specifying a MAXDOP query in the statement. For more information, see .

The operations of creating and rebuilding indexes, as well as deleting a clustered index, can be quite resource-intensive. The value of the max degree of parallelism parameter for index operations can be overridden by specifying the MAXDOP index parameter in the statement. The MAXDOP value is applied to the statement at run time and is not stored in the index metadata. For more information, see the article.

In addition to query and index operations, this parameter also controls the degree of parallelism when executing the DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP statements. Concurrency plans for these instructions can be disabled using trace flag 2528. For more information, see .

Safety

Permissions

Permissions to execute a stored procedure sp_configure without parameters or with only the first parameter are provided to all users by default. To perform the procedure sp_configure with both options, you must have ALTER SETTINGS permission at the server level to change a configuration setting or run a RECONFIGURE statement. ALTER SETTINGS permission is implicitly granted to fixed server roles sysadmin And serveradmin .

IN Object Explorer right click on the server and select Properties.

Click a node Additionally .

In field Maximum degree of parallelism Specify the maximum number of processors that can be used in the parallel execution plan.

Configuring the Maximum Degree of Parallelism parameter

Establish a connection to the Database Engine.

In the Standard panel, click Create a request.

Copy the following example into the query window and click the button Execute. This example describes how to use a procedure to set the max degree of parallelism parameter to 8.

USE AdventureWorks2012 ; GO EXEC sp_configure "show advanced options" , 1 ; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure "max degree of parallelism" , 8 ; GO RECONFIGURE WITH OVERRIDE; GO

For more information, see the article