when createing a view with 3 joined tables, I got the following error…
The total, internal size of the records in your joined SELECT statement is 26420 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
So I changed the ‘Buffer size’ value to some higher value say 50 Kb…
Now I want to confirm that will any problem arise because of changing the default value…?
and what is the maximum value we can specify for the buffer size?
I have seen AOS stability issues when buffer size is set to too high. If possible try to modify the view so that it does not require high buffer size value.
By doing this you create an instable environment that will cause you issues. Microsoft do not recommned you change it even though you have the option, look at the link posted by Harish. Also note the recommendation is to remove fields from the main table and put them into supporting tables. Alter this and you need to live with teh consequences, whatever they may be.
I tried changing the max buffer size to 28KB(default is 24) in a test environment and i didn’t face any issues. It just worked fine but i didn’t want to risk in production environment. So I Changed the table design to fit the max Buffer size.
Read the link and think about how you can corrupt the integrity of your database depending upon what you do - you are going against the recommendation of the authors of the software when you do this, so you need to tread carefully.
Hi, I am having the same problem with the buffer size.
I think it is a joke to be honest, how the standard buffer cannot cope with a simple 8 table query is beyond me.
I have had a highly ranked AX 2009 developer look at my query and it is developed in the way which they would create it, the only way to fix this silly error is to increase the buffer size - but the Gold Partners would probably advise against this as it is modifying the standard AX system settings.
As for “creating an unstable environment” - how would it as it a buffer size to allow data to pass through the buffer on running queries - assumably this would only happen when reports that are developed in SSRS are run, as the standard basic queries do not throw this error (as far as I know) so they would still only use under 24000bytes of buffer size?