The Parallelism setting for the instance of SQL Server when you configure BizTalk Server

Article translations Article translations
Article ID: 899000 - View products that this article applies to.
Expand all | Collapse all

INTRODUCTION

When you configure Microsoft BizTalk Server, the Parallelism setting for the instance of Microsoft SQL Server that houses the BizTalk Server databases is changed. This behavior occurs if the instance of SQL Server has multiple processors available. When you configure BizTalk Server, the Max Degree of Parallelism setting is set to 1 in the instance of SQL Server that hosts the BizTalkMsgBoxDB database.

Note The Parallelism setting does not affect the number of processors that SQL Server uses in a multiple-processor environment. The Parallelism setting only governs the number of processors on which any particular Transact-SQL statement can run at the same time. If the Parallelism setting is set to use one processor, the SQL Server query optimizer will not create execution plans that permit any particular Transact-SQL statement to run on multiple processors at the same time.

MORE INFORMATION

BizTalk Server database queries are relatively small, and they execute quickly. Therefore, BizTalk Server database queries do not benefit from a Parallelism setting that specifies using more than one processor. Changing the Parallelism setting to specify using more than one processor may have an adverse effect on BizTalk Server database queries.

Note If the instance of SQL Server 2000 that houses the BizTalk Server databases has only one processor available, Use all available processors is selected under Parallelism. In this case, Use all available processors is functionally equivalent to Use "n" processors together with 1 selected as the number of processors.

To view the Parallelism setting for an instance of SQL Server 2000, follow these steps:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager to start SQL Server Enterprise Manager.
  2. Expand the server group that contains the target server.
  3. Right-click the target server, and then click Properties.
  4. In the SQL Server Properties dialog box, click the Processor tab.
To view the Parallelism setting for an instance of SQL Server 2005 or a later version, follow these steps:
  1. Click Start, point to Programs, point to Microsoft SQL Server 200x, and then click SQL Server Management Studio .
  2. Connect to the instance of SQL Server that you want.
  3. Right-click the target server, and then click Properties.
  4. Click Advanced. Note the Max Degree of Parallelism setting in the right pane.
You can also view the Parallelism setting for an instance of SQL Server by executing the following Transact-SQL statement against the instance of SQL Server.
USE master 
EXEC sp_configure 'max degree of parallelism'
If the value is not set to 1, execute the following Transact-SQL statement against the instance of SQL Server:
USE master 
EXEC sp_configure 'max degree of parallelism', '1' 
reconfigure with override
The value in the run_value column that is returned by this query indicates the Parallelism setting for the instance of SQL Server.

If changing the Parallelism setting for an instance of SQL Server will have an adverse effect on other database applications that are being executed on the instance, you should create a separate instance of SQL Server to house the BizTalk Server databases before you run the Configuration Framework Wizard.

REFERENCES

For more information about why the Max Degree of Parallelism setting is changed when you configure BizTalk Server, click the following article number to view the article in the Microsoft Knowledge Base:
912262 The auto update statistics option, the auto create statistics option, and the Parallelism setting are turned off in the SQL Server database instance that hosts the BizTalk Server BizTalkMsgBoxDB database
For more information about the Parallelism setting in SQL Server, visit the following Microsoft Developer Network (MSDN) Web sites:
http://msdn2.microsoft.com/en-us/library/aa196725(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/ms181007.aspx

Properties

Article ID: 899000 - Last Review: May 24, 2009 - Revision: 4.0
APPLIES TO
  • Microsoft BizTalk Server 2009 Branch
  • Microsoft BizTalk Server 2009 Developer
  • Microsoft BizTalk Server 2009 Enterprise
  • Microsoft BizTalk Server 2009 Standard
  • Microsoft BizTalk Server 2006 R2 Branch Edition
  • Microsoft BizTalk Server 2006 R2 Developer Edition
  • Microsoft BizTalk Server 2006 R2 Enterprise Edition
  • Microsoft BizTalk Server 2006 R2 Standard Edition
  • Microsoft BizTalk Server 2006 Standard Edition
  • Microsoft BizTalk Server 2006 Enterprise Edition
  • Microsoft BizTalk Server 2006 Developer Edition
  • Microsoft BizTalk Server 2004 Developer Edition
  • Microsoft BizTalk Server 2004 Enterprise Edition
  • Microsoft BizTalk Server 2004 Partner Edition
  • Microsoft BizTalk Server 2004 Standard Edition
Keywords: 
kbbiztalk2004-2006swept kbinfo KB899000

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com