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.
Asked
Active
Viewed 139 times
5
-
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 '13 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 '13 at 18:17
-
@ceejayoz You may run 32 bit SQL instances on either 32 or 64 bit OSes. – jscott Feb 25 '13 at 18:24
-
Maybe I've been in EC2-land too long. "Instance" to me means "server", I guess. – ceejayoz Feb 25 '13 at 18:27
1 Answers
5
This configuration setting should be a good start:
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.
Ryan Ries
- 55,481
- 10
- 142
- 199
-
1Microsoft 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 '13 at 21:03