I have had an issue on a SQL and SSRS server that i have been unable to resolve without getting someone to restart the application services to drop and recreate the TCP connections made to SQL. I have used Quest to look into this but can find very little apart from which SPID it is and where it is coming from.
We are using WebSense and on the server there is one TCP connection of concern, that at the start, has a memory allocation of 2....and 10 days since that restart....it is 474,849....which equates to around 3.8GB. We have a MIN and MAX memory setting of 4.8GB.
Currently we have 3MB for the procedure cache when it was originally just under 2GB....and 1GB for the buffer cache where it was originally just under 2GB also. This is not a heavily used server so luckily the performance impact is minor. However, since yesterday the alerts for recompilation (all <40%) are driving me crazy!! The procedure cache is shrunk all the way down....and once the procedure cache is down to its smallest, the buffer cache starts to suffer and lose its allocation.
I refuse to stop the alerts on this server just to make it quiet. I have checked the DBCC MEMORYSTATUS which gives very little information that i have already. The MEMORYCLERKS DMV shows 3.7mil single-pages for SQLCONNECTIONPOOL. ReportingServices is only using 150MB and doesnt seem to be used much anyway.
I have logged a call with WebSense but they dont seem to have any idea what could cause this. I was hoping someone here could point me in the right direction or to assist in trying to get more imformation, and more importantly, a way to show WebSense, or myself, the cause of the issue and hopefully resolve it.
I have read that something using prepared statements can cause a memory leak....but unsure how to find any evidence for this.
The server details are:
Windows 2008 R2 SP1
SQL 2008 SP3 Enterprise (10.0.5500)
2 x vCPU
I saw another post relating to a similar issue but no answers. My memory usage graph is a dead straight line with no deviations....meaning the consumption was constant. I would not expect the WebSense logging utility to be as busy during the day as at night so was thinking this was not related to the SQL activity....more along the lines of the type of connection or something in the application....but again.....how do i show this?
Any help would be much appreciated.