Resolving Queue Data Visibility Issues and Deadlocks in UiPath Insights

News Knowledge Base
, , , , , , , , , , , , , ,
1

Why are you unable to see recent data from Queues in the Insights, though the database table [dbo].[QueueItems] shows the latest data? What could be the reasons for the latest data for Queues being from March 7, while there's no issue with the Robot Logs and Jobs data? Who can assist in resolving this issue and make sure that the most recent data from Queues appears in the Insights? When did this issue first become apparent, and when do you expect the resolution?

Issue Description

There were data visibility issues in UiPath Insights, where recent queue data was not being displayed, despite the database table containing the latest information.

Users may encounter the following error message:

'Transaction (Process ID 237) was deadlocked on generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction.'

This error is typically associated with a deadlock in the database, often involving the following query:

SELECT COALESCE((SELECT MAX(Id) FROM dbo.JobEvents), 0) - COALESCE((SELECT MAX(Id) FROM \[read\].JobEvents), 0)

Prerequisites:

  • All minimum requirements for Insights are met.
  • The read_committed_snapshot setting in the Insights database is enabled.
  • Affected versions : 22.10.7, 23.4.4, 23.10.2, 22.10.8
  • Fix versions: 24.10.0 Insights

Resolution

Understanding and Implementing MAXDOP

  1. What is MAXDOP?

MAXDOP (Maximum Degree of Parallelism) is a SQL Server configuration option that controls the number of processors used for the execution of a single query in a parallel plan. It determines how many parallel operations can be performed for a query.

  1. How MAXDOP 1 Can Help with Deadlocks

Setting MAXDOP to 1 forces SQL Server to use a single processor for query execution, which can help reduce deadlocks in certain scenarios:

    1. Simplified Execution Plans: MAXDOP 1 creates simpler, serial execution plans, reducing the likelihood of resource conflicts.
    2. Reduced Resource Contention: By limiting parallel operations, it minimizes the chances of multiple processes competing for the same resources.
    3. Predictable Query Behavior: Serial execution can lead to more consistent and predictable query performance, especially for smaller datasets.

  1. Effects of Setting MAXDOP to 1

While MAXDOP 1 can help with deadlocks, it's important to consider its effects:

    1. Performance Impact: For large, complex queries, limiting parallelism may increase execution time.
    2. Resource Utilization: It may lead to underutilization of available CPU resources on multi-core systems.
    3. Workload-Dependent: The impact varies based on the nature of your workload and database size.
    4. Implementing MAXDOP in UiPath Insights

To implement MAXDOP in UiPath Insights, you need to modify the orchestrator.dll.config file. Follow these steps :

  1. Locate the orchestrator.dll.config file in your UiPath Orchestrator installation directory.
  2. Open the file in a text editor with administrator privileges.
  3. Add the following lines within the <appSettings> section: 
    <appSettings> ...

    <add key="Insights.ModuleEnabled" value="true" />
    

    <add key="Insights.IngestionMarker.MAXDOP" value="1" />
    

    …
    

       &lt;/appSettings&gt;</code></pre>
    1. Save the file and restart the UiPath Orchestrator service for the changes to take effect.

    This configuration sets the MAXDOP value to 1 for the Insights ingestion process, which can help mitigate deadlock issues.

    Important: Ensure that you maintain the existing configuration settings in the orchestrator.dll.config file. Only add the new lines as shown above.

    Guidelines and Recommendations

    Based on Microsoft's recommendations: Server configuration: max degree of parallelism: Recommendations

    1. Start Conservative: Begin with a higher MAXDOP value and gradually reduce it if deadlocks persist.
    2. Consider Hybrid Approach: Use different MAXDOP settings for different workloads or queries.
    3. Monitor Performance: Regularly assess the impact of MAXDOP changes on overall system performance.
    4. Hardware-Specific Tuning: Adjust MAXDOP based on your server's CPU architecture and core count.
    5. Regular Review: Periodically review and adjust MAXDOP settings as your workload evolves.
    6. Implementation Steps
    7. Identify the specific queries causing deadlocks.
    8. Test the impact of MAXDOP 1 on a non-production environment.
    9. Apply the MAXDOP setting in the UiPath.orchestrator.dll.config file as shown above.
    10. Monitor the system closely after implementation for any performance changes.
    11. Additional Considerations
      • Ensure proper indexing on frequently queried columns.
      • Review and optimize the database schema if necessary.
      • Consider using query hints or plan guides for fine-grained control over parallelism.

    By following these guidelines and understanding the implications of MAXDOP settings, you can effectively address deadlock issues while maintaining optimal performance for your Insights database.