SQL Server Maximum Degree of Parallelism (MAXDOP)
This article is only applicable for KTA version of 7.5 and earlier, and is essential for good performance at higher system load in these versions.
The Maximum Degree of Parallelism (MAXDOP) setting appropriately controls how much SQL Server may try to use parallelism in executing a particular query. KTA 7.5 and earlier do not perform well when parallelism is used with queries to the TotalAgility_Documents, TotalAgility_Reporting, and TotalAgility_Reporting_Staging databases.
In many cases, especially under load, the difference is dramatic. That is why the KTA 7.5 Best Practices Guide recommends setting MAXDOP to 1, which disables parallelism, for the TotalAgility_Documents, TotalAgility_Reporting, and TotalAgility_Reporting_Staging databases. This recommendation also applies to previous versions of KTA.
Starting with KTA 7.6, parallelism no longer needs to be disabled, so refer to Microsoft recommendations for setting MAXDOP appropriately for your server.
Applying the recommended MAXDOP Setting
SQL Server 2016 and Later
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.
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.
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 --If SQL 2016 declare @SqlServerProductMajorVersion smallint ; SELECT @SqlServerProductMajorVersion = CONVERT(sysname, SERVERPROPERTY ('ProductMajorVersion')); IF @SqlServerProductMajorVersion > 12 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 --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
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.