January 12, 2011 at 12:07 pm
Hi All,
We have 280 GB database hosted on a Server (4 x 6 core processor). The total Physical RAM available is 256 GB. Out of which 221 is set for SQL Server. That leaves 35 GB for all the other operations. Currently we have only 700 MB Available out of it.
What uses the other 34 GB of memory? The last time we started the SQL Server box was 3 months back.
We are using Windows 2008 Enterprise Edition (SP2) 64 bit as our OS. The SQL Server edition is SQL Server 2008 Enterprise edition (SP1) 64 bit.
Could someone help me understand why we have such low available memory? In 3 months, 34 Gb of memory was used up.
-Roy
January 12, 2011 at 12:11 pm
I'm assuming you've looked at what Windows says is using the memory, in Task Manager. Right?
What besides the OS and SQL Server is running on that server? What's in that 35 Gb besides the OS?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 12:17 pm
Bear in mind that the max_memory setting limits the buffer pool, not SQL's total memory usage. It can use extra memory for non-paged allocations - thread stacks, backup buffers, CLR, in-process linked server drivers, extended procs.
Grab a copy of sysinternal's process monitor and have a poke around. Task manager's woefully inadequate for seeing memory usage.
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
January 12, 2011 at 12:36 pm
Gus, That server runs only SQL Server. No other applications are in that box. We used performance monitor to find out about the health of the Server. The memory is the only one in Red. CPU stays at 20%, there are no warnings on Disk or anything else.
Gail, I knew that the max memory setting was just for buffer pool, but 34 GB used up in 3 months for all other SQL operations? That seems pretty high. Shouldnt the memory be released after its use?
I thought about using Sysinternal. But I was afraid to use it since it is heavy on resource usage. We do pretty high batch request per second (13k batch per second).
-Roy
January 12, 2011 at 12:38 pm
SQL Server tends to take memory and keep it.
If the only things running on there are the OS and SQL Server, does the low amount of available RAM actually matter? Or is this just a curiosity question?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 12:49 pm
Paging seems low. But it is just knocking me off my mind that 34 GB could be used in such a short time. As per Microsoft, the available should not go below 100 MB. But it has gradually been going down. Therefore I am wondering why it does not reuse the memory that it took.
-Roy
January 12, 2011 at 1:18 pm
Roy Ernest (1/12/2011)
Therefore I am wondering why it does not reuse the memory that it took.
I think it does, but that still implies a gradual increase because it never (or maybe very very rarely) releases any, so the current memory currently allocated is always the maximum memory that has been in use to date.
Tom
January 12, 2011 at 1:51 pm
Tom.Thomson (1/12/2011)
Roy Ernest (1/12/2011)
Therefore I am wondering why it does not reuse the memory that it took.I think it does, but that still implies a gradual increase because it never (or maybe very very rarely) releases any, so the current memory currently allocated is always the maximum memory that has been in use to date.
This will slowly starve the OS of the memory I guess. They say that they will release it when other process needs it, but somehow I have a bad feeling that I should not feel comfortable with that.
farax_x, Thanks for the links. But I am not sure Lock pages to memory is what is needed here. We do not see much/any paging for the SQL Server. The SQL Server is not starved of memory. What I was trying to understand is why there is such a high memory usage in a short period of time.
-Roy
January 12, 2011 at 2:20 pm
What is the Size of the Page File ?
Try to disable any unnecassary or unused windows services on the server and see it it frees up some memory on the box.
You can also see if any other windows processes are using more memory from the Task Manager.
Also Non Bufferpool Memory components of SQL Server could be consuming much of that memory based on the high server activity .
Also try to use Optimize for Adhoc workload option, which will greatly reduce the memory consumption, if a lot of adhoc queries are being submitted to the server.
Thank You,
Best Regards,
SQLBuddy
January 12, 2011 at 2:21 pm
The high memory usage sounds like a good thing to me, because it means SQL is able to use it.
What do the Performance Monitor counters show for these counters?
SQL Server:Memory Manager Target Server Memory (KB)
SQL Server:Memory Manager Total Server Memory (KB)
select
[OBJECT_NAME]=
left(quotename(rtrim(a.[OBJECT_NAME]))+
quotename(rtrim(a.[COUNTER_NAME]))+
case
when rtrim(a.[INSTANCE_NAME]) = '' then ''
else quotename(rtrim(a.[INSTANCE_NAME])) end
,60),
[Memory GB]=
convert(decimal(10,3),round((a.[CNTR_VALUE]+0.000)/(1024.000*1024.000),3))
from
master.dbo.sysperfinfo a
where
a.[OBJECT_NAME] in ('SQLServer:Memory Manager')and
a.[COUNTER_NAME] like '%(KB)%'
January 12, 2011 at 2:31 pm
have you used sysmon to track memory usage by process? Use private bytes and working set counters for all instances under the process object.
If you see an instance slowly increasing there is your memory leak. AV is a possible candidate.
---------------------------------------------------------------------
January 12, 2011 at 5:12 pm
Roy, I know this is a really, really stupid question - but can you confirm what the max memory setting is? It should be 262144 for 256GB of memory and I have seen so many times now people setting that value in KB instead of MB.
Other than that - I would tend to agree with you. I would not expect that much additional memory to be used by SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 12, 2011 at 9:40 pm
Roy Ernest (1/12/2011)
We have 280 GB database hosted on a Server (4 x 6 core processor). The total Physical RAM available is 256 GB. Out of which 221 is set for SQL Server. That leaves 35 GB for all the other operations. Currently we have only 700 MB Available out of it. What uses the other 34 GB of memory? The last time we started the SQL Server box was 3 months back.
Most likely SQL Server is only using slightly more than the 221GB allocated to the buffer pool. Check the performance counters as already mentioned by others on this thread.
Usually, the remainder is being used by the OS for caching. Check sys.dm_os_sys_memory (system_cache_kb in particular).
As a side note, I do recommend always setting locked pages and a sensible max memory value. You really don't want to experience a working set trim. Ever.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2011 at 7:21 am
Michael Valentine Jones (1/12/2011)
The high memory usage sounds like a good thing to me, because it means SQL is able to use it.What do the Performance Monitor counters show for these counters?
SQL Server:Memory Manager Target Server Memory (KB)
SQL Server:Memory Manager Total Server Memory (KB)
We did a controlled shutdown and restart of our SQL Server today morning. We usually do a controlled shutdown and restart once every 3 months. So the Output might not mean anything right now.
OBJECT_NAMEMemory GB
[SQLServer:Memory Manager][Connection Memory (KB)]0.009
[SQLServer:Memory Manager][Granted Workspace Memory (KB)]0.000
[SQLServer:Memory Manager][Lock Memory (KB)]0.047
[SQLServer:Memory Manager][Maximum Workspace Memory (KB)]166.074
[SQLServer:Memory Manager][Optimizer Memory (KB)]0.001
[SQLServer:Memory Manager][SQL Cache Memory (KB)]0.002
[SQLServer:Memory Manager][Target Server Memory (KB)]221.000
[SQLServer:Memory Manager][Total Server Memory (KB)]29.599
-Roy
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply