SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to free up Physical memory (RAM)


Need to free up Physical memory (RAM)

Author
Message
balasaukri
balasaukri
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 301
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228259 Visits: 46339
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, MVP, M.Sc (Comp Sci)
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


iaminyourhead
iaminyourhead
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 415
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
balasaukri
balasaukri
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 301
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228259 Visits: 46339
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, MVP, M.Sc (Comp Sci)
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


balasaukri
balasaukri
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 301
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search