Article Number
000039109
Applies To
Product(s): Archer
Version(s): All Versions
Primary Deployment: On Premises
Description
Archer users are experiencing slowness when navigating through Archer and/or Archer Jobs are running for long periods of time.
Resolution
Archer recommends setting the Max Degree of Parallelism to 1 for the Archer Databases.
Max Degree of Parallelism (MAXDOP or Max DOP) is a feature in SQL Server to optimize query performance. It splits SQL query across multi core-processors running in parallel and sharing workload; subsequently, enhancing CPU utilization and significantly improving performance. MAXDOP value ranges between 0 to 32,767 from SQL Server 2016 onwards.
Standardizing MAXDOP value for an application:
To make best use of this feature, it is very important to understand the underlying factors that contribute towards SQL Server operation under any recommended infrastructure/platform. These factors are:
- The number of Core processors.
- The pattern of tasks performed by SQL Server Engine - Serial vs Parallel.
- The parameter set for the cost threshold for parallelism.
MAXDOP value does not necessarily mean the total number of parallel threads that are executed by SQL Engine at any point in time; instead, it applies to the number of parallel threads per SQL operator (e.g., Index Seek, Nested Loops, Compute Scalar, etc.). The total number of concurrent threads can ultimately exceed the MAXDOP value specified.
As best practice, SQL Server recommends using the number of logical processors as the MAXDOP value, hence, there is resilience in operation as each processor handles threads in parallel.
While benchmarking the value, it is equally important to assess the risk involved in introducing parallelism to any application. Splitting of queries can also have an adverse impact on the entire system. Additional workload on CPU can cause resource contention on the DISK IO subsystem. SQL Server holds lock when multiple threads concurrently access the same table, thereby resulting in higher CXPACKET waits. Due to these SQL blocks, timeouts and deadlocks appear on the server degrading the overall performance.
How to view MAXDOP via three different methods:
- SQL Server Management Studio > Server Properties > Advanced > Max Degree of Parallelism. This will apply to all databases.
- SQL Server Management Studio > Archer Database > Options > Max DOP. This will apply to the selected database.
- SQL Query Analyzer:
Type of MAXDOP configuration and its significance:
- MAXDOP 0 - This is the default recommended value. SQL Server Engine optimally decides on the number of parallel threads a SQL operator can be split with respect to the number of actual available logical CPUs.
- MAXDOP 1 - This stops parallel plan generation and forces SQL Server to use one processor. Typically, DML statements (Insert, Update, Delete) executes much faster with relatively cheaper query cost in serial executions. Apparently, when these processes are run in parallel, an extra cost is incurred to sync all threads involved in the operation. Often Nested queries and Long searches perform better with MAXDOP 1.
- MAXDOP > 1 - (Limit to the number of Core processors) It confines the number of threads a SQL query can be split into depending on the logical processors available. There is an added advantage for read accessibility via parallel routes, hence, better throughput is achieved. Even if a higher value is specified, SQL Server internally sets to the actual number of available CPUs. MAXDOP >1 is preferred for environments with higher concurrent activities and smaller batch executions.
As per current Archer DB schema, there are 20 stored procedures which explicitly uses SQL Hint and enforces serial plan execution overriding any level of parallelism configured.
Notes
Command to set the parameter:
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'max degree of parallelism', '1' GO RECONFIGURE; GO
