物理内存使用量达到高峰，再也没有恢复 [英] physical memory usage reaches at peak and never comes back

问题描述

我正在使用SQLBulkCopyInsert.writeToServer()在内存为12 GB且页面文件为24GB的服务器上导入大量内存，即5000000.一旦启动，我注意到该服务器上的物理内存使用量会稳定增加，直到接近100％.即使脚本完成之后(或由于缺少内存而失败)，内存使用率也不会下降.似乎Sql Server从不释放用于执行大容量插入的内存资源.它用于执行大容量插入的资源.

I am importing the recods of huge amoung i.e 5000000 using SQLBulkCopyInsert.writeToServer(), on a server with 12 GB of memory and a page file of 24GB. Once it starts, I notice the physical memory usage on that server steadily increases until in nears 100%. Even after the script completes (or fails due to lack of memory) the memory usage never drops. It seems that Sql Server never releases the memory resources it uses to perform the bulk inserts.ources it uses to perform the bulk inserts.

推荐答案

基本答案是SQL服务器被设计为使用所有可用的内存.它分配内存，以便即使不使用它也可以独占访问它. SQL使用顺序内存块以提高性能.这意味着它不想让其他应用程序访问这些块的各个部分.

您可以做几件事.首先，如果您看到SQL使用了所有内存并且一切运行正常，请不要担心.这很可能不是一个真正的问题.但是，您应确保将SQL Server配置为为操作系统和在该服务器上运行的任何其他应用程序保留一些内存.您可以配置SQL将使用的最小和最大RAM量.我能提供的最佳建议是为SQL提供所有可能的内存.我通常为OS保留2GB的空间，其余的留给SQL.

至于SQL不释放内存或内存泄漏，实际上并非如此.它不会将内存提供给操作系统，但不一定会低效地使用内存.要了解有关SQL如何使用内存以及如何更好地配置服务器的更多信息，请阅读Brent Ozar的这篇文章:

The basic answer is that SQL Server is designed to use all of the memory available to it. It allocates the memory so that it has exclusive access to it even if it isn''t using it. SQL uses sequential memory blocks in order to improve performance. That means it doesn''t want to give other applications access to pieces of those blocks.

There are a couple things you can do. First, don''t worry about it if you see SQL using all of the memory and things are working well. It most likely isn''t a real problem. However, you should be sure that your SQL server is configured to leave some memory for the Operating System and any other applications you run on that server. You can configure the minimum and maximum amount of RAM SQL will use. The best advice I can give is that you give SQL all the memory you can. I usually reserve 2GB for the OS and give SQL the rest.

As far as SQL not releasing the memory or having a memory leak, that isn''t really the case. It doesn''t give the memory to the OS, but it isn''t necessarily using the memory inefficiently. To learn more about how SQL uses memory and what you can do to better configure your server, read this article by Brent Ozar: http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/[^]

这篇关于物理内存使用量达到高峰，再也没有恢复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！