执行查询后，SQL Server不释放内存 [英] SQL Server not releasing memory after query executes
问题描述
我认为我在这里有一个基本的问题，可能会遇到很多问题.当我在SQL Server中运行查询时，它将在内存中加载执行查询所需的所有数据(例如，如果存在联接，则它将从那两个表中加载必要的数据)，但是当查询完成执行内存时不释放SQL Server使用的资源.
I think I have a basic question here that many might have encountered. When I run a query in SQL Server it will load in memory all the data it needs for query execution (for example, if there is a join then it would load the necessary data from those two tables) but when the query finishes executing the memory consumed by SQL Server is not released.
我注意到了这一点，因为几天前我正在分析一个占用大量tempdb空间的查询.当我用来运行查询时，它将(在执行结束时)消耗多达25 GB的RAM.除非我重新启动MSSQLSERVER服务，否则不会释放这25 GB的内存.
I noticed this because a few days back I was analyzing a query that takes up a lot of tempdb space. When I used to run the query it would (by the end of execution) consume upto 25 GB of RAM. This 25 GB RAM would not be released unless I restarted the MSSQLSERVER service.
你们如何进行SQL Server内存管理?显然这是一个问题吧?
How do you guys do SQL Server memory management? This is clearly an issue right?
我还想听听您是否进行了一些特定的操作来清除单个查询所用的内存.
I would also like to hear if you do something specific to clear the memory used up by a single query.
提前谢谢！
推荐答案
SQL Server确实被设计为请求尽可能多的RAM，除非操作系统明确要求此内存，否则不会释放该RAM.我认为最好的方法是限制服务器可以使用的RAM数量，这将使OS拥有一定数量的资源以使用no-matter-what.要设置此如何使用SQL Server Management Studio配置内存选项:
SQL Server is indeed designed to request as much RAM as possible which will not be released unless this memory is explicitly required by the operating system. I think the best approach is to limit the amount of RAM the server can use which will allow the OS to have a set amount of resources to use no-matter-what. To set this How to configure memory options using SQL Server Management Studio:
使用两个服务器内存选项最小服务器内存和最大服务器内存，重新配置由SQL Server内存管理器管理的内存量(以兆字节为单位)，用于SQL Server的实例.
Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server.
- 在对象资源管理器中，右键单击服务器，然后选择属性.
- 点击内存节点.
- 在服务器内存选项下，输入最小服务器内存和最大服务器内存所需的数量.
- In Object Explorer, right-click a server and select Properties.
- Click the Memory node.
- Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.
您还可以使用以下命令(示例)在T-SQL中进行操作:
You can also do it in T-SQL using the following commands (example):
exec sp_configure 'max server memory', 1024
reconfigure
将消耗限制为1GB.
注意:上面的内容不会将SQL Server的所有方面限制为该数量的内存.这仅控制缓冲池和执行计划缓存.此设置不控制诸如CLR，全文本，SQL Server .exe文件使用的实际内存，SQL Agent，扩展存储过程等.但是，这些其他事情通常不需要那么多的内存，缓冲池和执行计划缓存需要大量的内存.
我希望这会有所帮助.
这篇关于执行查询后，SQL Server不释放内存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
