| title | description | ms.custom | ms.date | ms.service | ms.reviewer | ms.suite | ms.topic |
|---|---|---|---|---|---|---|---|
SQL Server settings not to change |
Max Degree of Parallelism, Auto create statistics Auto Update statistics, and rebuilding indexes in BizTalk Server |
06/08/2017 |
biztalk-server |
article |
When setting up [!INCLUDEbtsSQLServerNoVersion] during the operational readiness procedures for [!INCLUDEbtsBizTalkServerNoVersion], you should not make changes to the following settings.
Max Degree of Parallelism (MDOP) is set to “1” during the configuration of [!INCLUDEbtsBizTalkServerNoVersion] for the [!INCLUDEbtsSQLServerNoVersion] instance(s) that host the [!INCLUDEbtsBizTalkServerNoVersion] MessageBox database(s). This is a [!INCLUDEbtsSQLServerNoVersion] 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 [!INCLUDEbtsBizTalkServerNoVersion] stored procedures and performance. If changing the parallelism setting for an instance of [!INCLUDEbtsSQLServerNoVersion] will have an adverse effect on other database applications that are being executed on the [!INCLUDEbtsSQLServerNoVersion] instance, you should create a separate instance of [!INCLUDEbtsSQLServerNoVersion] dedicated to hosting the [!INCLUDEbtsBizTalkServerNoVersion] 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 [!INCLUDEbtsBizTalkServerNoVersion] queries.
The [!INCLUDEbtsBizTalkServerNoVersion] 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.
The following options are turned off by default in the [!INCLUDEbtsBizTalkServerNoVersion] 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 [!INCLUDEbtsBizTalkServerNoVersion] 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 [!INCLUDEbtsBizTalkServerNoVersion] queries in [!INCLUDEbtsSQLServerNoVersion]. 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 articles:
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 What you can and can't do with the MessageBox Database server.
[!INCLUDEbtsBizTalkServerNoVersion] 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 [!INCLUDEbtsBizTalkServerNoVersion]. [!INCLUDEbtsBizTalkServerNoVersion] does support database index rebuilds (“DBCC DBREINDEX” and “ALTER INDEX … REBUILD …”), but they should only be done during maintenance windows when [!INCLUDEbtsBizTalkServerNoVersion] is not processing data. Index rebuilds while [!INCLUDEbtsBizTalkServerNoVersion] is processing data are not supported.
For more information, go to Blocking, deadlock conditions, or other SQL Server issues when you connect to the BizTalkMsgBoxDb database in BizTalk Server.
Index fragmentation is not as much of a performance issue for [!INCLUDEbtsBizTalkServerNoVersion] as it would be for a DSS system or an OLTP system that performs index scans. [!INCLUDEbtsBizTalkServerNoVersion] does very selective queries and updates and [!INCLUDEbtsBizTalkServerNoVersion] stored procedures should not cause table or index scans.