Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to free up Physical memory (RAM) Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 12:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:17 AM
Points: 42, Visits: 252
Hi,

There were lots of PAGEIOLATCH_SH waits while running queries, when drilled down I found the statistics were out of date, hence I ran update statistics to all databases. That time usage of physical memory became high and it hold almost full (63.6 GB out of 64 GB). Now Update Statistics activity has been finished and all the queries respond very fast but the usage of physical memory remains 63.6GB only. The Windows Task Manager->Processes->sqlservr.exe shows it holds only 961,360 KB of Memory. Other processes also don't take much memory. It is a Production server - I can't restart. Hence please help me to free up space.

Thanks
Bala
Post #1465318
Posted Wednesday, June 19, 2013 12:36 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 42,827, Visits: 35,957
balasaukri (6/19/2013)
Hence please help me to free up space.


Why? SQL Server uses memory for performance reasons, disk access is slow. Why do you want to reduce its memory usage? All you'll do if you force it to release memory is degrade performance.

p.s. Never use Task Manager to check SQL Server's memory usage, it shows incorrect results under some configurations.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1465333
Posted Wednesday, June 19, 2013 1:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:35 AM
Points: 42, Visits: 399
I agree with Monster but to answer your question, don't you have max server memory settings? Set that one.

Also go over this blog, it shows some tricks.
http://serverfault.com/questions/108192/how-do-i-get-sql-server-to-release-its-memory
Post #1465361
Posted Wednesday, June 19, 2013 8:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:17 AM
Points: 42, Visits: 252
I tried the techniques those are mentioned in the link already but nothing released the memory.

One way I agree with Monster because the performance tab in Task Manager shows physical memory usage as 63.6 GB but under the processes tab - the cumulative memory usage of all the processes are not even 3 GB, there itself it contracdicts. But still SOMETHING which makes Task manager to show the wrong results means - that SOMETHING should be the root casuse of problem - I trying to figure out that SOMETHING.

It is a Physical server - Max memory is set to 60 GB - LPIM is enabled - Some third party tool is configured for replication - Pagefault and DiskWriteBytes/Sec are very high - Disk subsystem is configured on RAID 6.

Thanks
Bala
Post #1465442
Posted Thursday, June 20, 2013 12:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 42,827, Visits: 35,957
balasaukri (6/19/2013)
I tried the techniques those are mentioned in the link already but nothing released the memory.


Again, why are you trying to release memory? SQL needs that memory to perform well. If it's using too much, reduce max server memory slightly

But still SOMETHING which makes Task manager to show the wrong results means - that SOMETHING should be the root casuse of problem - I trying to figure out that SOMETHING.


Task manager only shows memory allocated by the VirtualAlloc API call. When you have locked pages in memory, SQL does not use VirtualAlloc to allocate memory, it uses a different API call, hence Task Manager doesn't show that memory. It's a bug (or limitation) in Task Manager, nothing more.

As for the root cause of the problem, well there's no problem here. SQL's behaving as expected.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1465476
Posted Thursday, June 20, 2013 5:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:17 AM
Points: 42, Visits: 252
Hi GilaMonster! Thank you so much for showed some light on the dark area. I'll dig into that more. Thank you for your time to clarify. Much appreciated.

Thanks
Bala
Post #1465585
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse