执行命令后 SQL Server 物理内存增长
我正在 Windows Server 2008 和 SQL Server 2008 上运行服务器应用程序，现在我的情况如给定。
我已经实现了自定义连接池（为什么我这样做是另一个很长的故事）。因此我不会在每个请求上打开或关闭连接。
服务器应用程序使用池中的 10 个可用连接在一分钟内执行数千个 DBCommand。
执行命令后，我没有清理连接（因为我不知道要清理什么以及如何在不关闭并重新打开它的情况下清理），而且我也没有处理命令对象本身。 。
当服务器应用程序出现故障时，它会通过调用 close 方法释放所有连接。
现在我观察到，经过 1 小时或 2 个 sqlserver 进程的测试后，内存达到了 3Gig 左右，然后我关闭了我的服务器应用程序，即使占用的内存没有释放或减少（根据任务管理器和资源监视器），毕竟我重新启动后sqlserver释放内存。
现在以下是我的问题。
我是否需要每次调用DBCommand对象的Dispose，如果是的话会对连接对象产生影响。
上述问题是导致我观察到的情况还是还有其他原因。
有没有办法在不关闭连接的情况下清理连接，以及每次 DbCommand 执行后需要清理什么样的垃圾。
谢谢 穆巴沙尔
I am running Server Application on Windows Server 2008 with SQL Server 2008, Now My scenario is as Given.
I have implemented a custom Connection Pooling (why I did that its another long story). therefore I am not opening or closing connection on each request.
Server Application executes more than thousands DBCommand in a minute using 10 connections available in the Pool.
after Execution of the command and I am not cleaning connection(becuase i have no idea what to clean and how to clean without closing and reopening it) and also i am not disposing the Command Object itself.
When server application goes down it releases all the connection by call close method on them.
Now I observed that the After a test of 1 hour or 2 process of sqlserver goes around 3Gig Memory, Then I shut down my server application even then the occupied memory was not released or reduced (according to task manager and Resource Monitor) afterall i restarted sqlserver to release the memory.
Now following are my question.
Do i need to call Dispose of DBCommand object each time, if yes then will it leaves impact on connection object.
Is above mentioned problem is causing what I observed or there are other reasons as well.
Is there any way to clean the connection without closing it and what kind of garbage it need to clean after each DbCommand execution.
Thanks
Mubashar
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
由于关闭应用程序会处理所有对象，这一观察结果清楚地表明内存丢失问题不是由于您的物品没有被处理而引起的。 （尽管如此，正如 David 正确指出的那样，始终处理 IDisposables 是一种好的做法。）
因此，您应该查看 SQL Server 的内存配置。请注意，如果内存可用，SQL Server 使用该内存是一件好事。未使用的内存是浪费金钱。通常，一旦其他进程需要内存，SQL Server 就会释放内存：
因此，除非您的服务器开始过度交换，否则我不会太担心 SQL Server 内存消耗。
Since closing your application disposes all objects, this observation make it quite clear that the memory loss problem is not caused by your objects not being disposed. (Nevertheless, as David correctly pointed out, always Disposing IDisposables is good practice.)
Thus, you should look at the memory configuration of your SQL Server. Note that, if memory is available, SQL Server using that memory is a good thing. Unused memory is a waste of money. Usually, SQL Server frees memory once some other process needs it:
So, unless your server starts swapping excessively, I would not worry too much about SQL Server memory consumption.
SQL Server 将使用大量内存，直到必要时才会将其归还，因此您应该始终在专用计算机上运行 SQL。如果您确实必须在共享环境中使用最大内存限制，则可以配置最大内存限制，但您的应用程序不会导致 SQL 释放内存。
SQL Server will use lots of memory and not give it back until necessary, hence you should always run SQL on a dedicated machine. You can configure a maximum memory limit if you do have to use it in a shared environment, but there is no way your application will cause SQL to release memory.
始终
Dispose()IDispose-ables。不知道。
使用 sp_reset_connection 重置连接，然后将其返回到池中。
Always
Dispose()IDispose-ables.No idea.
Use sp_reset_connection to reset the connection before returning it to the pool.
连接池是受控制的，传递给连接字符串的参数基本上包括以下内容：
· 连接超时
· 最小池大小
· 最大池大小
· 池
如果您发现应用程序在很短的时间内生成大量连接，那么您首先检查应用程序中打开的连接，并确保在使用完它们后立即关闭它们。关闭的连接不会真正关闭，并且可供向服务器发出的另一个请求使用。特别检查您的 DataReader 对象，并在它们脱离上下文后立即将其关闭。有时，开发人员在嵌套循环中使用多个DataReader，并在finally子句中关闭所有DataReader。不要这样做，并在退出循环后立即尝试停止 DataReader。是的，你总是可以在finally子句中再次检查DataReader对象的状态，如果没有关闭，你可以关闭它。
现在检查将 ConnectTimeout 调整到最小值，例如 1 秒。这设置了应用程序关闭的池中连接的生命周期，并将 MaxPoolSize 设置为服务器的最大限制。我猜想，如果没有其他应用程序连接到同一服务器数据库，则该数量将是 100。将 MinPoolSize 值设置为 5，因为您会将 ConnectTimeOut 设置为最小值，在这种情况下，为了使连接池有效工作，您将需要一些连接始终可用。
尝试这些设置和更改。希望这能让您满意。
谢谢，
拉吉夫·兰詹·拉尔
Connection Pooling is controlled and the parameters passed to a connection string that basically comprises the following:
· Connect Timeout
· Min Pool Size
· Max Pool Size
· Pooling
If you see that your application generates a huge number of connections in a very short span, then you first check your application about the open connections and make sure to close them as soon as you finish working with them. The closed connection will not be actually closed and will be available for use by another request to the server. Specially check your DataReader objects and close them as soon as they go out of context. Sometimes, developer uses multiple DataReaders in nested loop and close all the DataReader in finally clause. Don't do this and try to stop the DataReader as soon as you come out of loop. Yes, you can always check the status of a DataReader object in finally clause again, and if not closed, you can close it.
Now check with tweaking the ConnectTimeout to a minimum, say 1 second. This sets the lifespan of a connection in pool closed by the Application, and set the MaxPoolSize to maximum limit of your server. I guess, it would be 100, if no other application would be connected to the same server database. Put MinPoolSize value to 5, as you would set the ConnectTimeOut to a minimum and in this case, connection pooling to work efficiently, you would need some connections always available.
Try these settings and changes. Hope this will work to your satisfaction.
Thanks,
Rajeev Ranjan Lall