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.

I've got a server that reports 8 GB of ram used up at 99%. When restart Sql Server, it drops down to about 5% usage, but gradually builds back up to 99% over about 2 hours.

When I look at the sqlserver process, its reported as only using 100k ram, and generally never goes up or below that number by very much. In fact, if I add up all the processes in my TaskManager, it's barely scratching the surface of my total available (yet TaskManager still shows 99% memory usage with "All processes shown").

It appears that Sql Server has a huge memory leak going on but it's not reporting it. The server has ran fine for nearly two years, with this only starting to manifest itself in the last 3-4 weeks.

Anyone seen this or have any insight into the problem?

EDIT

When the server hits 99%, performance goes down hill. All queries to the server, apps, etc. come to a crawl. Restarting the service makes things zippy again, until 2 hours has passed and the server hits 99% once again.

share|improve this question
add comment

5 Answers

up vote 6 down vote accepted

SQL server will cache as much data as it can -- which is as others have said a good thing. This data it is caching is called the buffer cache -- but you need to limit this.

So what you need to do to fix this is limit the size of the buffer cache. Rule of thumb is (Total_RAM - (2GB or 10% (whichever is larger)) = Max buffer cache. If you don't limit the size of this cache then the server can start to hit memory pressure as the OS and SQL start contending for memory. You can find this setting under the memory section of the SQL server properties in management studio:

enter image description here

Look at memory best practices in Brent Ozar's SQL Server deployment checklist in part ii for more detail about this.

Since this started happening recently, my guess is someone did something to create memory competition with SQL server by installing or running another piece of software.

Lastly, if you want to see how much memory SQL server is actually using for the buffer pool look at the SQL Server Memory Manager: Total Server Memory perfmon counter. This is the amount of memory the buffer pool is using (not the total server memory). Taskman won't show this memory.

share|improve this answer
    
Memory usage from AWE doesn't show up in Task Manager. If you're on 32-bit, AWE is about the only option to effectively utilize memory above 2GB per process. –  TristanK Mar 16 '11 at 0:41
add comment

The reason why it's not showing up in Task Manager is it's using AWE, which locks and allocates pages in memory. This makes it important to set the Max Server memory since the memory is not pageable. http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

share|improve this answer
add comment

It's not a leak; This is the SQL is supposed to work. It grabs all the RAM it can. SQL is something that should definitely be the only app on the box, for this reason among other. If you really needed to limit it, I believe you can do that, but heaven help you if SQL ever needs more RAM than what you set its limit at.

share|improve this answer
    
Why doesn't the usage show up in TaskManager? I have no problem with SqlServer using it all, but when it hits 99%, the server becomes dreadfully non-responsive, including all apps that are connected to the database. –  user2125551 Mar 15 '11 at 20:18
    
OK - if your server is becoming non-responsive that's a different problem. And as above, Task Manager is not the best tool to use for figuring out SQL resource problems. –  mfinni Mar 15 '11 at 20:29
    
Because, per the first answer, you might have configured it to use AWE. –  TristanK Mar 16 '11 at 0:39
add comment

Yep it's likely SQL, and something may have changed to your usage patterns from SQL connections. I believe SQL only starts caching SQL data in RAM when that data is requested, so maybe you've got more requests lately. Unless you have poor SQL performance I would assume it's doing what it should.

share|improve this answer
add comment

I'm not sure why this behavior would have changed in the past few weeks, but it sounds like SQL Server is behaving as designed. It will typically hold on to as much memory as it's allowed to, up to the max server memory limit, relinquishing it only if some other process really needs it. The point at which you think SQL should relinquish RAM and the time at which SQL thinks it should relinquish RAM are obviously not in sync. This is fine and not out of the ordinary, that's why there are controls in place for you to clear this up. Set the max server memory parameter to allow the OS and backround apps some RAM and you'll be fine.

Here's an insightful post to help your understanding - http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

Furthermore, check out number 3 on this page to see why Task Manager is the wrong tool to use when checking x64 SQL Server's memory usage - http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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