Bad Performance when SQL Server hits 99% Memory Usage

sql-server-2008windows-server-2008

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.

Best Answer

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.

Related Solutions

Sql-server – Missing Memory on Windows Server 2008

I found this article today which discusses in detail locked pages, AWE, and 64 bit systems. http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

One of the bullet points (#3) is title "Why is task manager not showing all of the memory allocated for SQL Server"

It's a very nice explanation. In the end the reason this doesn't show up is that sql server has "locked" those pages in memory. Locked pages are NOT part of the working set so they won't appear in Task manager or even appear as part of the applications memory set.

If you run select * from sys.dm_os_process_memory you can see the locked_page_allocations_kb field which will tell you how much RAM sql server has allocated in those pages.

SQL Server Memory Usage doesn’t tally

115982 is a very similar question

Is this a 64bit server - do you have the lock pages in memory local policy enabled?

the most likely reason is that this SQL Server 64bit instance is using “locked pages” as I’ve described in an earlier question. Notice the name on the Task Manager column is called Memory (Private Working Set). Remember we also said that if SQL Server 64bit instances use “locked pages” this memory would not be part of the working set (because remember AWE APIs are used on 64bit to “lock” pages and that memory is not part of the working set). So since the locked pages are not part of the working set, they won’t appear in this column in Task Manager. On the Task Manager for Windows Server 2003, this column is called “Mem Usage” but it also reflects the working set of the process.

Here is an article that explains it in depth

You can also view the counters in SQL

SELECT
    object_name
   ,Counter_name
   ,cntr_value
   ,ROUND(( cntr_value * 8192.0 ) / 1048576, 0) AS cntr_value_MB
FROM
    sys.dm_os_performance_counters
WHERE
    object_Name LIKE '%Buffer Manager%'
    AND RTRIM(counter_name) IN ( 'Free pages', 'Total pages',
                                 'Database pages' ) 
UNION SELECT
    object_name
   ,Counter_name
   ,cntr_value
   ,ROUND(( cntr_value / 1024 ), 0) AS cntr_value_MB
FROM
    sys.dm_os_performance_counters
WHERE
    counter_name IN ( 'Target Server Memory (KB)',
                      'Total Server Memory (KB)' )

Best Answer

Related Solutions

Sql-server – Missing Memory on Windows Server 2008

SQL Server Memory Usage doesn’t tally

Related Topic