Need to free up Physical memory (RAM)

Question

Need to free up Physical memory (RAM)

balasaukri

Default port

Points: 1443
More actions
June 19, 2013 at 12:05 pm

#294882

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 SSC Eights! Points: 810 More actions · Answer 2

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 Default port Points: 1443 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 Default port Points: 1443 More actions · Answer 5

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