Max DOP on SQL (or another reason to have a dedicated SQL for BizTalk)

Often we find ourselves at a customer or project explaining why it is better to have a dedicated SQL server or cluster for your BizTalk platform.

One can easily come up with the most obvious reason as to why this is important:

BizTalk is designed and optimized for parallel processing and throughput and the BizTalk databases are resource intensive: it is the heart of your BizTalk environment.
If you are sharing a SQL server between BizTalk and any of your other applications, you are pulling away those resources from BizTalk. This is in regard to memory, threads, CPU cycles, etc…

However I find that customers are hard to persuade with that argument and I cannot blame them:

  • If they look at the resource usage of their SQL server that is currently hosting their BizTalk platform, it is hardly doing anything. Memory usage is high off course (it is quite normal behavior for SQL to take up all memory for caching purposes), but the load on CPU and disk is mostly quite low.
  • They have other applications (either old or new) that need a SQL server environment. Most of the times the SQL database they need hosting is nothing more then a few dozen MB, so the tendency is there to say: what harm can it bring to host it on the BizTalk SQL?
  • They paid good money for the SQL server license(s) and BizTalk license(s) they are running their BizTalk platform on. They do not want to ask their manager for extra money again (cost of hardware/storage/licenses) because of budget limits or any other reason for that matter.

So in the end, what often happens at some projects is that the SQL server or SQL cluster hosting your BizTalk platform becomes a shared server or cluster hosting the BizTalk environment as well as several other databases.

WHY IS A SHARED SQL A BAD THING?

Like I mentioned above, any resources you’re taking away from your SQL server, you are effectively taking away from your BizTalk platform.

Additionally, there is something on SQL called the “Max DOP” parameter or the parameter for the “maximum Degree Of Parallelism”. Learn more about max DOP on MSDN.

In short, it is the number of processors used by SQL server on a SQL Server instance to execute queries in parallel to each other in order to give the results as fast as possible.
If your Max DOP parameter is set to something higher than 1, SQL will try to process queries in parts by spreading them over a number of processors. All done by the SQL engine without you having to care about it much.

The reason why I talk about this here is that BizTalk, during configuration, will actually set this max DOP parameter to one (1) on the SQL server instance where your Message Box database is located. This is due to the fact that the BizTalk Message Box database is highly optimized and works quite differently from other “normal” databases. Other databases are just data stores where SQL needs to retrieve data from and store data to. BizTalk is built quite different in many ways while it stays in essence a data store off course. The BizTalk databases are that optimized that setting Max DOP to something higher than one (1), it will actually hurt BizTalk throughput and performance.

Having said this, your “other” non-BizTalk databases will actually perform worse in 99,9% of the cases having Max DOP set to 1.

So there it is, another reason to have a dedicated SQL server/cluster for your BizTalk platform.

Do you know any other reasons why sharing your BizTalk SQL environment is a bad idea? Feel free to put them in the comments.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s