Software Engineer
Subscribe to the newsletter
In Microsoft Dynamics 365 Finance and Operations, users occasionally encounter errors while loading forms, which can disrupt daily workflows and affect productivity. One common issue you may have come across appears in the form of a warning message:
“The total size of the records in your joined Select statement is 224230 bytes, but Microsoft Dynamics 365 for Finance and Operations is by default performance-tuned not to exceed 196608 bytes.”
If you have encountered the record size limit error in Dynamics 365 Finance and Operations and are unsure how to proceed, this guide will walk you through understanding the issue and provide step-by-step solutions to resolve the joined record size limitation.
What does the joined record size limitation error mean?
This error message indicates that the system’s joined record size limit has been exceeded. In simple terms, the system restricts the maximum amount of data that can be joined and retrieved in one query. This limitation is designed as a performance safeguard, as handling large data sets can impact overall performance. However, there are scenarios where this default buffer limit can pose a barrier to accessing the necessary data.
Understanding the root cause of this form error?
The primary cause of this error lies in the global buffer size setting for forms within Dynamics 365 Finance and Operations. This buffer acts as a memory allocation that temporarily holds data when joining records. By default, the buffer space is capped at 196608 bytes (about 196.08 KB), which is generally sufficient for most standard operations. However, if your data volume exceeds this limit, the system will prevent form loading to avoid performance degradation.
In this case, the required buffer size for the selected data was 224230 bytes, exceeding the system’s default configuration. Therefore, an adjustment is necessary to increase the buffer size to match the needs of your data retrieval, allowing forms to load without issue.
Let’s explain this through screenshots:
The defined buffer size for default is 196608 bytes and its equal to 196.08 Kilobyte.
The required buffer size for selected data is 224230 bytes.
The fix: Adjusting the buffer size for optimal performance
To overcome this limitation, you can increase the buffer size within the system configuration. However, this change should initially be tested in a development environment to ensure it resolves the issue without causing other side effects. Follow these steps:
Step 1: Access the development environment
Start by implementing the fix in your Dev VM (development virtual machine) environment. Testing changes here allows you to confirm functionality and assess any impact on system performance.
Step 2: Run the buffer size adjustment script
The script below modifies the SYSGLOBALCONFIGURATION table, setting the buffer to a higher value. The script adjusts the VOLATILE_SQLTRANSFERBUFFERKBYTES setting, which controls the transfer buffer for SQL operations.
UPDATE SYSGLOBALCONFIGURATION
SET VALUE = 220
WHERE NAME = ‘VOLATILE_SQLTRANSFERBUFFERKBYTES’;
In this example, the buffer size is set to 220 KB, which should accommodate the record size required (224230 bytes). You can adjust this value further based on your specific data needs.
Step 3: Test and verify
After executing the script, attempt to load the problematic form to verify if the increased buffer resolves the issue. If the form loads correctly without errors, you can proceed to apply this solution to higher environments like UAT (User Acceptance Testing) and, eventually, production.
Moving to UAT and production
Once validated in Dev, the next step involves implementing the change in the UAT environment. You can access the UAT database through the Microsoft Dynamics Lifecycle Services (LCS) portal, which provides tools and utilities to manage your application environment.
If this adjustment is needed in the production environment, you can utilize the “Run custom X++ scripts with zero downtime” feature. This approach allows changes to be made without affecting active users or causing downtime. Be sure to calculate the buffer size accurately to avoid potential memory overloads, which could negatively impact performance.
Key considerations for performance tuning
When adjusting the buffer size, consider the following for better performance tuning:
- Impact on system performance: Increasing the buffer size can allow larger data sets to load, but excessive values can lead to memory strain. Adjust only as necessary to avoid unintended impacts on system performance.
- Testing in stages: Always start by testing in Dev, then move to UAT, and finally to production. Each stage provides an additional safeguard to catch potential issues before they reach the live environment.
- Continuous monitoring: After applying changes in production, monitor system performance for a period to ensure no new issues arise.
Summing up
With this solution, you can address this load issue with forms in Dynamics 365 effectively, restoring access to necessary data without compromising system stability. Be mindful of performance tuning principles, and always test thoroughly to ensure optimal results.
If you are facing this issue on production, you can execute this script by using the Run custom X++ scripts with zero downtime feature. In case you get stuck, feel free to reach out to us for support at marketing@confiz.com.
Note: Be careful when you are calculating the buffer size.
