sql server use of memory

Question

our sql server with 8gb of memory seems to be timing out. the advice I am getting is to add more memory. the question is how much? One guy says sql server will use all available memory "no matter how much you put it" (if a bad sp is running amok..).

is there a automated report I can run to advise me when memory use reaches 80%? do we need a third party program to monitor memory usage?

Answer 1

SQL will try to use all the physical memory it can get its hands on so that it can load all possible data into memory and limit the disk I/O. Something you might consider (if you haven't already) is to set the max memory option in SQL Server. I typically set it to 2-3 GB less than total physical memory (on a box dedicated to SQL Server). That way the OS will have a couple of gig to work with that SQL won't touch. If you have other services running on the server you'll want to adjust that for their needs.

Regarding the reports, there are a myriad of monitoring solutions available so you can watch and be alerted when your memory consumption is getting up there.

Answer 2

Handing out a sizing recommendation for SQL server is hard. You should use the Performance Monitor to check on SQL server counters for possible bottlenecks.

Also important when talking about sizing issues: there is a programmed hard memory limit depending on the version and the edition of the SQL server used. It may range from 1 GB (Express editions) to OS maximum (64-bit Datacenter editions). See the edition comparison chart of the appropriate version for details.

Answer 3

You don't state what version and edition of SQL Server you're running, but advice such as "add more memory" without the accompanying data to support that admonition, is bad advice. If you're running SQL Server 2005 perhaps this will help:

http://technet.microsoft.com/en-us/library/cc966540.aspx