Changing MAX_DOP setting in SQL Azure DB V12
When SQL Server runs on a computer with 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. To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution. The maximum value for thedegree of parallelism setting is controlled by the edition of SQL Server, CPU type, and operating system. 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. [1].
What is the default MAX DOP in SQL DB V12?
Default setting for MAX_DOP is 0 in SQL Azure Database V12.
How to identify if the database hit the max worker threads?
Run the following query by connecting to the master database:
SELECT * FROM sys.resource_stats
and look for max_worker_percent. This shows the worker threads utilization. If it shows 100% then it is obvious that application is saturating the worker threads.
If the number of connections to the database are less than the max allowed and the worker threads exhausted then it clearly because of the max dop setting on the database.
Should I reset MAX DOP then?
Well if a few queries hijacking all the worker threads, then you should seriously consider setting it to some value < max CPU available.
How this setting impacts Basic / Standard databases?
Changing this setting on standard databases has no impact as the number of cores available to it are not more than 1.
How to check the current max dop setting?
select * from sys.database_scoped_configurations
How to Change the MAX DOP setting?
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
References: https://technet.microsoft.com/en-us/library/ms181007(v=sql.105).aspx