56
We are facing high Memory Consumption in SQL Server 2012 upto 95% .
As the number of AX Users log in a day and start their working (like taking reports , processing ) , SQL Server 2012 memory goes upto 95% gradually in 3 to 4 hrs .. And when i check AX Server at off hours when no user connected to AX ... Server 's Memory remains upto 95%... Please advise to resolve issue.
*This post is locked for comments
Is this just an observation that seemed odd to you, or is there a performance issue that triggered the post?
If SQL memory consumtpion is too high then other system functions may struggle and memory has to be swpaped about and prformance can be impacted. is exactly That is why sql server should be installed on a dedicated machine (virtual or phisical,r) with nothing else present except the bare minimum needed for the server to run -especially for your production instance. In the real world dedicated servers come at a cost that not all choose to afford.
In such a situation while it may reduce average performance you may paradoxically need to reduce the max memory available to SQL so that other systems have enough memory to run without swapping memory with SQL. Even when you increase memory on the machine, but the database load remains the same, it is possible that even after the memory upgrade SQL Server will be the top consumer of memory.
Let me expand a bit. By default SQL Server dynamically allocates memory and does not release it until there is a request from Windows. SQL Server,reserves availablememory and caches data into the memory to decrease access to disk drives and thereby increase its performance. When other processes on the same server require memory, SQL Server releases the needed memory. So, SQL Server can in theory use almost all available memory on the server. What other systesm- mayeb the operating system itself.
When your server is a dedicated database server there is no problem regarding memory allocation, however sometimes there are many applications running on the database server. Those applications use memory at start up and may be unable to request additional memory from Windows, when needed. In this case, to permit the other applications to work , we can limit SQL Server's access to memory by setting the "max server memory" option. By default it is set to 2147483647 MB, which allows SQL Server to use approximately all of the server's memory. Change this setting to a lower value, according to requirements. It e.g. by SQL Server Management Studio or a T-SQL script.
When there are more than one SQL Server instances running on your server, you can set "max server memory" for each of these, considering their load, .The sum of "max server memory" values for all instances should be be less than the total physical memory in the server. This ensures that free memory will be available immediately for instances after start up. However,in this case when one of instances is not running, the running instances will be unable to use the remaining free memory.
DBCC MEMORYSTATUS command
MEMORYSTATUS is an undocumented DBCC command that provides a snapshot of SQL Server's current memory status. This command identify the root cause of the memory pressure on SQL Server and it also analyzes that, how well SQL Server is using its allocated memory
Consider also adding a flash memory drive and install all other server systems onthta e.g the operating system, log files etc.
.
Its default behavior of SQL, it will consume as much RAM as it can and will not release once it has consumed. To release/reset the consumption, you have to restart the SQL services.
But as for best practices let it consume and you can set maximum RAM limit in SQL settings to limit the consumption.
Anything between 80-85% would be good for SQL.
It is good that almost all available memory is utilized. This way the database can provide results from memory instead of reading from the disk. Unused memory is memory that is not giving you any benefit :)
However to prevent using all the memory you should configure maximum allowed memory for SQL Server as Ievgen suggested.
SQL server tries to use as much memory as possible end never release it back, so it is ok and should be your concern . If it consumes 100% then you need to set "max server memory" as per article
https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/configure-sql-server-and-storage-settings#configuring-max-server-memory to leave some for windows and other applications.
Current AX Version is AX 2012 R3
#1
André Arnaud de Cal...
294,145
Super User 2025 Season 1
#2
Martin Dráb
232,896
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
