Currently an active database continues to use more memory until it reaches about 5.8GB and then starts throwing frequent connection timeout errors. I understand that SQL Server is designed to use as much memory as possible, but It shouldn't be throwing those errors. In particular, something seems to run which causes the memory to grow by about 2GB very rapidly. The DB has many indexes and stored procedures, how do I track down which one is responsible?
A:
gbn
2010-07-29 07:45:13
Thanks for the answer, I'll try it out as soon as I get a chance. But to quickly respond to your question I actually meant the memory increases by 2GB (I've since updated the OP)
Brehtt
2010-07-29 09:04:12
The DB just threw the error again: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". I tried running the query you provided but it returned no rows.
Brehtt
2010-07-30 02:59:53
A:
SQL server is designed to grow and eat up all memory in the system. Therefore it will do just that, allocating memory for query plan cache and tables and indexes. It is supposed to release the memory when the system requires it but it is my experience that the system will start using more memory using the paging system and things will slow down a lot before sql server starts releasing the memory it holds.
If your data is not as big as the memory it seems to eat and you don't have too many connections either, then it must be allocating the memory for the many caches it uses.
You can find this from sql server perfmon memory manager counters to pin point what component of sql server is eating up most of the memory.
I encountered just this and here are the details of how i solved the issue (link) - a combination of upper memory limits (sp_configure 'max server memory') + ensuring that the query plan cache does not grow too big