Overcoming the Joined Record Size Limitation in D365 Finance and Operations

Resolving the Record Size Limit Error in Microsoft Dynamics 365 Finance and Operations: A Performance Tuning Guide.

Hi readers,

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.”

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

The error stems from the default global buffer size in D365 Finance and Operations, set at 196,608 bytes. This buffer temporarily holds data during record joins. If data exceeds this limit, as in this case (224,230 bytes), the form fails to load. Increasing the buffer size resolves the issue.

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.

Note: Be careful when you are calculating the buffer size.

Solution: Adjusting the Buffer Size

To resolve this issue, increase the buffer size in the system configuration. Test the change in a development environment first to ensure stability. Follow these steps:

• Access Development Environment: Apply and test the change in your Dev VM to confirm functionality and performance impact.
• Run Buffer Size Adjustment Script: Execute the following SQL script to update the buffer size

UPDATE SYSGLOBALCONFIGURATION
SET VALUE = 230
WHERE NAME = 'VOLATILE_SQLTRANSFERBUFFERKBYTES';        

• Adjust Buffer Size: In this example, the buffer is set to 220 KB to handle the required 224,230 bytes. Modify this value as needed for your data requirements.
• Test and Verify: After running the script, test the form. If it loads without errors, implement the solution in higher environments (UAT and production).

Moving to UAT and Production

Once validated in Dev, implement the change in UAT via the Microsoft Dynamics Lifecycle Services (LCS) portal.

For production, use the "Run custom X++ scripts with zero downtime" feature to apply changes without disrupting active users. Ensure accurate buffer size calculations to prevent memory overload and performance issues.

Important Considerations

• System Performance: Increasing the buffer size supports larger datasets but may strain memory if set excessively high. Adjust cautiously.
• Stage-Wise Testing: Test changes in Dev first, then UAT, and finally production to catch issues early.
• Continuous Monitoring: After deployment in production, monitor performance to ensure stability and address any new issues promptly.

"Hope this helps you resolve the issue seamlessly. Happy troubleshooting and learning!"