November 22, 2011 at 2:54 am
Hello,
I hope someone out there can help me out. I have a 64bit SQL Server 2004 R2. The memory is currently running at 31gb and I have 32 gb in the machine (this is physical memory). When I look at Task Manager I can see that the application running the most memory is a SQL instance at 848,332K. Everything else is nowwhere near that. If I look at the resource monitor sqlservr.exe is running at 1,521,664. Dont know if its relevent but the CPU is only at 3-5% consistently.
I understand that this is a tough thing to help someone with in a forum, but I would like to know what you guys would do to make a start in finding out where all the memory is going, I need somewhere to start. OS is Windows 2008 R2 64Bit.
UPDATE: I notice that now and again the PAGES/SEC appears to be spiking, pushes the memory usage to to 30gb, but the memory is not released afterwards, but stays as it is leaving little left.
Thanks in advance,
D.
November 22, 2011 at 5:50 am
first of all check the SQL memory usage via perfmon using counter
SQL server :memory manager: Total server memory
that will give you a truer picture of the memory SQL is using. That value only actually records buffer cache so the total will be a bit higher than that (proc cache, locks etc).
Ensure max memory is set for SQL. I'd start at around 28GB on a 32GB machine.
---------------------------------------------------------------------
November 22, 2011 at 6:29 am
Regarding your concern about memory not being release: SQL Server never releases memory from the buffer pool. The only exception is when the OS requests it due to memory pressure, and only then if Lock Pages In Memory is not set.
Since you don't see the size of the buffer pool in Task Manager when Lock Pages In Memory is set. You have to use PerfMon as described by george sibbald.
I recommend you read this article http://blogs.technet.com/b/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx to get a better under understanding on SQL Server memory usage and all the problems you can encounter if memory settings are "suboptimal".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply