Troubleshooting SQL Server Memory Issues

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 p开发者_StackOverflow社区articular, 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?

---

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

---

It won't be memory as such: SQL Server is designed to use it

You probably have an long running transaction, or something else like upload, that is blocking other processes.

Run this to see what's going on

SELECT
    p1.SPID AS blockedSPID, p2.SPID AS blockingSPID, ...
FROM 
    master..sysprocesses p1
    JOIN
    master..sysprocesses p2 ON p1.blocked = p2.spid

And when you say "grows by 2GB". do you meand MDF or LDF file?

• LDF = long running or open transaction
• MDF = lots of data being generated/uploaded (I've seen a rogue audit trigger)

The 2GB memory increase will be data in cache most likely