When SQL Server runs on a computer with more than one processor or CPU, it detects the best degree of parallelism, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. You can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.
The MAXDOP hint is used to implicitly specify the maximum degree of parallelism to use for a specific statement. The degree of parallelism is roughly defined as how many separate processors to utilize for a single query.
SELECT *
FROM dbo.test_demo
OPTION (MAXDOP 1)
This will force only one instance of the SPID to be spawned. To allow more instances of a SPID to be spawned replace the 1 with the maximum number.
The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.
We can change this value based on our requirment.
sp_configure 'max degree of parallelism' 5.
This means if a query uses a parallel execution plan it will only use four of the available processors.
The MAXDOP hint is used to implicitly specify the maximum degree of parallelism to use for a specific statement. The degree of parallelism is roughly defined as how many separate processors to utilize for a single query.
SELECT *
FROM dbo.test_demo
OPTION (MAXDOP 1)
This will force only one instance of the SPID to be spawned. To allow more instances of a SPID to be spawned replace the 1 with the maximum number.
This needs to be specified after each statement. It is not specific to the statement.
what is default value for maxdop?The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.
We can change this value based on our requirment.
sp_configure 'max degree of parallelism' 5.
This means if a query uses a parallel execution plan it will only use four of the available processors.
No comments:
Post a Comment