Skip to main content

KTA Database Performance: Maximum Degree of Parallelism


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.

--If SQL 2016
declare @SqlServerProductMajorVersion smallint ; SELECT @SqlServerProductMajorVersion = CONVERT(sysname, SERVERPROPERTY ('ProductMajorVersion'));
IF @SqlServerProductMajorVersion > 12
    -- For SQL 2016+
    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'';';

--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,
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.

Applies to:  

Product Version
KTA 7.5
KTA 7.4
KTA 7.3