wiki

Codit Wiki

Loading information... Please wait.

Codit Blog

SQL Server 2016: MAX DOP at database level.

 

Posted on Tuesday, November 28, 2017 7:45 AM

Toon Vanhoutte by Toon Vanhoutte

Very recently, I discovered a new feature in SQL Server 2016. It allows you to configure the Max Degree of Parallelism (MAXDOP) on a database level, instead of instance level. This is very important to take into account for BizTalk installations. The BizTalk MessageBox database performs at it best when MAXDOP is set to 1.

Quoting the BizTalk documentation:

Max Degree of Parallelism 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 this 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.

Thanks to this new feature in SQL Server 2016, we can have the BizTalk MessageBox running with MAXDOP set to 1 on the same instance with databases that have MAXDOP set to 0. Unfortunately, the BizTalk configuration still sets the MAX DOP value to 1 at instance level. Please vote for this UserVoice item, if you agree this should be changed to the MessageBox database level!

This gives us one reason less to install the BizTalk MessageBox database on another instance than the rest of the BizTalk databases. One argument to keep this strategy of two instances, is the fact that you can perform better memory allocation and CPU affinity on a SQL instance level.

Thanks to my co, Pieter Vandenheede, for his assistance on this one!
Toon

Categories: BizTalk
written by: Toon Vanhoutte