Bad Performance when SQL Server hits 99% Memory Usage
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