0

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

0

8 Answers 8

3

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.

3
  • Hi the memory allocation is set to min=0 & max = 2147483647 MB Could having it set that high cause the problem?!
    – Anonymous
    Dec 12, 2009 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. Dec 12, 2009 at 17:15
  • No, those are the defaults and are usually harmless. SQL Server works best when it uses lots of RAM.
    – MartW
    Dec 13, 2009 at 1:27
3

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;
2

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?

4
  • Ah, PAE and AWE. I wonder when they're going to deprecate that crap and just say, "use x64!" Dec 12, 2009 at 14:40
  • @Dave: next version of sql server is purported to be 64bit only.
    – Joel Coel
    Dec 12, 2009 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, 2009 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. Dec 12, 2009 at 17:15
1

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
1

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

0

Whatch the connections count.

SELECT COUNT(*) FROM master..sysprocesses

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

2
  • What might be the cause if the count does keep going up?
    – Anonymous
    Dec 12, 2009 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, 2009 at 17:02
0

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

2
  • 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, 2009 at 17:05
  • If full, your DBA should have configured regular transaction log backups. This allows point in time restoration. Dec 12, 2009 at 18:45
0

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.

1
  • No we're very carefull to only use parameterised SP's
    – Anonymous
    Dec 12, 2009 at 16:55

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