What's a good way of checking how much (actual) memory is currently being used vs. how much is SQL Server allocated to itself?
检查当前正在使用多少（实际）内存与SQL Server分配给自身的数量的好方法是什么？
I've been resorting to memory_utilization_percentage but that doesn't seem to change after running the following to release memory.
我一直在使用memory_utilization_百分比，但在运行以下内容后释放内存似乎没有改变。
SELECT [Memory_usedby_Sqlserver_MB] = ( physical_memory_in_use_kb / 1024 ) ,
[Memory_utilization_percentage] = memory_utilization_percentage
FROM sys.dm_os_process_memory;
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
SELECT [Memory_usedby_Sqlserver_MB] = ( physical_memory_in_use_kb / 1024 ) ,
[Memory_utilization_percentage] = memory_utilization_percentage
FROM sys.dm_os_process_memory;
A solution is to drop max server memory for the SQL Server and increase it again to force SQL Server to release unused but allocated memory. However an issue with this approach is that we cannot be sure how far to reduce max server memory, hence run the risk of killing SQL Server. This is why it's important to understand how much SQL Server is 'actually' using before reducing the value for max server memory.
解决方案是删除SQL Server的最大服务器内存并再次增加它以强制SQL Server释放未使用但已分配的内存。但是，这种方法的一个问题是我们无法确定减少最大服务器内存的距离，因此冒着杀死SQL Server的风险。这就是为什么在减少max server memory的值之前了解SQL Server“实际”使用了多少很重要的原因。
5 个解决方案
#1
1
SQL Server always assumes it is the primary application running. It is not designed to share resources. It will always take all the available memory and it will only release it for the operating system unless you throttle with 'max server memory'.
SQL Server始终假定它是运行的主应用程序。它不是为共享资源而设计的。它将始终占用所有可用内存，并且只会为操作系统释放它，除非您使用“max server memory”进行限制。
By design, Sql Server does not play well with others.
按照设计，Sql Server不能很好地与其他人一起使用。
This sqlskills article recommends a baseline for throttling followed by monitoring and raising the throttle as needed:
此sqlskills文章建议使用限制基准，然后根据需要监视和提高限制：
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
#2
0
You have to set 'Max server memory' to some value between 1-2 GB. This range is safe in most cases. It may take a time to release the memory after executing below:
您必须将“最大服务器内存”设置为1-2 GB之间的某个值。在大多数情况下，此范围是安全的。执行以下操作后可能需要一段时间才能释放内存：
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 1024;
GO
RECONFIGURE;
GO
That setting allows to clear the pool, compile memory, all the caches, clr memory, etc.
该设置允许清除池，编译内存，所有缓存，clr内存等。
The minimum value for 'max server memory' is 128 MB, but it's not recommended as SQL Server may not start in certain configurations. If it happens, use "-f" switch to force SQL start with minimal configuration, then change the value to the original one.
“最大服务器内存”的最小值为128 MB，但不建议这样做，因为SQL Server可能无法在某些配置中启动。如果发生这种情况，请使用“-f”开关以最小配置强制SQL启动，然后将值更改为原始值。
#3
0
I don't have a solution for how to release the allocated memory. However, for our purposes we were able to figure out, how to allow active-active clusters to run safely. We've decided to set minimum server memory to ~2GB. This is helpful because no matter how much max memory an instance decides to use, it will never run other instances out of memory. So again, this solves our purpose but it still doesn't answer the question of how much memory is actually being used, how low can we drop the max server memory, etc...
我没有解决方法如何释放分配的内存。但是，出于我们的目的，我们能够弄清楚如何允许主动 - 主动群集安全地运行。我们决定将最小服务器内存设置为~2GB。这很有用，因为无论实例决定使用多少内存，它都不会在内存中运行其他实例。所以再一次，这解决了我们的目的，但它仍然没有回答实际使用了多少内存，我们可以降低最大服务器内存等等的问题......
#4
0
I don't think SQL Server releases memory unless the operating system actively requests it. If there is a case of other processes requesting more memory and if there is none at all, SQL Server will release the unused memory on its own. Rather than trying to flush the unusued memory, I'd probably go with limiting the SQL's maximum allowed memory.
除非操作系统主动请求，否则我认为SQL Server不会释放内存。如果有其他进程请求更多内存而且根本没有内存，SQL Server将自行释放未使用的内存。我可能会限制SQL的最大允许内存，而不是尝试刷新不可用的内存。
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory', 512; --or some other value
GO
RECONFIGURE
GO
For further info, you could check this MSDN article: https://msdn.microsoft.com/en-us/library/ms178067.aspx
有关详细信息，您可以查看此MSDN文章：https：//msdn.microsoft.com/en-us/library/ms178067.aspx
#5
-1
This post is solved in the following link, please check the format:
这篇文章在以下链接中解决，请检查格式：
SQL Server not releasing memory after query executes
SQL Server在查询执行后不释放内存
#1
1
SQL Server always assumes it is the primary application running. It is not designed to share resources. It will always take all the available memory and it will only release it for the operating system unless you throttle with 'max server memory'.
SQL Server始终假定它是运行的主应用程序。它不是为共享资源而设计的。它将始终占用所有可用内存，并且只会为操作系统释放它，除非您使用“max server memory”进行限制。
By design, Sql Server does not play well with others.
按照设计，Sql Server不能很好地与其他人一起使用。
This sqlskills article recommends a baseline for throttling followed by monitoring and raising the throttle as needed:
此sqlskills文章建议使用限制基准，然后根据需要监视和提高限制：
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
#2
0
You have to set 'Max server memory' to some value between 1-2 GB. This range is safe in most cases. It may take a time to release the memory after executing below:
您必须将“最大服务器内存”设置为1-2 GB之间的某个值。在大多数情况下，此范围是安全的。执行以下操作后可能需要一段时间才能释放内存：
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 1024;
GO
RECONFIGURE;
GO
That setting allows to clear the pool, compile memory, all the caches, clr memory, etc.
该设置允许清除池，编译内存，所有缓存，clr内存等。
The minimum value for 'max server memory' is 128 MB, but it's not recommended as SQL Server may not start in certain configurations. If it happens, use "-f" switch to force SQL start with minimal configuration, then change the value to the original one.
“最大服务器内存”的最小值为128 MB，但不建议这样做，因为SQL Server可能无法在某些配置中启动。如果发生这种情况，请使用“-f”开关以最小配置强制SQL启动，然后将值更改为原始值。
#3
0
I don't have a solution for how to release the allocated memory. However, for our purposes we were able to figure out, how to allow active-active clusters to run safely. We've decided to set minimum server memory to ~2GB. This is helpful because no matter how much max memory an instance decides to use, it will never run other instances out of memory. So again, this solves our purpose but it still doesn't answer the question of how much memory is actually being used, how low can we drop the max server memory, etc...
我没有解决方法如何释放分配的内存。但是，出于我们的目的，我们能够弄清楚如何允许主动 - 主动群集安全地运行。我们决定将最小服务器内存设置为~2GB。这很有用，因为无论实例决定使用多少内存，它都不会在内存中运行其他实例。所以再一次，这解决了我们的目的，但它仍然没有回答实际使用了多少内存，我们可以降低最大服务器内存等等的问题......
#4
0
I don't think SQL Server releases memory unless the operating system actively requests it. If there is a case of other processes requesting more memory and if there is none at all, SQL Server will release the unused memory on its own. Rather than trying to flush the unusued memory, I'd probably go with limiting the SQL's maximum allowed memory.
除非操作系统主动请求，否则我认为SQL Server不会释放内存。如果有其他进程请求更多内存而且根本没有内存，SQL Server将自行释放未使用的内存。我可能会限制SQL的最大允许内存，而不是尝试刷新不可用的内存。
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory', 512; --or some other value
GO
RECONFIGURE
GO
For further info, you could check this MSDN article: https://msdn.microsoft.com/en-us/library/ms178067.aspx
有关详细信息，您可以查看此MSDN文章：https：//msdn.microsoft.com/en-us/library/ms178067.aspx
#5
-1
This post is solved in the following link, please check the format:
这篇文章在以下链接中解决，请检查格式：
SQL Server not releasing memory after query executes
SQL Server在查询执行后不释放内存