How to Resolve SQL Locking issues in Microsoft Dynamics CRM

How to Resolve SQL Locking issues in Microsoft Dynamics CRM

SQL Server Blocking refers to the situation where one connection blocks another, even though they come from the same application or user is called blocking. In case of Microsoft Dynamics CRM, It is not very uncommon that a customer is having deadlocking issues with their Microsoft Dynamics CRM On-premises solution. Most of the times the deadlocks are caused due to the following reasons:

• Missing Indexes
• Out of date statics – Use Update Stats
• Insufficient hardware and lack of capacity planning.
• Incorrect Server Configuration
• Bad design decisions
• Unnecessarily excessive use of plugins and workflows.

Every live connection in SQL Server will have a logged-on session with the database and will have an associated Session ID a.k.a. (SPID). For Each SPIDs will have a reference to as a process which consists of the SQL server resources and the data structures necessary to service a query at the request of a single connection from a given client. Client applications can spawn one or more connections.

Questions to ask before going ahead with the Troubleshooting:

• Did the CRM run in the past without locking problems? If Yes, then are there any recent software or hardware changes?
• Did the lock timeouts or deadlocks started recently?
• What version and Isolation level of the DBMS are you running?
• Does the problem only occur at certain times? Peak V/S Non-Peak period.
• If there was a change recently, what has changed (e.g., users count, number of applications, database maintenance or Service pack update, changes to any other relevant software, etc.)

As you can see from the above example, SQL Server sees all these connections separately and it does not matter whether they come from a single client application on a single client computer and multiple connections from multiple client applications or multiple client computers. If SQL Server is not configured correctly, or due to inefficient application code, or due to high transnational systems, One client connection can block another connection, regardless of whether they come from the same application or separate applications on two different client computers or the sample client computer with different applications.

Steps to Perform in order to resolve the issues:

• Set the MAXDOP setting to 1 from a default value of ‘0’

Changing the MAXDOP setting to 1 ensures that Query will not go for “Parallelism” and should reduce the locking issues a bit.

• If you are using the E-Mail Router heavily, you can create the following Index’s to see if this improved the performance:

USE <OrganizationName>_MSCRM
GO
CREATE NONCLUSTERED INDEX [CRM_ActivityPartyBase_EmailRouter1] ON [dbo].[ActivityPartyBase]
(
[ActivityId] ASC,
[ParticipationTypeMask] ASC,
[PartyId] ASC
)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CRM_ActivityPartyBase_EmailRouter2] ON [dbo].[ActivityPartyBase]
(
[ParticipationTypeMask] ASC,
[PartyId] ASC
)
INCLUDE ( [ActivityId],
[ActivityPartyId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CRM_ActivityPartyBase_EmailRouter1] ON [dbo].[ActivityPointerBase]
(
[ActivityTypeCode] ASC,
[statecode] ASC,
[actualend] asc,
[statuscode] asc
)INCLUDE ([activityid], [modifiedon], [deletionstatecode])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CRM_ActivityPointerBase_EmailRouter2] ON [dbo].[ActivityPointerBase]
(
[statecode] ASC,
[ActivityTypeCode] ASC,
[deletionstatecode] asc
)INCLUDE ([ActualEnd], [StatusCode], [ActivityID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CRM_EmailBase_EmailRouter1] ON [dbo].[EmailBase]
(
[activityid] asc,
[deliveryattempts] asc,
[DirectionCode] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

Ensure that the Log-File size is not very large. Large Log Files can cause “Lock Escalations”

Storage Planning for TempDB

• Set the recovery model of TempDB to SIMPLE. This model automatically reclaims log space to keep space requirements small.
• Do not allow for TempDB files to automatically grow. This reduces the CPU overhead of managing a dynamic file growth.
• Have multiple data file for TempDB (Total Number of TempDB Primary Data File = Number of Processors available to SQL)
• Each data file should be of equal size.
• Try to keep each data files in separate disk drives for IO Parallelism.
• TempDB Data and Log files should be kept in faster disk drive (Preferably RAID 1 if possible)
• Use RAID-10 or SSD Disks.
• Pre-size TempDB files
• 25% of largest DB size.
• Set Auto Growth to fixed size < 200 MB
• You should have the same number of data files as the number of CPUs up to a maximum of 8.
• Change the Index fill factor to 80 on the Advanced Menu of SQL Server Instance Level properties.”
• Change the isolation level to either Read committed or Read committed Using Row Versioning

They way SQL Server works, Blocking is inevitable and an unavoidable characteristic of any relational database management system (RDBMS) where the concurrency model is lock-based. Simply put, a blocking occurs when one SPID holds a lock on a specific resource in SQL Server and a second SPID attempts to acquire a conflicting lock type on the same resource. Now locking happens continuously on SQL Server and the time frames are small for which the first SPID locks the resource. When it releases the lock, the second connection is free to acquire its own unique lock on the resource and continue with query execution and processing. While this is an acceptable normal behavior and happens many times throughout the day with no noticeable effect on system performance, it is when the time taken is much higher and repeatable, it can be a cause for concern.