Feb 24, 2012
tom

SQL Server 2008 R2 memory leak

Question

We’re in the process of moving from one server to another.

I’m running the SQL Server database off our new server, with the old server now using that as a DB server with the aim to make it a smooth transition from one server to the other whilst DNS propagates so no down time for anyone.

Nothing else is currently running on the new server, it’s a fresh install of everything.

The sqlserver.exe process seems to be increasing in memory requirements non stop, is this most likely to be connections not closing properly on my website? Or are there any known memory leaks in SQL Server? We receive around 40k page views a day, and probably around 5x that number in crawler pageviews.

Total DB MDF+LDF size is quite small, only 600mb. The current commit (KB) of sqlservr.exe is 2,393,000. It’s increasing at a rate of about 0.5MB every second or two.

If this is connections not closing properly on our website is there any way to clean old open connections? Apart from obviously fixing the root cause what can we do in the meantime?

Asked by Tom Gullen

Answer

I’m not a SQL internals guru, but to my knowlege, SQL will use as much RAM as you’ve configured it to and that the OS will allow it to have. There’s more cached than the DB its self, for example the execution plans
http://msdn.microsoft.com/en-us/library/ms181055.aspx

As a good rule of thumb your SQL memory should be configured in the following way:

Take your OS RAM and subtract 2GB or 10% whichever is greater. That should be the “Maximum” memory. Next set your mininimum memory to half of what your OS has set. As per Brent Ozar http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/

Answered by Eric C. Singer

Related posts:

  1. Any known SQL Server 2008 Management Studio memory leak issues?
  2. Enabling AWE Memory for SQL Server 2008 on Windows 2008 R2
  3. SQL Server 2005 and 2008 Memory Configuration Recommendations
  4. Something eats all memory (I suspect memory leak on some app). How to detect what?
  5. How much memory can SQL Server 2005 x86 use when installed on Windows Server 2008 x64?

Leave a comment