Enterprise TechSQLHow to Resolve SQL Locking issues in Microsoft Dynamics CRM May 25, 20169157 views0Share By IG ShareHow to Resolve SQL Locking issues in Microsoft Dynamics CRMSQL 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:Note: If you buy something from our links, we might earn a commission. See our disclosure statement.Missing IndexesOut of date statics – Use Update StatsInsufficient hardware and lack of capacity planning.Incorrect Server ConfigurationBad design decisionsUnnecessarily 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] GOEnsure that the Log-File size is not very large. Large Log Files can cause “Lock Escalations”Storage Planning for TempDBSet 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 files25% of largest DB size.Set Auto Growth to fixed size < 200 MBYou 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 VersioningThey 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. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise TechList of the Best HPTX Cases – Huge PC Cases for Large BuildsThe computer cases have been one of the essential aspects when it comes to enjoying ...
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise TechWhat Is A DataCenter Power Distribution Unit (pdu) Server Rack CabinetsNo matter whether you call it a server closet or server room or a cabinet, ...
Storage SystemsWD SE vs WD Red Pro Specifications Comparison – Enterprise Class NASThe HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...