|Applies To||RSA Product Set: ECAT|
RSA Product/Service Type: ECAT
RSA Version/Condition: 4..0.x,4.1.x,4.2.x
Platform (Other): SQL 2012 Standard/Enterprise, SQL 2008 Standard/Enterprise
|Issue||Database performance is lagging, or else improvements are generally desired to increase system performance.|
|Tasks||Open the MSSQL Studio software|
Right-click the server instance
Modify the cost threshold and Max Degree of Parallelism fields
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.
|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.