SQL Server Maximum Degree of Parallelism (MAXDOP)
When using KTA version of 7.7 or lower this setting is essential for good performance.
The Maximum Degree of Parallelism (MAXDOP) setting appropriately controls how much SQL Server may try to use parallelism in executing a particular query. 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.
In KTA 7.6 and KTA 7.7 the best practices guide defers to Microsoft recommendations for setting MAXDOP appropriately for your server. However, setting MAXDOP to 1 is still an important part of improving performance, especially for the Documents database.
KTA 7.8 makes significant changes in the structure of the Documents database aimed at improving performance which should prevent the need for this setting.
Applying the recommended MAXDOP Setting
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.
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 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.