SQL Server Blockage

The Plant Applications SQL Server can fail when running at the same time as an Online-Transaction Processing (OLTP) application and a reporting database.

About this task

Because SQL transactions are typically small for OLTP applications, a common practice is to disable parallelism to minimize conflicts between different processes. To improve the response of large complex reporting queries however, it is generally recommended to maximize parallelism. If the Plant Applications SQL Server starts to experience a significant amount of blocking that affects the timely interaction of Plant Applications with operators and control systems, reducing or disabling the parallelism option may alleviate the issue.

You should initially restrict the SQL Server parallelism option it to use half the number of processors available. If not adequate, try disabling parallelism altogether by setting the option to 1 so that one processor is used for a given query. For more about max degree of parallelism option, see the SQL Server documentation.

Procedure

  1. In SQL Server Management Studio, right-click the server, then click Properties to display the Server Properties dialog box.
  2. Under Select a page, click Advanced to display the Advanced page.
  3. Under Parallelism, edit the Max Degree of Parallelism value. The default is 0 (zero), which uses the actual number of available processors.
    OptionDescription
    EditRecommended value: half the number of processors on the server.
    1Disables the option.
  4. Click OK.