June 6, 2012 at 6:20 am
We have many sql server 2008 R2 64-bit clusters, and all will slowly consume as much memory as possible. For example, we do a reboot the first Sunday of the month, and by the next scheduled reboot, it's consuming the max allotted memory. What can I check to see what is causing this? Could it an application issue - connections not closing? How do I check that?
These clusters are shared database clusters with about 50 databases on them.
June 6, 2012 at 6:35 am
This is what SQL server does (by design).
What are the specifications of your machines (Proc's, Core's and Memory)? Is only SQL Server running on these clusters?
June 6, 2012 at 6:42 am
It's peanut butter jelly time!!
Yeah that's what I thought - sql by design. What is the reasoning behind that - why does it slowly consume memory? All of the clusters just have sql server running on them - 32GB ram with 24 procs.
June 6, 2012 at 6:59 am
SQL does this by design because reading memory is faster than reading from disk.
the bigger concern would be not HOW much memory is being used, but how WELL is the memory being used.
if you have 32 GB of RAM and you Page Life Expectancy is a low number, then i would be worried.
SELECT [object_name], [counter_name],[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy'
a value of less than 300 might be a concern.
June 6, 2012 at 7:01 am
Great information...thank you so much
June 6, 2012 at 7:27 am
Two things...
SQL is designed to use as much memory as you let it. It will increase it's memory usage as long as Windows is not signalling low memory.
300 for page life expectancy is an insane threshold these days. That number comes from an MS support doc from something around 8-10 years ago and it was reasonably valid when servers had 4GB of memory at most. These days with servers that have 64GB or more of memory, having a page life expectancy of around 300 would indicate critical memory pressure and major IO load, probably more than even the best tuned SAN can sustain.
http://www.sqlskills.com/blogs/jonathan/post/Finding-what-queries-in-the-plan-cache-use-a-specific-index.aspx (last section)
Please read through chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply