We will understanding the top common database transaction constraints in Microsoft Dataverse. Before we start, make sure to subscribe to CRM Crate so that you can stay up to date in the field of Power Platform.
Dataverse provides a secure platform for storing and managing data utilized by business applications. The data is organized within Dataverse into tables or entities, each comprising rows (formerly known as records) and columns (previously referred to as fields/attributes). Each column is tailored to accommodate specific types of data, such as name, age, date, and more. While Dataverse comes with a standard set of tables to address common scenarios, users have the flexibility to create custom tables tailored to their organization’s needs and populate them with data using Power Query. Subsequently, app creators can leverage Power Apps to develop robust applications that harness this data.
Benefits of Dataverse
Access your Dynamics 365 Data – Data originating from your Dynamics 365 applications is stored in Dataverse, enabling the swift development of applications that leverage your Dynamics 365 data. Additionally, you can enhance your applications by incorporating Power Apps functionalities.
Productivity tools – The inclusion of tables in Microsoft Excel add-ins enhances productivity and ensures convenient access to data.
Easy to manage – Both the metadata and data are stored in the cloud, alleviating any concerns about the specifics of their storage.
Easy to secure – Data is securely stored so that users can see it only if you grant them access. Role-based security allows you to control access to tables for different users within your organization.
Rich metadata – Data types and relationships are used directly within Power Apps.
Logic and validation – Define calculated columns, business rules, workflows, and business process flows to ensure data quality and drive business processes.
Scalable customization issues in Dataverse
Microsoft Dataverse safeguards itself and its users against extended operations that could impact the response times for the requesting user, as well as the stability and responsiveness of the system for others.
Some individuals implementing Dataverse solutions encounter challenges when the platform or the underlying Microsoft SQL Server database generates errors as a result of the implemented protective measures. These errors are frequently misinterpreted as indications of the platform’s inability to scale or as incorrect termination or throttling of requests to the system. This article describe how the platform protects itself from the impact of these requests imposed on the system and explains why this behavior is most often the result of custom implementations not understanding the impact on blocking and transaction usage within the platform.
Common symptoms for database transaction errors in Dataverse-
The process of examining and resolving performance related issues in Microsoft Dataverse usually begins when specific errors and symptoms appears in the system. These errors and symptom are often perceived to be problems in the platform and the necessary remedial step is to loosen up the platform constraints that typically trigger a slow running request to become a reported error.
Issues of this type typically consist a set of common symptoms, as shown in the table below.
| Symptom | Description |
|---|---|
| Slow requests | Users see slow response times for the system in particular areas, for example, certain forms and queries |
| Generic SQL errors | Certain actions respond with a platform error reporting a Generic SQL Error. This error often translates at a platform layer to a SQL timeout. |
| Deadlocks | Platform errors reporting that a deadlock has occurred, which has forced the action to be terminated and rolled back. |
| Limited throughput | Particularly in batch load scenarios, this often exhibits in slow throughput being achieved, slower than should be possible. |
| Intermittent errors / slow performance | An important indicator of these behaviors is where the same action can be fast or incredibly slow, and retrying it works much more quickly or avoids an error. |
Typical symptoms often arise from underlying causes that result in specific requests running sluggishly, subsequently triggering platform constraints. The diagram below depicts common symptoms along with some of the prevalent root causes associated with them.
What are the top common database transaction constraints in Dataverse?
The Dataverse platform intentionally sets numerous constraints to mitigate the potential adverse effects of any single action on the overall system and, consequently, on users. Although this behavior can be tiresome as it may block or cancel certain requests and often prompts questions around whether the constraints can be lifted, this is rarely a good approach when you consider the broader implications.
Below are the different constraints which Dataverse can intentionally use to mitigate any adverse effect on system.
Plug-in timeouts
1. Plug-ins will time-out after 2 minutes.
2. Do not perform long running operations in Dataverse plug-ins. Protects the platform and the sandbox service and ultimately the user from poor user experience
Workflow limits
1. No specific hard limits, but balance resource across organizations.
2. Where demand is low an organization can take full advantage of available capacity. Where demand is high, resources and throughput are shared.
3. Operates under a Fair Usage policy.
SQL timeouts
1. Provides protection within a particular organization and its private database.
2. Also provides protection at a database server level against excessive use of shared resources such as processors/memory.
3. Requests to SQL Server usually time-out at 120 seconds, although in some cases the command timeout is longer.
4. Protects against long running requests.
Maximum concurrent connections
1. With multiple web servers, each with 100 concurrent connections to the database of typical < 10 ms, this suggests a throughput of > 10k database requests for each web server. This shouldn’t be required and would hit other challenges well before that
2. There’s a platform default setting of a maximum connection pool limit of 100 connections from the Web Server connection pool in IIS to the database. Dataverse doesn’t change this value.
3. If you hit this, it’s an indication of an error in the system; look at why so many connections are blocking.
ExecuteMultiple
1. The processing of large groups of these requests can tie up vital resources in Dataverse at the expense of more response critical requests by users.
2. TheExecuteMultiplemessage is designed to assist with collections of operations being sent to Dataverse from an external source.
Service Protection Limits
1. To ensure consistent availability and performance for everyone we apply some limits to how APIs are used. These limits are designed to detect when client applications are making extraordinary demands on server resources.
[…] of time a plug-in can run before the platform forcefully ends its execution & cancels the database transaction. If a plug-in goes beyond this limit, it gets terminated, which could result in incomplete […]