Dear Professional Members,
Inside our MS Windows Server 2008 R2, there were recorded more than 90% memory occupied by MS SQL Server 2008 in the part of Dynamics NAV. How to configure memory for NAV database in a proper memory allocation? As illustrated below from 2 screen shots for the memory problems:
All available memory (for MS SQL server) is being consumed constantly
(Difference between Peak and Minimum memory in use: 1.2GB only)
Next, we are installed the 2 Tiers in the Same Server (i.e. Dynamics NAV Server/NAV WS and MS SQL Server). If we delegate it into 3 Tiers into 3 Servers, can we solve the memory problem for occupying in 90%?
Hope our members can give us remedies.
Normally SQL Server will consume as much memory as possible, e.g. granted by the OS. The OS itself usually uses about 2GB, hence all the rest is available for “others”, and sooner or later SQL Server will take it all …
Therefore you shouldadjust the SQL Server property “Max. Server Memory”: on a dedicated machine (just OS and SQL) this could be Physical RAM - 2 GB;for example, if you have 64GB RTAM in totalm the SQL Server Max should be 62GB.
In your case you also have to regard the memory consumption of the other NST & WS - ideally these services should be placed on a sparate box. So if you put them directly on the SQL Server machine, the SQL Server Max. Memory needs to be lower; I suggest to calculate 2GB per NST/WS.
So in your case maybe it’s OK to adjust SQL Server “Max. Server Memory” to 56GB, and maybe the “Min. Server Memory” to 32GB.
WIth oerfmon.exe you could monitor “Memory - Available MBytes” - this should be at least several hundreds; if that’s not the case I’d rather decrease SQL Server Max. Memory …