SQL Server Setttings to NEVER Change for BizTalk Applications

Just finished up a week of teaching a deep BizTalk 2006 course for local customers, and, taking a pleasant week-long vacation. In doing so,
I've queued up a fat batch of topics to cover over the coming months.

Here, I wanted to quickly point out a couple of SQL Server settings that you should NEVER change, even if one of our own SQL guys
tells you to. First, of all we purposely set the Parallelism setting of the SQL Server instance to "1" during BizTalk
configuration. Parallelism is the ability for SQL to take advantage of more than 1 processor while executing a T-SQL statement. See
this KB article for why we DON'T want this value changed
In the same vein, we also turn off auto create statistics and auto update statistics for a specific reason, some of which you
can read about in this KB article.

Basically, our brains in Redmond have hard-coded all the various join and lock hints in our databases to ensure that BizTalk behaves
consistently and doesn't have to rely on SQL Server trying to figure out the best query plan. In fact, allowing SQL Server to guesstimate
a query plan will result in significantly worse performance. You can read more about how SQL Server uses statistics
here. For parallelism, our BizTalk queries simply
gain no benefit, and are actually harmed, when parallelism is turned on. Read a bit
here for a good read on some side effects of parallelism.

Moral of story, even if well-intentioned SQL expert tells you to (a) change the parallelism setting back to 0 or (b) wants to flip
Enable Auto Update Statistics or Auto Create Statistics on for the databases, give them a firm "no" and potentially use the threat of
violence. Another reason to use a separate SQL Server instance for BizTalk databases if you're forced to share the SQL Server box with
other applications.

Final note, I'm taking the BizTalk 2006 beta exam tomorrow, and I'll post my summary here in the afternoon.

Technorati Tags: BizTalk