Database performance is lagging, or else improvements are generally desired to increase system performance.

Tasks

Open the MSSQL Studio softwareRight-click the server instanceModify the cost threshold and Max Degree of Parallelism fields

Resolution

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.

Adjust parallelism using the following steps:

Select the Database instance name at the top of the Object Explorer tab and right-click. Select Properties, then Advanced.

Change “Cost Threshold for Parallelism” from the default value of 5 to 50.

Change “Max degree of Parallelism” from the default value of 0 to 8.

Notes

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