Dynamics CRM 4.0 SQL Dead Locks

I have to admit Dynamics CRM is slightly addictive. A web based application that integrates with Outlook, can challenge you at many different levels, authentication(kerberos), IIS, AD, SQL, reporting services, E-mail relays, Exchange integration, development and customization, it uses all Microsoft main platforms to create this powerful and flexible application. And is this interesting integration with a variety of systems that makes me constantly busy in search for more knowledge.

I want to share a few registry keys and SQL tips to improve the SQL dead locks that can occur on enterprise role based environments.

Lets consider the following environment:

Platform01 & Platform02 work on a Active/Active set up, there is no out of the box Active/Passive option unless you configure a Windows Cluster. Both Asynchronous servers constantly read the CRM database for pending jobs to process. As you can imagine having two servers actively looking for jobs to process will certainly at same point both servers will try to process the same jobs and this is one of the main reasons deadlocks occur on this type of environments.

CRM Tweaks

Registry changes: Configure the following registry key on the Asynchronous servers in order to improve performance and avoid dead locks.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM

Name: AsyncDBAppLock          
Type: DWORD
Value: 1

For more information on the above registry key refer to the following KB:
http://support.microsoft.com/kb/2249156

Database changes: On the MSCRM_CONFIG database, DeploymentProperties table you can find a few fields starting with Async, these fields are used to control the Asynchronous service behaviour tweaking it to your exact needs. Important: changes to these fields should be done always on a dev/test environment before applying it to production and always backup your database or table before changing it.

We will not cover all the available keys here, the idea with the below changes are intended to reduce the amount of records the async services process, lowering the probability of accessing the same records and causing deadlocks.
--

update DeploymentProperties set IntColumn = 200 where columnname = 'AsyncItemsInMemoryHigh';
update DeploymentProperties set IntColumn = 100 where columnname = 'AsyncItemsInMemoryLow';

 --
The default values are 1000 and 2000 we lowering to 200 and 100. keep an eye on the backlogging, if you notice jobs building up, you can increase this slightly or play with the below query to increase the Select Interval, again these values should be customized based on our environment workload:

--

update DeploymentProperties set IntColumn = 1 where columnname = 'AsyncSelectInterval'

--


"AsyncItemsInMemoryHigh – Max number of async operations the service will store in memory. Upon selection interval, if the number of items in memory falls below AsyncItemsInMemoryLow, the service will pick enough to reach up to AsyncItemsInMemoryHigh again.
AsyncItemsInMemoryLow – Minimum number of async operation the service needs to have in memory before loading new jobs into memory. Upon selection interval, if the number of items in memory falls below this value, the service will pick up enough to reach AsyncItemsInMemoryHigh again."

Performance and Scalability Assessment of Customer Implementation
http://www.microsoft.com/downloads/details.aspx?FamilyID=e76d8916-81a6-4330-90ae-b24f8263fff8&DisplayLang=en


SQL Server Tweaks
On the SQL side there are a couple of tweaks I want to mention to improve performance and avoid dead locks. I would start with enabling read committed snapshot isolation. Important: Enabling this option can impact on the SQL server performance, make sure you monitor performance closely after changes made.

Enable snapshot isolation on both:

• MSCRM_CONFIG
• ORG_MSCRM

MSCRM4 Optimizing and Maintaining. [page. 33]
"use READ COMMITTED isolation to minimize potential locking contention while protecting transactions from “dirty” reads of uncommitted data modifications. With dirty reads, data within the current transaction can potentially be modified by other transactions between individual statements, resulting in non-repeatable reads or phantom data"


The second is setting Max Degree of Parallelism to: 1

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

MSCRM4 Optimizing and Maintaining. [page. 34]
"Setting this value to 1 will suppress parallel plan generation, while setting the value to a number greater than 1 (up to a maximum of 64) will restrict the maximum number of processors used by a single query execution"

Hope this gives you a hand.