SQL Server does not kill queries that Dynamics NAV does

Question

Hello,

I have the following scenario, I have Dynamics NAV 2017 working in a Virtual Machine in Azure, everything is fine until a search is made for a record in Dynamics NAV, the SQL service increases the use of RAM memory up to 96% and the search on the platform ends but SQL continues to consume the resources. The way we have found workaround for the moment is to stop user access in Dynamics NAV, stop the SQL Server service and start it again and allow user sessions in Dynamics NAV. Any similar experience and possible solution?

Thanks

Answer 1

It is completely normal and expected for SQL Server to consume all RAM up to the max server memory and not release it. This is not a problem or an indication of a problem.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

Answer 2

Adding to what Tom says, this is to be expected. If this is a problem because there are other processes on the machine, you may want to restrict how much memory SQL Server can grab with the configuration option "max server memory".

Else, I would say that the solution is to sit on your hands and do nothing.