SQL Server Settings That Should Not Be Changed
When setting up SQL Server during the operational readiness procedures for BizTalk Server, you should not make changes to the following settings.
Max Degree of Parallelism (MDOP) is set to “1” during the configuration of BizTalk Server for the SQL Server instance(s) that host the BizTalk Server MessageBox database(s). This is a SQL Server instance-level setting. This setting should not be changed from the value of “1”. Changing this to anything other than “1” can have a significant negative impact on the BizTalk Server stored procedures and performance. 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 SQL Server instance, you should create a separate instance of SQL Server dedicated to hosting the BizTalk Server databases.
Parallel queries are generally best suited to batch processing and decision support workloads. They are typically not desirable in a transaction processing environment where you have many short, fast queries running in parallel. In addition, changing the MDOP setting sometimes causes the query plan to be changed, which leads to poor query performance or even deadlocks with the BizTalk Server queries.
The BizTalk Server stored procedures provide the correct joins and lock hints wherever possible in order to try to keep the query optimizer from doing much work and changing the plan. These stored procedures provide consistent query executions by constructing the queries such that the query optimizer is taken out of the picture as much as possible.
For more information, see Microsoft Knowledge Base article 899000, "The Parallelism setting for the instance of SQL Server when you configure BizTalk Server" at http://go.microsoft.com/fwlink/?LinkId=153432.
The following options are turned off by default in the BizTalk Server MessageBox database when it is created:
Auto create statistics
Auto update statistics
Do not enable these options on MessageBox databases. Enabling the "auto create statistics" and "auto update statistics" options can cause undesirable query execution delays, especially in a high-load environment.
In addition, the BizTalk Server stored procedures have exact joins and lock hints specified on the queries. This is done to ensure that the optimal query plan is used by the BizTalk Server queries in SQL Server. The distributions and expected results for the queries are known; the approximate number of rows returned is known. Statistics are generally not needed.
For more information, see the following Microsoft Knowledge Base articles:
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" at http://go.microsoft.com/fwlink/?LinkId=153430.
917845—"You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server" at http://go.microsoft.com/fwlink/?LinkId=153429.
The MessageBox database should be treated like non-Microsoft application source code. That is, you should not “tweak” the MessageBox database via changes to tables, indexes, stored procedures, and most SQL Server database settings. For more information, in the BizTalk Core Engine's WebLog, see the entry "What you can and can't do with the MessageBox Database server" at http://go.microsoft.com/fwlink/?LinkId=101577.
BizTalk Server does not support defragmenting indexes. “DBCC INDEXDEFRAG” and “ALTER INDEX … REORGANIZE …” are not supported since they use page locking, which can cause blocking and deadlocks with BizTalk Server. BizTalk Server does support database index rebuilds (“DBCC DBREINDEX” and “ALTER INDEX … REBUILD …”), but they should only be done during maintenance windows when BizTalk Server is not processing data. Index rebuilds while BizTalk Server is processing data are not supported.
For more information, see Microsoft Knowledge Base article 917845 " You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server" at http://go.microsoft.com/fwlink/?LinkId=153429.
Index fragmentation is not as much of a performance issue for BizTalk Server as it would be for a DSS system or an OLTP system that performs index scans. BizTalk Server does very selective queries and updates and BizTalk Server stored procedures should not cause table or index scans.
For more information about index fragmentation and workload types, see "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at http://go.microsoft.com/fwlink/?LinkId=101580. A quote from the article:
“As shown in Figure 1, there is little difference between the performance of the stored procedures before and after defragmenting. Because the underlying queries issued by these stored procedures acted upon very selective portions of the data, workload performance was not adversely affected by fragmented indexes.”
|The contents of the article also apply to SQL Server 2008 and SQL Server 2005.|