Dynamics Community Forum Thread Details

Hello,

We found out that simple fetch xml query bellow was locking the database. The fetchxml bellow was found in the SQL as the head blocking process 4 times in 1 hour. Blocks were ranging from 2 mins, 28 mins, 38 mins and up to 57 mins.

Nothing seems unusual in the Execution plan of the query, looks normal, very little data is passed.

Calling this from a C# plugin which is reading little data, one or two rows of data, nothing special, just creates one record in the CRM 8.2 on prem.

Plugin runs only once, is synchronous and is registered on preOperation.

Has anyone else experienced such scenario and solved it?

Answers will be highly appreciated

<fetch output-format='xml-platform' mapping='logical' distinct='false'>
                                  <entity name='entity1'>
                                    <attribute name='attribute1' />
                                    <attribute name='attribute2' />
                                    <order attribute='attribute1' descending='false' />
                                    <filter type='and'>
                                      <filter type='or'>
                                        <condition attribute='attribute1' operator='eq' value='someValue' />
                                        <condition attribute='attribute1' operator='eq' value='someValue' />
                                      </filter>
                                    </filter>
                                    <link-entity name='entity2' from='entity2id' to='entity2id' alias='ent'>
                                      <filter type='and'>
                                        <condition attribute='attribute3' operator='eq' value='{id}' />
                                      </filter>
                                    </link-entity>
                                  </entity>
                                </fetch>

Categories:

Microsoft Dynamics CRM

Hello crm 8.2 developer,

This is a challenging situation, especially with a seemingly simple FetchXML query causing significant database locking. Let's break down the potential causes and troubleshooting steps, considering the context of your CRM 8.2 on-prem environment:

Understanding the Problem:

Blocking: The fact that this query is identified as a head blocking process means it's holding locks on resources that other processes need, causing them to wait.
Duration: The long durations of the blocks (28-57 minutes) are critical. This indicates a severe performance bottleneck.
Simplicity: The query itself appears straightforward, suggesting the issue might be related to underlying data, indexes, or SQL Server configuration.
PreOperation Plugin: Running synchronously in pre-operation means the lock is held before the main transaction is committed.

Potential Causes and Troubleshooting:

Index Fragmentation/Missing Indexes:
- Even with small data volumes, fragmented or missing indexes on entity1 or entity2 can lead to inefficient query execution and prolonged lock durations.
- Action:
  - Analyze the execution plan in SQL Server Management Studio (SSMS) closely. Look for table scans, index scans with high logical reads, and missing index recommendations.
  - Rebuild or reorganize indexes on entity1 and entity2, especially attribute1, attribute2, and attribute3.
  - Ensure appropriate indexes exist for the filter conditions.
Parameter Sniffing Issues:
- SQL Server might be caching an execution plan based on specific parameter values (someValue, {id}). If those values result in a poor execution plan for other values, it can lead to performance problems.
- Action:
  - Try adding OPTION (RECOMPILE) to the FetchXML query (if possible through your plugin). This forces SQL Server to generate a new execution plan each time.
  - If possible, test the query in SSMS with different values for the filter conditions to see if performance varies significantly.
  - If the issue is parameter sniffing, you may need to adjust the query or SQL Server configuration.
Lock Escalation:
- SQL Server might escalate row-level locks to page or table-level locks if a large number of rows are being accessed. This can lead to blocking.
- Action:
  - Check the lock escalation settings for the relevant tables.
  - Consider optimizing the query to reduce the number of rows accessed.
Transaction Isolation Levels:
- The transaction isolation level can affect how locks are acquired and released.
- Action:
  - Verify the transaction isolation level used by the plugin and the CRM application.
  - Consider using a less restrictive isolation level if appropriate.
CRM Platform Issues:
- While less likely, there might be underlying issues with the CRM 8.2 platform itself.
- Action:
  - Check for any known issues or hotfixes related to FetchXML queries or database locking in CRM 8.2.
  - Ensure your CRM environment is up to date with the latest patches.
SQL Server Resource Contention:
- If the SQL Server is under heavy load, resource contention (e.g., CPU, memory, I/O) can lead to performance problems and blocking.
- Action:
  - Monitor SQL Server performance using Performance Monitor or other tools.
  - Identify any resource bottlenecks and take appropriate action.
Link Entity Performance:
- Even though it is a simple link entity, if there is a large amount of data in entity2, and there is no proper indexing, this could be the source of the problem.
- Action:
  - Verify the indexes in entity2.
  - Verify the amount of data in entity2.

Debugging Steps:

Enable SQL Server Profiler:
- Capture a trace of the SQL Server activity during the blocking period. This will provide detailed information about the locks being held and the queries being executed.
Use SSMS Activity Monitor:
- Monitor the active processes and locks in SSMS to identify the blocking queries.
Review CRM Trace Logs:
- Enable tracing in CRM to capture detailed information about the plugin execution.
Isolate the Plugin:
- Disable other plugins and customizations to rule out any conflicts.
Test the FetchXML Independently:
- Execute the FetchXML query directly in SSMS to see if it causes blocking outside of the plugin context.

Key Considerations:

On-Prem Environment: On-prem environments often have unique configurations and challenges.
CRM 8.2: This version is older, so there might be limitations or known issues.
Plugin Context: The plugin's execution context can influence database behavior.

By systematically investigating these potential causes and using the debugging steps, you should be able to pinpoint the root cause of the database locking issue.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more.
If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Best Regards,
Daivat Vartak

Community site session details

CRM 8.2 on prem, simple fetch xml query locks database?

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

Featured topics

Product updates