000034021 - Adjusting parallelism in ECAT to improve database performance

Document created by RSA Customer Support Employee on Sep 21, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000034021
Applies ToRSA Product Set: ECAT
RSA Product/Service Type: ECAT
RSA Version/Condition: 4..0.x,4.1.x,4.2.x
Platform: Windows
Platform (Other): SQL 2012 Standard/Enterprise, SQL 2008 Standard/Enterprise
IssueDatabase performance is lagging, or else improvements are generally desired to increase system performance.
TasksOpen the MSSQL Studio software
Right-click the server instance
Modify the cost threshold and Max Degree of Parallelism fields
Adjusting Parallelism

It is necessary to adjust the number of cores handling sql statements in parallel and the cost threshold which is the estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. The reason this is an improvement over assigning a query to all the cores at once, is because it requires more coordination to split the work across all cores. Additionally, you can't do any other jobs while this is happening. The purpose of assigning cores this way is to balance the cost of each query against enough cores to get maximum efficiency without impacting other running jobs.
NOTE! This should be applied only against a database dedicated for use with ECAT as changing this setting affects other applications on the database as well, possibly in very negative ways.

  1. Adjust parallelism using the following steps:
    1. Select the Database instance name at the top of the Object Explorer tab and right-click. Select Properties, then Advanced.
    2. Change “Cost Threshold for Parallelism” from the default value of 5 to 50.
    3. Change “Max degree of Parallelism” from the default value of 0 to 8.

NotesIt is important to note that this applies differently on different size deployments. To put it another way:

This KB could be said to apply as “one size fits all” provided the deployment is 10K agents or larger.

Below that, only the max degree of parallelism should be changed and it should be somewhere between 2 and 8 based on minimum between number of cores and number of disks (8 cores and 4 disk RAID-10 would lead to Max DOP of 4 for example).
The threshold of 50 should remain that way on larger deployments.