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

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 "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:

Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager to start SQL Server Enterprise Manager.
Expand the server group that contains the target server.
Right-click the target server, and then click Properties.
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:

Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio .
Connect to the instance of SQL Server that you want.
Right-click the target server, and then click Properties.
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.

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 2000 or SQL Server 2005 database instance that hosts the BizTalk Server 2004 or BizTalk Server 2006 BizTalkMsgBoxDB database

For more information about the Parallelism setting in SQL Server, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_7h9q.asp