Bad Performance when SQL Server hits 99% Memory Usage
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.
Solution 1:
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:
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.
Solution 2:
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
Solution 3:
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.
Solution 4:
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 - https://techcommunity.microsoft.com/t5/sql-server-support/fun-with-locked-pages-awe-task-manager-and-the-working-set-8230/ba-p/315836
Solution 5:
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.