SQL SERVER 2008 - Memory Leak while storing Millions of records

Question

I need some help very badly. I'm working on a project where a bulk of data is entered all the time. It's a reporting software.

10 Million records in an average is stored per day and it could keep on increasing as users increase.

As of now, SQL SERVER CONSUMES 5gb of RAM on the task manager. I have an 8GB ram on my server now.

How do other enterprises manage such situations?

Answer 1

SQL Server typically uses as much memory as it can get it's hands on, as it then stores the more frequently accessed data in memory to be more efficient, as disk access is slower then memory access.

So nothing is wrong with it using 5gb of memory.
To be honest, it's leaving 3gb of memory for other applications and the operating system, so there might not be anything wrong with this. (If this is all that server is designed to do.).

To configure the memory limit, do the following:

• In SQL Server Enterprise manager, right click on the server name, and go to properties.
• Click on the Memory option
• Reduce the maximum server memory to what you think is appropriate.
• Click ok.

Answer 2

I highly doubt that this is in fact a memory leak. The increase of SQL Server's memory usage is by design, simply because it caches a lot of stuff (queries, procedures). What you will most likely see is that if the available memory that is still left runs low, SQL server will 'flush' its memory, and you would see in fact that memory will be freed in the end.

Answer 3

SQL Server uses memory efficiently and takes as much as it can. It's also usually clever enough to release memory when needed.

Using 5GB means:

• SQL Server is configured to 5GB or SQL Server has simply reserved this memory during normal usage
• It's left 3GB because it doesn't need to use it

Nothing is wrong... and I'd probably configure the SQL Server max mem to 6.5GB...

Late addition: Jonathan Kehayias blog entry