8

I have a ~45GB database that increases rapidly (~1GB every week).

So it's doing a lot of inserts; the web app doesn't have many users so reads are low right now.

But I'm noticing that the RAM sql server users goes from ~1GB when the server is first started to using ~7GB of RAM after a week.

Is there some sort of memory leak? There was an article on MSDN that suggested that the SQL server increases the memory it uses for caching and that will always continue to increase. However, the server only has 7.5GB or ram so I'm worried that it's going to run out of memory unless I restart SQL Server every week... which seems wrong.

Is this typical behavior? Is there some setting that I can set to set a cap on the memory usage?

rksprst
  • 445
  • 1
  • 6
  • 9
  • 3
    See Farseeker's answer. He hit the nail on the head. This is normal, out-of-the-box behavior and while there is a way to cap SQL's memory use; you don't really need to do that. Our SQL server caps at 10.5GB - 11GB (out of 12GB total) of memory in a few hours. I, and my DBA, is very happy when we see SQL with that much memory. It means our applications will have access to that data just that much faster. – Cypher Sep 18 '10 at 22:56

2 Answers2

14

SQL server is meant to use as much ram as it can get its hands on. SQL server is also incredibly smart. We have database servers with RAM ranging from 2Gb to 96Gb and not once, ever, have I had to step in and start managing RAM manually, and most of them sit at 99.5% utilisation.

The simple fact is that SQL Server running with 1Gb of consumed memory is going to be a lot slower at reads than an SQL server running with 96Gb of consumed memory.

Unless you are sharing the SQL server roles with other memory intensive roles, then just leave it alone. It will never use more memory that is available in the system, and if it hits 99% utilisation then great - you've created a large cache and things will be faster from now on.

Mark Henderson
  • 68,823
  • 31
  • 180
  • 259
2

Typically you'll want to allocate 1-2 Gigs of RAM for the OS so that the OS has RAM to work with. The SQL Server can then have the rest of the memory.

mrdenny
  • 27,174
  • 4
  • 41
  • 69