I am facing a severe performance issue where Business Central (tested on v24 and v27) automatically injects the OPTION (FAST 50) hint into all SQL queries.
This is causing a "low memory grant" problem: the SQL Optimizer estimates a tiny memory requirement based on 50 rows estimation. When the query actually processes thousands of records, it results in massive TempDB spills and I/O pressure across the entire instance.
I mainly use the FindSet and Find function in AL, but I haven't found any documentation or parameters for this function that would allow me to control or disable this hint. I want to avoid manually managing Plan Guides or forcing plans in Query Store for every single case.
Is there a specific Business Central configuration that force the usage of the OPTION (FAST 50) hint ?
Thank you for any kind of help.
