Microsoft KB Archive/899000

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

Article ID: 899000

Article Last Modified on 10/25/2007

-

APPLIES TO


 * 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

-



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 2000 or SQL Server 2005 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 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 &quot;n&quot; 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, follow these steps:
 * 1) Click Start, point to Programs, point to Microsoft SQL Server 2005, 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 'show advanced option', '1' RECONFIGURE WITH OVERRIDE GO

EXEC sp_configure 'max degree of parallelism' GO 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.

