i4connected Knowledgebase 5.6

SQL Server Execution plan

Abstract

Check out this article and learn how to avoid running multiple requests on your i4connected server without flooding it by setting the Max Degree of Parallelism value properly.

It is well known that the SQL Server can run multiple queries in parallel, but if a query does not use a parallel execution plan it can lead to CPUs over-flooding.

The parallelism concept comes from dividing a big task into smaller tasks, where each small task is assigned to a specific processor, to accomplish part of the main task. Finally, the partial results taken from each small task will be combined together into one final result.

Here are the steps you needed to properly configure the SQL Server execution plan:

  1. Open the Microsoft SQL Server Management Studio application.

  2. Click-right on the Server node and select the Properties option.

  3. In the Server properties dialog click on the Advanced section.

  4. Under the Parallelism category, proceed with the following settings, considering the amount of parallel query threads from your current execution plan:

    Max_degree_of_parallelism.jpg
    1. Set the value of the Cost Threshold for Parallelism.

    2. Set the value of the Max Degree of Parallelism.