Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Dec 13 '09 at 0:53

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Mark Henderson Mar 15 '11 at 23:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add comment

8 Answers 8

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.

share|improve this answer
    
Hi the memory allocation is set to min=0 & max = 2147483647 MB Could having it set that high cause the problem?! –  Anonymous Dec 12 '09 at 17:11
    
Could be, or maybe not. Check the connections first. If you see that you aren't leaking connections, you have my permission to set the max memory down to a lower level. –  Dave Markle Dec 12 '09 at 17:15
    
No, those are the defaults and are usually harmless. SQL Server works best when it uses lots of RAM. –  CodeByMoonlight Dec 13 '09 at 1:27
add comment

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;
share|improve this answer
add comment

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?

share|improve this answer
    
Ah, PAE and AWE. I wonder when they're going to deprecate that crap and just say, "use x64!" –  Dave Markle Dec 12 '09 at 14:40
    
@Dave: next version of sql server is purported to be 64bit only. –  Joel Coel Dec 12 '09 at 15:03
    
Hi - i know one server for now, another in the future. Anyhow, its 64bit, all up to date and AWE is not enabled. Databases are realy tiny at about 20mb. Thank you for the links, i'll have a good read of those.. –  Anonymous Dec 12 '09 at 16:45
    
@unknown(google): AWE and PAE are hacks that MS had to put in in order for SQL Server to use more than 2 or 3GB of RAM when you run it on a 32-bit server. –  Dave Markle Dec 12 '09 at 17:15
add comment

By default, SQL Server will use as much memory as it can, up to all available RAM on your server. You can restrict use as follows:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'max server memory (MB)', 2048
RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
share|improve this answer
add comment

The trick here is that if SQL Server is on the same machine as your website (more common that it should be, and far from best practice), then SQL Server and IIS will continually grapple for memory. However, unlike IIS, SQL Server doesn't like letting go of memory once it's got hold of it. Application pools in IIS can be configured to recycle/release memory periodically. Setting a fairly aggressive value here will help you keep your application pool sizes down, but won't deal with the SQL Server issue.

The solution is to cap the amount of memory SQL Server can claim by running a script such as the one posted by RickNZ above, which will cap the memory used by SQL Server at 2048MB (2GB) - i.e. half the memory on the server.

As mentioned by other respondents: MS SQL Server will (if left to own devices) automatically claim RAM to service its various caching requirements (data buffers, procedure cache, lazy writer queues, etc) up to around 95% of the available memory available to the O/S before it stops gradually inching its use forward. It's not a memory leak per se - SQL Server was designed to run by default as the primary application service on a server. Shared server scenarios are definitely a "By Configuration" situation. Every data query that pulls back a new set of pages from disk may drive caching of:

  1. Data Pages
  2. Index Pages
  3. Query Plans (possibly multiple per stored procedure if you have non-homogeneous data distributions for key data in your tables/indexes, and also plans for raw SQL queries)

If you want to minimize the cache footprint rather than tune the memory, you need to get your head around topics such as Parameter Sniffing, Query Plan Cacheing and using code objects such as functions and views to minimize the amount of data returned to your queries.

Hope that helps.

Ozziemedes

share|improve this answer
add comment

Whatch the connections count.

SELECT COUNT(*) FROM master..sysprocesses

It's a bad sign if their number permanently growing up.

share|improve this answer
    
OK, thank you i'll keep an eye on that –  Anonymous Dec 12 '09 at 16:56
    
What might be the cause if the count does keep going up? –  Anonymous Dec 12 '09 at 16:58
    
That would generally indicate that some process is opening new connections but not closing them. Check to make sure that all your SqlConnection instances are properly Closed/Disposed. –  Aaron Dec 12 '09 at 17:02
add comment

Could be that you're not recycling your transaction log.

share|improve this answer
    
That could be the case yes, the DBs are backed up nightly, what would i need to set to ensure the logs are cleared out? Would setting the recovery model to simple help? (From FULL) –  Anonymous Dec 12 '09 at 17:05
    
If full, your DBA should have configured regular transaction log backups. This allows point in time restoration. –  SuperCoolMoss Dec 12 '09 at 18:45
add comment

Are you by any chance issuing a lot of unparameterized ad-hoc SQL? If so, you're probably having fun with the execution plan cache.

share|improve this answer
    
No we're very carefull to only use parameterised SP's –  Anonymous Dec 12 '09 at 16:55
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.