Database Performance - Maximum Degree of Parallelism

How to configure SQL Server 2016 and Later Including Azure

In SQL Server 2016 and later, MAXDOP is available as a database level setting.  In SQL Server Management Studio, for each applicable database (TotalAgility_Documents, TotalAgility_Reporting, and TotalAgility_Reporting_Staging):

• Right click on the database and click Properties
• Go to Options, and scroll down to the group named Database Scoped Configurations
• Change Max DOP to 1
• In this same window is another setting critical for performance: Legacy Cardinality Estimation.

How to configure the setting for OPMT Tenant Databases

In an On-Premise Multi-Tenant installation, the tenant database combines the contents of the  applicable databases (TotalAgility_Documents, TotalAgility_Reporting, and TotalAgility_Reporting_Staging), as well as contents of the Main/Archive/Finished databases. 

Any benefit from higher Max DOP on the main tables will pale in comparison to the negative effect on the Documents/Reporting tables if it is not set to 1.  Thus for good performance it is essential to set Max DOP to 1 on the tenant database.

How to configure SQL Server 2014 and Earlier

SQL Server 2014 and earlier uses an instance level setting to control MAXDOP, so the same setting will apply to all databases within the instance.  It is essential that parallelism be disabled on the the TotalAgility_Documents, TotalAgility_Reporting, and TotalAgility_Reporting_Staging databases, even if this means that it will be disabled for other databases in the instance. 

• Right click on the server and click Properties
• Click Advanced, and scroll down to the Parallelism group
• Change Max Degree of Parallelism to 1

Checking MAXDOP Status

Instead of using the version specific instructions to check the properties of the instance or each database, the following script can be run the check the MAXDOP setting at each level regardless of SQL Server version.  Execute the script in the specific way described below.

SELECT @@VERSION AS Version
SELECT ServerProperty('ServerName') AS ServerName

--If SQL 2016+ non-Azure
IF CONVERT(sysname, SERVERPROPERTY ('ProductMajorVersion')) > 12 AND CONVERT(sysname, SERVERPROPERTY ('EngineEdition'))<5
BEGIN
    -- For SQL 2016+
    -- Check MAXDOP, LEGACY_CARDINALITY_ESTIMATION
    RAISERROR(' *** Database scoped configurations (MAXDOP in SQL 2016+) *** ', 0, 1) WITH NOWAIT
    EXEC sys.sp_MSforeachdb N'SELECT name, CAST(value AS NVARCHAR(1)), N''?'' AS DatabaseName FROM ?.sys.database_scoped_configurations WHERE name=''MAXDOP'';';
END

--If Azure
IF CONVERT(sysname, SERVERPROPERTY ('EngineEdition'))>4 BEGIN
    RAISERROR(' *** AZURE SQL: Please query provide results of the following query for each KTA database: *** ', 0, 1) WITH NOWAIT
    RAISERROR(' SELECT DB_NAME() AS DBName, * FROM sys.database_scoped_configurations', 0, 1) WITH NOWAIT
    RAISERROR('  ', 0, 1) WITH NOWAIT
END

--Check instance level MAXDOP
RAISERROR(' *** Instance level MAXDOP (SQL Server 2014 and earlier) *** ', 0, 1) WITH NOWAIT
select name,
    convert(int, isnull(value, value_in_use)) as config_value,
    convert(int, value_in_use) as run_value
from sys.configurations
WHERE name='max degree of parallelism'
      
-- Requires VIEW ANY DATABASE server-level permission
RAISERROR(' *** Database compatiblity level *** ', 0, 1) WITH NOWAIT
SELECT d.compatibility_level, d.name
FROM sys.databases AS d

Azure SQL: Note that in Azure SQL the above script cannot enumerate the separate databases, so either connect to each KTA DB separately and get the results from the following:

SELECT DB_NAME() AS DBName, * FROM sys.database_scoped_configurations

Or, if your user has access to each database, replace default names with the database names from your environment and get results from the following query:

SELECT 'TotalAgility' AS DBName, * FROM TotalAgility.sys.database_scoped_configurations
UNION ALL
SELECT 'TotalAgility_Documents' AS DBName, * FROM TotalAgility_Documents.sys.database_scoped_configurations
UNION ALL
SELECT 'TotalAgility_Reporting' AS DBName, * FROM TotalAgility_Reporting.sys.database_scoped_configurations
UNION ALL
SELECT 'TotalAgility_Reporting_Staging' AS DBName, * FROM TotalAgility_Reporting_Staging.sys.database_scoped_configurations
ORDER BY name, DBName

How to run this script

Because this script returns many different result sets, it is substantially more convenient to save the results by running the scripts this way:

• After opening a script in SQL Management Studio, Click the “Query” menu, then under “Results To…”, choose “Results to File”.
• Alternatively, use the shortcut: Ctrl + Shift + F.
• Now when you execute the script it will prompt to save the entire set of results as a file.
• It is important to save the results using a descriptive filename.