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
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.