MSSQL : Recommedation for maximum degree of parallelism ( MAXDOP )

What is MAXDOP ?

Maximum degree of parallelism (MAXDOP), that helps to controls the number of processors used to run a single statement that has a parallel plan for running. The default value is set to 0, which allows you to use the maximum available processors on the machine.

Please use the following recommendations when you configure the maximum degree of parallelism server value:

    - Single NUMA node: < = 8 logical processors, keep MAXDOP <= actual number of cores
    - Single NUMA node: > 8 logical processors, keep MAXDOP = 8
    - Multiple NUMA nodes: < =16 logical processors, keep MAXDOP <= actual number of cores
    - Multiple NUMA nodes: > 16 logical processors, keep MAXDOP = 16 (SQL Server 2016 and above), keep MAXDOP = 8 (prior to SQL Server 2016)

SQL Server estimates the cost of the query at the time of execution, If this cost exceeds the cost threshold of parallelism, SQL Server considers parallel plan for this query. The number of processors it can use is defined by the instance-level maximum degree of parallelism, which is superseded by the database-level maximum degree of parallelism, which in turn is superseded by the query hint for maximum degree of parallelism at the query level.

To gather the current NUMA configuration for SQL Server 2016 and higher, run the following query:

        SELECT
         @@SERVERNAME,
         SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
        cpu_count,  /*the number of logical CPUs on the system*/
        hyperthread_ratio, /*the ratio of the number of logical or physical cores that are exposed by one physical processor package*/
        softnuma_configuration, /* 0 = OFF indicates hardware default, 1 = Automated soft-NUMA, 2 = Manual soft-NUMA via registry*/
        softnuma_configuration_desc, /*OFF = Soft-NUMA feature is OFF, ON = SQL Server automatically determines the NUMA node sizes for Soft-NUMA, MANUAL = Manually configured soft-NUMA */
        socket_count, /*number of processor sockets available on the system*/
        numa_node_count /*the number of numa nodes available on the system. This column includes physical numa nodes as well as soft numa nodes*/
        from sys.dm_os_sys_info

You can check the current value using the following query:
       
        Exec sp_configure 'max_degree_of_parallelism'

Working of MAXDOP at Different level in SQL Server: 

I would like to put shed some light about working of MAXDOP at different level:
As you know the Maximum Degree of Parallelism (MAXDOP) can be defined in one of three ways:

    1. Instance Scoped via sp_configure
    2. Database Scoped via database properties
    3. Query Scoped via query hint option

So now which of these will trump other as follows:

   - A query hint always overrides the database and instance configuration.
   - The database scoped configuration will override the instance configuration only
when it is not the default value ( 0 ).
   - The instance scoped configuration limits MAXDOP across all databases and queries
when the a query option and database scope have not been defined.

If the databases are configured with MAXDOP=0 as the Database Scope Configuration,
then the instance level MAXDOP setting is used.

To Verify current setting or to Change at database level MAXDOP:
	1. Open the database properties dialog in the UI from Object Explorer
	2. Navigate to the Options tab >> Advanced
	3. Under Parallelism tab >> Max degree of parallelism (the default value = 0 )

	Or you can use query:

	SELECT * FROM sys.database_scoped_configurations