Microsoft KB Archive/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 =

Article ID: 912262

Article Last Modified on 11/20/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 Developer Edition
 * Microsoft BizTalk Server 2006 Standard Edition
 * Microsoft BizTalk Server 2006 Enterprise Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
 * Microsoft BizTalk Server 2004 Enterprise Edition
 * Microsoft BizTalk Server 2004 Developer Edition
 * Microsoft BizTalk Server 2004 Partner Edition
 * Microsoft BizTalk Server 2004 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-





INTRODUCTION
This article discusses why the auto create statistics option and the auto update statistics option are disabled and the Max Degree of Parallelism setting is set to 1 in the Microsoft SQL Server 2000 or Microsoft SQL Server 2005 server instance that hosts the Microsoft BizTalk Server 2004 BizTalkMsgBoxDB database or the Microsoft BizTalk Server 2006 BizTalkMsgBoxDB database.



MORE INFORMATION
BizTalk Server relies heavily on SQL Server for accurate and rapid data processing. The auto create statistics option, the auto update statistic option, and the Max Degree of Parallelism setting can significantly change the execution plan of a query from one instance of SQL Server to another instance of SQL Server. Therefore, to provide consistent support for all customers, BizTalk Server pre-hints all queries with the best possible search criteria for each query scenario. This behavior enables consistent BizTalk Server to SQL Server interaction. The interaction can be benchmarked and supported by Microsoft regardless of the customer environment.

To enable BizTalk Server to work as intended, the following options are turned off in the BizTalkMsgBoxDb database:
 * auto create statistics
 * auto update statistics

Additionally, the Max Degree of Parallelism setting is set to 1 in the SQL Server 2000 or SQL Server 2005 server instance that hosts the BizTalkMsgBoxDB database.

