When I try to execute a query which uses complex joins in dynamics ax 2009, the sql buffer size is reached depending upon the buffer size of configuration database size. Here is the error in the infolog.
The total, internal size of the records in your joined SELECT statement is 25666 bytes, but Microsoft Dynamics is by default performance-tuned not to exceed 24576 bytes.
It is strongly recommended that you split your table(s) into smaller units.
Alternatively, you have to specify a 'Buffer size' value of 26 Kbytes or higher in the 'SQL' tab page in the Microsoft Dynamics Configuration Utility. The default value is 24 Kbytes.
As a solution, you can increase the buffer size in the ax server configuration manager by multiples of 2. By default it has 24kb of buffer.Also note how the page memory use increases from task manager of 'AX32' services.The increase in buffer id proportional to the increase in PF memory.