SQL Server slowly hogging RAM, where do i start to optimise? [duplicate]

Possible Duplicate:
SQL Server memory constantly going up, from 1GB RAM used to 7GB in a week.

Hi There,

We have a fairly new 2008 server with 4gb ram and SQL Server 2008. We're currently serving a few sites that only get about 300 hits per day each, so very low traffic.

However, over time, the RAM gets slowly eaten by SQL Server (over a couple of days) until the sites in question slow to a crawl.

The only thing that fixes it is to resstart the SQM server process and the app pool.

In SQL Server, the max number of concurrent connections is set to '0' - unlimited.

My DBA is away but so can anyone help advise me where/how i an start to figure out where the issue is?

Many thanks

Ben

You need to check to make sure SQL Server's memory allocation is properly set: http://support.microsoft.com/kb/321363

Also, check to see that you don't have lots and lots of connections being opened and not disposed of. Run the sp_who2 stored procedure to see if your connection count is going up drastically. If that's the case, then you have a bug in your code where you're not disposing of your connections properly.

Most likely there isn't any leak. This is the 'by design' behavior of SQL Server: allocate all physical RAM to the buffer pool, leave nothing to other applications. For details, see my reply to When can I host IIS and SQL Server on the same machine? (short answer: never).

Update

If your databases are 20mb then the culprit is unlikely to be the buffer pool. First thing to do would be to check the errorlogs and the system event log for messages from the moment the memory pressure occurred. Whenever SQL faisl to allocate memory it will dump a detailed internal allocation map that can help identity any possible problem. In addition you should start inspecting sys.dm_os_memory_clerks and see which clerks grow over time:

select type, sum(single_page), sum(multi_page)
from sys.dm_os_memory_clerks
group by type
order by sum(single_page+multi_page) desc;

SQL SERVER 2008 - Memory Leak while storing Millions of records

Can someone explain my Windows/SQL Server memory usage

SQL Server 2k5 memory consumption

And a search

Other than that:

• you've one server for both web and SQL Server? Not recommended

• With 4GB RAM is it 32 or 64 bit. Is 3GB, AWE or PAE enabled?

• What size is your database? Is it maintained?

• Have you use code like sp_xml_preparedocument that cause memory leaks?

• Latest service pack etc?