HOW TO: Manage SQL Server Parallelism with RockSolid

SQL Server query parallelism can be configured to restrict the number of CPU's that a given query execution can across at run time.  Restriction of the number of CPU's available to a given query execution is a useful method of managing SQL Server concurrency.  

Different workloads through the course of an operational schedule may have different parallelism requirements.  For example, you may wish to limit query parallelism to a single CPU during business hours.  Conversely, during batch or maintenance windows you may wish to unrestrict parallelism so batch queries can run on all available cores.  Whatever your requirement, RockSolid can be configured to manage the setting of parallelism automatically based on your policy settings.

Prerequisites

Before you can enable automated query parallelism you must have:

  • RockSolid monitoring enabled for the instance
  • Automated management enabled for the instance

Configure RockSolid Parallelism Management

To configure RockSolid to automatically management parallelism, perform the following steps:

  • At the relevant level in your instance hierarchy configure you instance calendar.  This is an important step and may impact a number of other functions within RockSolid, and how RockSolid deploys maintenance jobs within your environment.

  • At the relevant level in your isntance heirarchy navigate to the Settings tab to view the instance policy, and click on the "Instance Config" sub-tab.
  • Find the "Query Paralleism" heading and set "Manage Parallelism" to on.
  • For each window type you have configure in your calendar, specify the parallelism setting to be applied during that window.
  • In addition set the "Undefined Hours Type" to the default parallelism value you want RockSolid to apply if no calendar window is defined.
  • Click Save to apply the policy.

How RockSolid Implements Parallelism Management

RockSolid will take the settings you have configured above and construct a SQL Agent job that is deployed to each instance.  This job runs every 15 minutes and checks the current time with the windows configured within the instance calendar configuration.  As the instance moves between windows the SQL Agent job will change parallelism to the corresponding value.

NOTE: This job is deployed automatically from RockSolid.  Any changes you make directly to this job will be automatically removed and lost.  The correct method of changing the job properties is to update the RockSolid policy for the instance. 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.