5

This may be related to other threads I've seen, but I need a solution to this specific situation. The SQL instance continuously consumes more and more RAM until after about a week it's grabbed over 3.5 GB of RAM (remember it's a 32-bit instance, and no I can't use a 64-bit instance) and the functionality of the instance basically comes to a grinding halt, forcing a server reboot to get it freed up. There must be a more elegant method of recovering the RAM, or configuration change to prevent it in the first place. I'm obviously not an expert in SQL Server and the instance was basically a "default" installation so it certainly isn't optimized.

Improve this question
4
  • I'm confused. Your title says it's a 64-bit OS, but your question says it's a 32-bit instance.
    – ceejayoz
    Feb 25, 2013 at 17:46
  • In 64-bit Windows, a 32-bit process can allocate up to 4GB, so his question seems feasible to me.
    – Ryan Ries
    Feb 25, 2013 at 18:17
  • @ceejayoz You may run 32 bit SQL instances on either 32 or 64 bit OSes.
    – jscott
    Feb 25, 2013 at 18:24
  • Maybe I've been in EC2-land too long. "Instance" to me means "server", I guess.
    – ceejayoz
    Feb 25, 2013 at 18:27

1 Answer 1

Reset to default
5

This configuration setting should be a good start:

enter image description here

Edit: Also, keep in mind that limiting the amount of RAM that SQL is allowed to use will save memory for other applications, other instances, and the OS itself... but by limiting the amount of RAM SQL is allowed to use, you may just hit the performance wall more quickly. SQL will start paging data out to disk when it runs out of RAM, regardless of if you are putting a hard limit on the amount of RAM SQL is allowed to use or not. If you want performance, you want to avoid having to use the computer's disks. This means adding more RAM, using 64-bit instances so they can take advantage of the extra RAM, and having more efficient indices and queries. You already said you can't do the first two - the next thing would be to find a DBA to try to optimize your data and queries so that they don't use as much memory.

Improve this answer
1
  • 1
    Microsoft SQL Server attempts to cache as much of your databases in memory as it can. RAM is orders of magnitude faster than disk. SQL Server comes out of the box set to use all available RAM, as Ryan showed. The guidance is to run nothing but SQL Server on a machine intended for SQL Server use. Left to its own devices, over time SQL Server can grow as large as the sum of all your databases and then some. This is by design.
    – Larry Silverman
    Feb 26, 2013 at 21:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .